Learn Blog Pricing About Sign in

Learn

Blog

Pricing

About

Sign in

DateTimeFields property

App.DateTimeFields — { DateTimeField }

The date and time fields of the app as an array.

Accessing all date and time fields of the app through this property has many uses:

The following sections detail how these scenarios can be realized. Skip to the examples at the bottom for the concise version.

Making the background color red if a date and time field is invalid

The BackgroundColor property determines the background color of a screen and all screens that follow that have no explicit background color set. That means that if the background color is set for the first screen of an app, and no other screens have a background color set, the first screen determines the background color of the entire app.

We can make use of this knowledge to make the background of the entire app red, but only if at least one date and time field of the app is invalid. This formula is associated with the BackgroundColor property of the first screen:

IF(OR(NOT(App.DateTimeFields.Valid)), Color.Red)IF(OR(NOT(App,DateTimeFields,Valid)); Color,Red)

The formula fragment App.DateTimeFields.ValidApp,DateTimeFields,Valid returns a logical array, where TRUE indicates that the corresponding date and time field is valid and FALSE indicates that the corresponding date and time field is invalid. Applying the NOT function to this array negates every element, meaning that TRUE indicates that a date and time field is invalid and FALSE indicates that a date and time field is valid. (The ! operator would have had the same effect.)

Then, the OR function is applied to this array. It returns FALSE only if all elements of the array are FALSE. In other words, it returns TRUE if one or several elements are TRUE, meaning that it returns TRUE if one or several date and time fields are invalid.

Finally, the IF function is used to return the color red if one or several date and time fields are invalid. Otherwise, IF returns a blank value, which has no effect on the background color. The net effect is that the background color of the app is made red if one or several date and time fields of the app are invalid.

Finding the latest date

This formula returns the latest date represented by the values of the date and time fields of the app:

MAX(App.DateTimeFields)MAX(App,DateTimeFields)

The formula above can also be written as follows:

MAX(App.DateTimeFields.Value)MAX(App,DateTimeFields,Value)

However, as the MAX function is looking for an array of numbers, and date and time fields return numbers through their Value properties, .Value,Value is inferred and does not need to be spelled out.

Finding the latest date from other date and time fields

Let's say that the app consists of ten date and time fields, Field1 through Field10. If Field10 should contain the latest date of the other date and time fields, it is tempting to try to associate this formula with the Value property of Field10:

MAX(App.DateTimeFields)MAX(App,DateTimeFields)

However, that formula will not work. Instead, you'll get an error message, because you're effectively asking that the calculated value of Field10 includes the value of Field10 itself.

This is similar to trying to use the formula NumberField1 * 2NumberField1 * 2 for the Value property of NumberField1, effectively asking that the value of NumberField1 should be set to the value of NumberField1, multiplied by two. This is known as a circular calculation, and results in an error message.

In order to solve this issue, the formula needs to reference the date and time fields to include in the calculation explicitly:

MAX(Field1:Field9)MAX(Field1:Field9)

Above, Field1:Field9Field1:Field9 creates an array consisting of Field1, Field9 and all items that appear between them. Notably, Field10 is not part of the array.

Enabling a button only if all date and time fields have been filled out

The Enabled property of a button determines if users can interact with the button. If a button should only be enabled if all date and time fields of the app have been filled out, associate this formula with the Enabled property of the button:

AND(ISDEFINED(App.DateTimeFields))AND(ISDEFINED(App,DateTimeFields))

The ISDEFINED function returns a logical array when its sole parameter is an array. TRUE elements in the array indicate that the fields have defined values, that is, have been filled out. Conversely, FALSE elements indicate that the fields have not been filled out.

Finally, the AND function returns TRUE only if all elements of the array are TRUE, otherwise it returns FALSE. The net effect is that AND returns TRUE only if all date and time fields of the app have been filled out, prompting the Enabled property to only allow users to press the button once all fields have defined values.

Including all date and time field values of the app in an email

The Body property of email report buttons allows the body of an email to be set through a formula. While email report buttons have built-in support for including field values, through the IncludedFields property, building a text string manually to include in the email body allows us more flexibility.

Consider this formula, which should be associated with the Body property of an email report button:

TEXTJOIN(NEWLINE(), FALSE, App.DateTimeFields.Label & ": " & App.DateTimeFields.FormattedValue)TEXTJOIN(NEWLINE(); FALSE; App,DateTimeFields,Label & ": " & App,DateTimeFields,FormattedValue)

