Calculated Field based on Two Dates

Hi Braedon Owen,

I was able to achieve this functionality with the following formula on a calculated checkbox field:

or(and(not(isAfter($start_date_c,now())),not(isBefore($end_date_c,today()))),and(not(isAfter($start_date_c,now())),not(greaterThan(strlen(toString($end_date_c)),0))))

Here are a few notes regarding the formula and behavior:

• $start_date_c and $end_date_c are both date fields. If you want to compare datetime fields, you would need to incorporate the concept presented in the Calculated Field - Converting Datetime to Date KB article.
• I use not() with isAfter() and isBefore() to simulate the <= and >= conditions you specify.
• The formula used to identify if the $end_date_c field is empty is taken from the Calculated Field - Determining if a Field Is Blank KB article
• There is a bug (54571 - you will need to be a support-authorized contact to view bug links) with the isAfter() function when comparing to today(). I mitigated that bug by incorporating the addDays() function, so that when the $start_date_c is the current day, the formula validates as expected. When this bug is fixed, remove the addDays() function. I mitigated this bug by using the now() function with isAfter().  Do not use now() with isBefore() because that will result in an improper validation for end dates equaling the current date. Sugar Support elected to create a new bug, 83499, to track the issue with using isAfter() and today(). Once that bug is fixed, you should replace now() with today().
• There is a bug (80890 possibly - I am validating with Sugar Support) where the checkbox will always appear checked while editing the record in question. Once you save the record, the checkbox will reflect the expected result. This issue is now mitigated with the use of now() for the isAfter() formulas. 

I hope this helps!

That works perfectly, thanks Chris!

I do have another question surrounding this; will the record automatically update itself to say inactive/active or would that be a process that is created?

for example, a promotion is created with start date tomorrow. When I save that, the checkbox for 'active' will be false, will it show active tomorrow automatically?

That works perfectly, thanks Chris!

I do have another question surrounding this; will the record automatically update itself to say inactive/active or would that be a process that is created?

for example, a promotion is created with start date tomorrow. When I save that, the checkbox for 'active' will be false, will it show active tomorrow automatically?

Hi Braedon,

Fields with Sugar Logic require a save event in order to update, so the checkbox would not automatically update in the scenario you present. The most likely method to achieve this behavior would be via SugarBPM. One consideration to make there is the volume of records that would need to be continually evaluated by SugarBPM, because it may cause a performance concern. The reason for the performance concern is the process definition would need to incorporate wait events and those can be expensive to process. If there is a high volume of records (along the line of 1000s, not 100s) to continually evaluate, a custom scheduler may be a better solution to address this use case. My company, Upsert, develops custom schedulers to meet these use cases.

With regards to SugarBPM, your use case has two distinct scenarios:

1. Start date and end date set - I am assuming you would design the process to eventually end once the start and end date are both in the past. This should be achievable with wait events evaluating both fields.
2. Start date set and end date empty - I am less clear on what the exit criteria for this scenario would be. Would it just be once the start date is the current day or in the past? If so, this should also be achievable.

If you need further guidance on leveraging SugarBPM, it's probably best you start a separate thread in the community, and I will be happy to participate.

Hi Braedon,

In learning more about the defects with using isAfter() and today() together from Sugar Support, I was able to craft a better formula that improves the user experience. I updated the original post with that new formula and comments.