Advanced
This section contains additional details about using the date-based conditions and other advanced date-based alert concepts.
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.
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 on 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.
As noted above, the
getDate()
method returns a date value equal to today. This may be used in other methods, like
dateEval()
and
dateAddDays()
.
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()
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 lowercase '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.
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 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 in 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 (in this example, 175 is the column ID number):
IsheetManageColumnPageCollection.editRow(175,'contentManager')