Above, the formula fragment App.DateTimeFields.LabelApp,DateTimeFields,Label returns a text array, made up of the labels of the date and time fields of the app. The formula fragment App.DateTimeFields.FormattedValueApp,DateTimeFields,FormattedValue also returns an array, this time made up of the formatted values of the date and time fields. Using &, the labels are joined together with the formatted values, separated by a colon.

The resulting text array, where every element consists of a label, followed by a colon and a value, is converted into a single text string using the TEXTJOIN function. Its first parameter, NEWLINE()NEWLINE(), ensures that the array elements are separated from one another using line breaks.

Ranges versus this property

If the app only consists of the date and time fields Field1, Field2 and Field3, these formulas are equivalent:

App.DateTimeFieldsApp,DateTimeFields
Field1:Field3Field1:Field3
{ Field1, Field2, Field3 }{ Field1; Field2; Field3 }

The second formula uses a range to create an array consisting of Field1, Field3 and all items that appear between them, which in this case is only Field2.

The chief advantage of the DateTimeFields property, compared to a range, is that there is no need to update formulas when additional date and time fields are added to the app. If Field4 were to be added to the app, the Field1:Field3Field1:Field3 range would have to be changed to Field1:Field4Field1:Field4 everywhere it is used.

By contrast, App.DateTimeFieldsApp,DateTimeFields automatically includes Field4, and any other date and time fields that are added.

Filtering date and time fields

If you want to process only a subset of the date and time fields returned from this property, use the FILTER function. It can base its decision on which date and time fields to return on the property values of the date and time fields.

This formula only returns visible date and time fields:

FILTER(App.DateTimeFields, App.DateTimeFields.Visible)FILTER(App,DateTimeFields; App,DateTimeFields,Visible)

Crucially, you can also filter on the names of the date and time fields, using standard text functions. This formula only returns date and time fields whose names include the text string "Required":

FILTER(App.DateTimeFields, CONTAINS(App.DateTimeFields.Name, "Required"))FILTER(App,DateTimeFields; CONTAINS(App,DateTimeFields,Name; "Required"))

If you use a deliberate naming strategy for your date and time fields, you can use FILTER in conjunction with this property to ensure that you only process a specific subset of date and time fields.

Related properties

Use the DateTimeFields property of a form screen to access all date and time fields of said form screen and the DateTimeFields property of a form group to access all date and time fields of said form group.

The Fields property returns all fields of an app, not just date and time fields. The Items property returns all items of a form screen, including fields, buttons, text boxes and named values. Use NumberFields, TextFields, SwitchFields, NumberDropDownFields and TextDropDownFields to access other kinds of fields.

Examples

MAX(App.DateTimeFields)MAX(App,DateTimeFields)

Returns the latest date represented by the date and time fields that belong to the app.

MAX(App.DateTimeFields.Value)MAX(App,DateTimeFields,Value)

Returns the latest date represented by the date and time fields that belong to the app. If .Value,Value is left out, it is inferred.

AND(App.DateTimeFields.Valid)AND(App,DateTimeFields,Valid)

Returns TRUE if all date and time fields of the app are valid and FALSE otherwise. App.DateTimeFields.ValidApp,DateTimeFields,Valid returns a logical array, where each element reflects whether its corresponding date and time field is valid. Finally, the AND function returns TRUE if all elements are TRUE and FALSE otherwise.

TEXTJOIN(NEWLINE(), FALSE, App.DateTimeFields.FormattedValue)TEXTJOIN(NEWLINE(); FALSE; App,DateTimeFields,FormattedValue)

Returns all formatted values of the app's date and time fields as a text string, where values are separated from one another using line breaks. The formula fragment App.DateTimeFields.FormattedValueApp,DateTimeFields,FormattedValue returns an array of formatted values, which the TEXTJOIN function joins together with line breaks.

AND(ISDEFINED(App.DateTimeFields))AND(ISDEFINED(App,DateTimeFields))

Returns TRUE if all date and time fields of the app have been filled out. When the ISDEFINED function is applied to an array of date and time fields, it returns a logical array whose elements indicate if the corresponding date and time field has a defined value. The AND function returns TRUE if all array elements are TRUE and FALSE otherwise.