Date related functions
You should try to use the isValidDate() function around any date expression to check for nulls when referencing a Date based Field in an Expression.
Returns DateTime, after adding a numeric value to a date field:
dateDiff([EndDate], [StartDate]) : Returns number of days between two dates. Use the 'datediff' function to calculate the duration of a task based on an End Date minus a Start Date
day([DateField]) : Returns day
month([DateField]) : Returns month
year([DateField]) : Returns years
hour([DateField]) : Returns the hour of a date/time field
minute([DateField]) : Returns the minute of a date/time field
dateNow() : Returns the current date into a date field, or an expression expecting a date field
dateTimeNow() : Returns the current date and time into a datetime field
isValidDate([DateField]) : Returns true or false if the field being specified as the parameter holds a valid date field. isValidDate() is useful in expressions to account for scenarios where dates referenced in expressions may have no value. If you had an expression which added 30 days to [DateField], you need to check that the date field has a value before adding days to it, so that would look like.
if(IsValidDate([DateField]), addDays([DateField], 30), [BlankDate])
If the value in [DateField] is a valid date, then add 30 days to the value in [DateField], otherwise return the value from a hidden Field that has a blank date ([BlankDate]). You could replace the field reference to blank date to ‘2000-01-01T00:00:01Z’ which is the actual value of blank for a date (date fields have to contain a value)
dateParse([String]) : Returns null or the date if the text being specified as the parameter is in a parseable date format – Examples of Valid date formats are '2015-11-30′, '30 Nov 2015'
minDate([DateField1], [DateField2], [DateField3], [DateField4], [DateField5]) : Given a list of dates or date fields, the function returns the earliest date
maxDate([DateField1], [DateField2], [DateField3], [DateField4], [DateField5]) : Given a list of dates or date fields, the function returns the latest date
weekNum([DateField]) : This will return the week of the year that the date in the date field falls into
CreatedDate() : Using this function returns the date for the earliest entry in the activity stream.
UpdatedDate() : Using this function returns the date for the latest entry in the activity stream
A complex example which takes a date time field [TestDateTime] and converts it in to a friendly formatted date in a text field using an expresion as follows
'' + year([TestDateTime])
+ '-' + IF(month([TestDateTime]) <10, '0', '') + month([TestDateTime])
+ '-' + IF(day([TestDateTime]) <10, '0', '') + day([TestDateTime])
+ ' ' + IF(hour([TestDateTime]) <10, '0', '') + hour([TestDateTime])
+ ':' + IF(minute([TestDateTime]) <10, '0', '') + minute([TestDateTime])
, 'No Date Provided')
The above would check the validity of a date field before then showing the date in the format
Time Related Functions
Expressions can be used to combine a time to a given date on the Record, to calculate the difference between to times and also to set the current time.
TimeDiffString(TimeFieldIdentifier1, TimeFieldIdentifier2, DisplaySeconds (optional)): This will return to a Text Field the difference between two times in a friendly format of hours, minutes and optionally seconds.
Example - If EndTime is 14:40 and StartTime is 2:20 then:
TimeDiffString(EndTime, StartTime, true) returns 12:20:00
TimeDiffString(EndTime, StartTime, false) returns 12:20
TimeDiffTotalSeconds(TimeFieldIdentifier1, TimeFieldIdentifier2): This will return hours and minutes to a Time Field or number of seconds to a Number Field and returns the difference between the two times.
Example - If StartTime is 14:40 and EndTime is 15:40 then:
TimeDiffTotalSeconds(StartTime, EndTime) returns 01:00 to a Time Field
TimeDiffTotalSeconds(StartTime, EndTime) returns 3600 to a Number Field
TimeNowString(DisplaySeconds (optional)): This will return to a Text Field the current time of day (in UTC form) upon the opening of a Record.
Example - If current time of day is 15:30 then:
TimeNowString(true) returns 15:30:00
TimeNowString(false) returns 15:30
TimeNowTotalSeconds(): This will return to a Time or Number Field the current time of day (in UTC form) upon the opening of a Record.
In the instance of returning to a Number Field the number of seconds past 00:00 will be given.
Combining Date and Time Fields
CombineDateWithTime(DateFieldIdentifier, TimeFieldIdentifier): This will bring together both a Date Field and a Time Field in order to present both in a DateTime Field. An example of such is given below:
Make sure to click the 'Save' button when making any changes in order for them to be added to the next app version. Once you have made all the changes you need to an application you are then ready to publish it to workspace.
Note on Field Dependencies - Read more here
Field dependencies are by definition the other field(s) that are reliant on a given field, usually dependent fields are interlinked to one another by expressions and are indicated by an atom symbol. They are a crucial tool when looking to delete Fields within an App or, when it comes to understanding the structure and data flows of an App.
Article is closed for comments.