Basics
Alerts can be configured to trigger based upon the value entered into a Date column. This guide is designed to allow you to quickly and easily copy and paste the alert conditions into your own iSheet properties page and then change the corresponding values as appropriate.
As with all other iSheets alerts, you will need to ensure that certain steps have been taken in order to prepare the iSheet for alerting. It is advised that you familiarise yourself with these concepts first, before attempting Date Based alerts.
Here are the key steps required to set up an iSheet alert:
- Select Enable sheet alerter on the iSheet Properties page.
- Designate an iSheet Alerter view on the iSheet Manage views page.
- Add an alerter condition on the iSheet Properties page.
- Ensure users who wish to receive alerts sign up for alerts at a frequency of immediate, daily and/or weekly.
Typically, an alert is sent out when today's date is either a certain number of days before, after or on the date entered into any Date column. For example, perhaps you have a Date column where you record a date and then want to be alerted 7 days prior to that date. In order to achieve this, you would use the following expression:
dateEval(getDate(), 'EQ', dateAddDays(#{col_172},'-7'))
Effectively, this expression is instructing the system to check today's date against the date in the specified Date column and see if it is either a number of days before or after. In the above example, the alert would trigger 7 days before the date entered. The numerical value at the end of the statement is what controls when the alert is triggered. The example below would be for an alert to go out 7 days after the specified date.
dateEval(getDate(), 'EQ', dateAddDays(#{col_172},'7'))
Combining Multiple Alerts
You can combine multiple expressions in order to trigger a series of alerts. This is achieved by joining the expressions together via the use of the OR operator, which is expressed in the following way: ||
The below example is for an alert to trigger 60 and 30 days before a specified date.
dateEval(getDate(), 'EQ', dateAddDays(#{col_172},'-60')) || dateEval(getDate(), 'EQ', dateAddDays(#{col_172},'-30'))
It is possible to combine as many of these alerts as desired.
Remember to always ensure your syntax is correct and that your statements are formatted correctly, as above. Typically, when joining multiple alerts together, you should build them up in incremental steps, testing each condition element as you go to ensure that it works before moving on and adding the next element. This will help you easily identify where a problem can be found when it is encountered.
Using Number Columns
You may wish to allow users the ability to define how many days before a particular date you want to receive an alert. This is possible by having the system reference both a date column and a number column within your iSheet. Below is an example of how to achieve this.
dateEval(dateAddDays(getDate(),'#{col_13481}'), 'EQ', #{col_13480})
In this example 'col_13481' is the Number column and 'col_13480' is the Date column. When the alert is triggered it checks the number that has been input into the number column and then sends out the alert that number of days before the date in the Date column.
Combining Date and Choice alerts
Whilst you can combine multiple alerts in one expression, you can also combine Date based alerts and Choice based alerts. Typically this would be used when you want to alert a number of days before or after a certain date, but only if a second value equates to true. For example, you may wish to receive an alert 7 days before a specified date as long as a 'Yes/No' Choice column is set to 'Yes'. In this scenario, if the Choice column is set to 'No', then the alert won't be triggered. An example of this scenario is below.
dateEval(getDate(), 'EQ', dateAddDays(#{col_14},'-7')) && containsAnyOf(#{col_10}, 'Yes')
The above example is using a hard-coded value of 'Yes', however, you could also get the same result by using a User Selection condition, as seen below.
dateEval(getDate(), 'EQ', dateAddDays(#{col_14},'-7')) && containsAnyOf(#{col_10}, #{ucol_10})
This method relies on the various Choice columns being included in the Alert Preferences and each user setting their preferences for what they wish to receive Alerts on.
Logical Operators
Whilst it is advised that you use the Equals operator most of the time, it is possible to apply other logical operators if required. Again, when building new Alert conditions, be sure to build incrementally and test at each step. Below is an example of other operators that can be used to build custom date based alert preferences.
- 'EQ' (equals)
- 'NE' (not equals)
- 'LT' (less than)
- 'GT' (greater than)
- 'LTE' (less than or equal to)
- 'GTE' (greater than or equal to)
Nesting Conditions
When joining multiple conditions together, you may find that you need to nest some of these so the system interprets them as you intend. This is much like how you would nest parts of an equation in mathematics. Below is an example of a more complex, nested Alert condition.
dateEval(dateAddDays(getDate(),'#{col_13481}'), 'EQ', #{col_13480}) && (containsAnyOf(#{col_13470}, #{ucol_13470}) || containsAnyOf(#{col_13482}, #{ucol_13482}) || containsAnyOf(#{col_13453}, #{ucol_13453}) || containsAnyOf(#{col_13483}, #{ucol_13483}))
In the example above, the first part of the condition is a date based alert using a number column to define the number of days before. The second part of the condition is a number of 'Yes/No' choice columns allowing users to select a date based alert based on one of the four choices. In order to achieve this, we have wrapped the four choice alerts within parentheses (with the key section highlighted). In this way, we treat these for conditions as one. We can then join the first and second elements of the condition together with the AND operator: &&
Advanced
This section contains additional details about using the date based conditions and other advanced date based alert concepts.
dateEval()
The dateEval() method compares two dates and determines whether an alerter condition evaluates to true or false, or whether or not to send an email alert.
This method takes three arguments: dateEval([date1], [logical operator], [date2])
- The two date arguments can be one of four types of date values:
- A date column in the iSheet. Date column identifiers appear above the email alert conditions in the iSheets Properties page when "Enable sheet alerter" is checked.
Note that if the value of a date column used in your condition is empty, such as if no date was entered for a given record, then dateEval() evaluates to false.
- Today's date, which can be referenced using the getDate() method,
- An adjustment to a Date column or to today's date, using the dateAddDays() method (see below), or
- A hard-coded date value, in this format: 'DD MMM YYYY'. Note that the date value must be surrounded by single quotes, such as '05 Nov 2019'
- The logical operator defines the relationship between the two dates, such as whether they must be equal, one must be greater than another, etc. There are 6 possible logical operators, listed above, which must always be surrounded by single quotes.
dateAddDays()
The dateAddDays() method takes a date value and adjusts it by the number of days specified.
This method takes two arguments: dateAddDays([date], [number of days])
- The date argument can be any of the four types of date values listed above, but typically will be a date column or today's date (getDate()). A nested dateAddDays() reference may also be used, as demonstrated below in the Using Calculation Columns section.
If the value of the date referenced in your condition is empty, such as if no date was entered for a given record, then dateEval() will evaluate to false.
- The number of days argument can be any one of the following types of number values:
- A hard coded number, like '5' or '-7'.
- A reference to a column in the iSheet. This can either be:
- A Number column. If the number column is left blank in a record, and that number column is used in the dateAddDays() method, then the alerter condition will always evaluate to false for that record. Therefore, it is recommended that any such number column always be a required column in the iSheet.
- A Calculation column. Note that if a calculation column is based on a number column that has been left blank, the calculation column will always evaluate to zero, which may lead to unanticipated alerts being triggered. Therefore, it is recommended that any number column used in a calculation field always be a required column in the iSheet.
- A Single line or Multi line (non-rich) text column. Note that the value that is in that column must be a number. A non-number value will not work. Also, the column ID number for text columns -- like '#{col_175}' -- are not listed in the Properties page. See Locating the iSheet column ID instructions below to find a text column's ID number.
The source of the number CANNOT be a choice field, such as if you wished to create a "Days In Advance" choice field, with choices of 1, 5, 10 and 20, to allow the user to pick one, which would govern when the alert is triggered.
The value in the [number of days] argument MUST always be surrounded by single quotes, regardless of what type of value it is. For example, it should be written as: '-7' or '5' or '#{col_432}' if that is a number or calculation column.
getDate()
As noted above, the getDate() method returns a date value equal to today. This may be used in other methods, like dateEval() and dateAddDays().
recordModified()
An alert will typically be sent out EVERY day that the alerter condition evaluates to true for a given iSheet item
For example, if the condition is: Expiration Date is less than or equal to Today's Date:
(dateEval(#{col_172}, 'LTE', getDate())
Once the Expiration Date matches today's date and then recedes further into the past, then every day thereafter the alert will be triggered, unless the Expiration Date is updated, or some other part of the condition causes the alert to be false.
However, by adding the recordModified() method to the condition, you are telling the system to send out the alert only when the rest of the condition is true AND the record has just been modified or created.
For example, if the alerter condition is written as follows, the alert would be sent once the Expiration Date had been reached for a given record AND only again if someone manually updated that record:
dateEval(#{col_172},'LTE',getDate()) && recordModified()
Syntax
These elements must be used exactly as written above, including the case of the letters in the methods and the use of parentheses. Using "recordmodified()" with a lower case 'm' will not work. Also, make sure to always use "simple" single quote characters, not the single quote characters found in Microsoft Word. In other words, do not use Word to create the formula and then copy and paste a formula from Word.
Time and decimal points
If the value in a Date column includes a time element as well as the date, for purposes of date based alerts, the time portion of the date value will be ignored entirely. The same applies to the getDate() method; only the date portion is relevant. Dates with times will NOT be rounded up or down to the nearest date; the time element will simply be ignored.
If a number with decimals is used in dateAddDays(), it will be rounded to the nearest whole number, where .5 rounds up.
Date based alert frequency
When an alerter condition includes the dateEval() method, a special rule applies that affects when alerts are sent:
- For each day that the condition is true, an alert will be sent at midnight at the start of that day, based on the time zone of the relevant Collaborate instance
These daily alerts will be sent out to every user who has signed up to receive alerts for ANY frequency, even if they have not signed up to receive immediate or daily alerts. For example, if a user signs up for weekly alerts and the "Expiration Date is less than Today" alert is triggered for a newly added item the next day, that user will receive a daily alert. The reason for this is based on the assumption that a user would prefer to receive a date based alert as soon as possible. For example, a user should not wait a week to be alerted about an upcoming expiration date, as the expiration date may have been passed by the time the user receives the weekly alert digest.
Also, if a regular date based alerter condition is used, alerts will still be sent out when that condition is true for a given record, even if notifications were suppressed by the user when that record was added or updated.
Date comparisons and positive and negative days
The following alerter condition tells the system to send out an alert when the Expiration date is 60 days from today's date (i.e., 60 days before), OR send an alert when the Expiration date is 30 days from today's date (30 days before).
dateEval(getDate(), 'EQ', dateAddDays(#{col_172},'-60')) || dateEval(getDate(), 'EQ', dateAddDays(#{col_172},'-30'))
The same alerter condition could be written in the following way:
dateEval(#{col_172}, 'EQ', dateAddDays(getDate(),'60')) || dateEval(#{col_172}, 'EQ', dateAddDays(getDate(),'30'))
In both examples, we want an alert sent some number of days before the date has been reached. In the first example, the alerter logic states: send an alert when today is equal to the Expiration Date minus 60 days (or minus 30 days). In the second example, the alerter logic states: send an alert when the Expiration Date is equal to today's date plus 60 days (or plus 30 days).
There are usually two different ways to write the same condition. Write it the way that makes the most sense to you and test the alerts before releasing to production.
Using Calculation Columns
One of the main reasons to use Calculation columns is because the dateAddDays() method can only take a hard-coded number or a column reference to represent the number of days. For example, if you want to trigger an alert 30 days before the Renewal Notice Deadline before the Expiration Date, you may NOT write it this way (with the key section highlighted):
dateEval(dateAddDays(getDate(),'#{col_175}+30'), 'EQ', #{col_172})
(where column 175 is the Renewal Period column).
Instead, create a calculation field called "Renewal Period Plus 30 Days" (column 176) and reference THAT calculation column instead:
dateEval(dateAddDays(getDate(),'#{col_176}'), 'EQ', #{col_172})
There are alternatives, like nesting the dateAddDays() method to achieve the same result, although this gets hard to read:
dateEval(dateAddDays(dateAddDays(getDate(),'#{col_175}'),'30'), 'EQ', #{col_172})
Locating the iSheet column ID
On the iSheet properties page, every Date, Number and Calculation column is listed, to make it easier to reference those columns in the alert condition. However, if you need to locate the ID number of a text column (multi-line or single line) which includes a number for use in the dateAddDays() method:
- Navigate to Manage columns for the iSheet
- Right-click the name of the column and select Inspect to view the HTML source of the element in the browser console
- The onclick attribute will look something like this: IsheetManageColumnPageCollection.editRow(175,'contentManager').
(in this example, 175 is the column ID number)
Best practices
Use the equals logical operator most of the time
If any logical operator besides equals ('EQ') is used in the dateEval() method, there is the risk that every recipient of those alerts will start to receive an alert EVERY day that the condition evaluates to be true. This is rarely the intended behaviour. Typically, using the 'EQ' operator is the best approach, as that will only cause an email alert to be sent on the one day that the condition evaluates to true.
Other logical operators are best used when the recipient is required to take some action to ensure that the condition no longer evaluates to true. Until that occurs, the daily alerts will continue to be sent. For example, in the lease expiration context, add a column to the iSheet called 'Lease Renewal In Progress' (Yes or No). In the email view, filter for only those leases where the renewal is not in progress. Once a recipient receives an alert for a lease coming due, they can change the value of this field to 'Yes', which will cause alerts for that lease to stop. But until the user changes the value of the lease renewal in progress column, the user will continue to receive alerts. If the lease is extended, then the user can modify the lease Expiration Date to be in the future and set the Lease Renewal in progress column back to 'No', so that next year an alert will be sent when the lease is expiring.
Filter on [Me]
Configure the email view to be filtered based on the value of an assignee column. For example, add an 'Assignee' column - a user lookup type column - to the iSheet. This way, you can make a person responsible for each lease. In the email view, filter for records where Assignee = [Me]. That way, only the person who is responsible for a given lease will receive a lease expiration alert for it, not every user who signs up for alerts. This would be similar to assigning a task to a specific user.
