How to use Relative Date capability in Selection Criteria and Active Record Criteria 

Many times in Salesforce it is useful to use certain Relative Date keywords in a Date field instead of having to select a specific date. For example, in many scenarios it is more convenient to be able to view/use records assigned ‘TODAY’ instead of on a specific date which will need changing manually to reflect correct records. In Salesforce, we have many such keywords in use such as TODAY, YESTERDAY, TOMORROW etc and Q-assign is now able to support them as well. 

This capability in Q-assign also allows for more flexibility such that users will now be able to use Relative Dates such as ‘Last N Days’ or ‘TODAY + 5’ without having to create any formula fields in Salesforce. Please see the following steps to setup Relative Dates in Q-assign: 

Create a new Assignment Group (AG) or edit an existing AG  

In Selection Criteria or Active record criteria select a "DateTime" field in the Field Column 

1. When a DATETIME field is selected 

• Create a new or Edit an existing AG 
• In Selection Criteria or Active Record Criteria click on Add row 
• Select a ‘DateTime’ field in the Field Column 
• A picklist will be displayed in the Value Column which will let you select a fixed date for that field or any relative date like NOW, TODAY, YESTERDAY etc (including NOW) as shown below 

• Save the assignment group

2. When a DATE field is selected 

• Create a new or Edit an existing AG 
• In Selection Criteria or Active Record Criteria click on Add row 
• Select a ‘Date’ field in the Field Column 
• A picklist will be displayed in the Value Column which will let you select a fixed date for that field or any relative date like TODAY, YESTERDAY etc (except NOW) 

• Save the assignment group

3. When FixedDate is selected for a Date or DateTime field 

• Create a new or Edit an existing AG 
• In Selection Criteria or Active Record Criteria click on Add row 
• Select a ‘DateTime’ or ‘Date’ field in the Field Column 
• From the picklist of Date Types displayed in the Value Column, select the option of Fixed Date 
• A date picker will be displayed and from that you can select a fixed date for the field.  

Upon saving, under the value column only date that was selected from data picker will be displayed as shown below 

 

• Save the assignment group

4. When NOW or TODAY relative date is selected for a DateTime field 

• Create a new or Edit an existing AG 
• In Selection Criteria or Active Record Criteria click on Add row 
• Select a ‘DateTime’ field in the Field Column 
• From the picklist of Date Types displayed in the Value Column, select the option of TODAY or NOW 
• A further 3 fields will be displayed to let the user add or subtract any number (offset) from the relative date of NOW or TODAY. The first field is for selecting if the offset is to be subtracted from or added to NOW/TODAY. The second field specifies the offset, whilst the third field defines the unit of offset (minutes, hours, or days). This offset field only accepts numbers and if left blank will be considered as 0 or no offset.  
  • Where the offset is not left blank or set to 0, a saved relative date will show in the format TODAY + 1 Day(s)

  • When NOW is selected, the available unit of offset will be "Minutes”, “Hours”, or “Days" 

  • When TODAY is selected, the available unit of offset will be "Days" only, as TODAY relates only to date (there is no option to offset by minutes or hours) 

• Save the assignment group

5. When a relative date literal with the format LAST_n_DAYS:n is selected for a Date or DateTime field 

• Create a new or Edit an existing AG 
• In Selection Criteria or Active Record Criteria click on Add row 
• Select a ‘DateTime’ or ‘Date’ field in the Field Column 
• From the picklist of Date Types displayed in the Value Column, select a date literal with the format LAST_n_DAYS:n, that contains ":n" at the end 
• Further 2 fields will be displayed to let the user select a number (offset) for n. The first field displayed is for specifying a number for the "n" in the date literal. The second field displays the unit of offset which only accepts numbers and if left blank will be considered as 0 or no offset.  

Where the offset is not left blank or set to 0, a saved relative date will show in the format LAST N DAYS + n Day(s) (here 6 represents the number of days as the date literally says LAST N DAYS). 

 

• Save the assignment group

Please set up the rest of the Assignment Group as per your requirements. Please contact us at support@ortooapps.com for any questions.