The table below lists the functions available in the Formula Editor as well as a description and a common example of how that function can be used.
Function |
Description |
Example |
||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
Returns the value of a number without regard to its sign. Absolute(2) returns 2 as does Absolute(-2). |
||||||||||||||||||||||||||||||||||
Examines all values contained within the specified column of a table object and returns the average of all values contained within that column. Use the All Rows option when selecting the field. |
You may wish to calculate the average sale value within a specified period. Using the Average function can help you do this. |
|||||||||||||||||||||||||||||||||
Browser Type |
Returns the browser used for displaying the app instance. |
|||||||||||||||||||||||||||||||||
Browser Version |
Returns the version number for the browser used for displaying the app instance. |
|||||||||||||||||||||||||||||||||
Rounds up a numeric field to the nearest whole number. |
A contractor charging an hourly fee may charge a full hour minimum even if less than an hour has been worked. Use the Ceiling function to round up to an even hour. |
|||||||||||||||||||||||||||||||||
Allows data from up to five input fields to be joined together as a string rather than as a numeric addition. |
Example: Join together 2013 & 06 & 10 to get 20130610 instead of 2029. |
|||||||||||||||||||||||||||||||||
Examines all values contained within the specified column of a table object and returns the number of non empty rows contained within that column. Use the All Rows option when selecting the field.
The Set Table Rows behavior includes advanced options to count the number of rows that contain certain values. |
If each row in your table represents a sale, the Count function can tell you how many sales you have in a given period. |
|||||||||||||||||||||||||||||||||
Returns a date by adding the specified number of units of time to the specified date or time field. Along with Date Difference, this can be used to calculate the days an app instance has been open.
•Date - The Date Field to add to. •Amount - The number of units of time to add to the date field. •Units - The required unit of time to which amount refers.
The units available for apps and reports are as follows:
|
In an app where users input the number of days for a hotel stay checking in on a specific date, this function will return the check-out date. |
|||||||||||||||||||||||||||||||||
Returns the difference between two date or time fields and returns the result in the specified unit of time. Along with Date Add, can be used to calculate the days an app instance has been open.
•Date 1 - First date field. •Date 2 - Second date field. •Units - The unit of time in which the result is returned.
The units available for apps and reports are as follows:
|
||||||||||||||||||||||||||||||||||
Returns the day of the month for the specified date field. |
The Day function can help you to identify which days of the week sales are lowest and highest. |
|||||||||||||||||||||||||||||||||
Will extract a parameter from a query string that is contained in an object on the app. The query string is likely to have been imported into the app and would contain several parameters in a single string.
Specify the field name that contains the query string and the name of the parameter. |
||||||||||||||||||||||||||||||||||
Rounds down a numeric field to the nearest whole number. |
An employer may only pay an employee in hourly increments. If the employee has worked 1 hour and 9 minutes, this function will round down to 1 hour. |
|||||||||||||||||||||||||||||||||
Use this function on a Show App or Show Report button/behavior in order to receive data passed by custom parameters. Parameter names must be two characters or more to avoid conflicts with built-in parameters associated with embedding an app into a Web page. Also, data passed may not exceed 250 characters. |
A parent app (application) passes to a child app (W4) the name and address of the applicant using custom parameters. |
|||||||||||||||||||||||||||||||||
It is possible to call an embedded app from JavaScript in the Web page. Data from the Web page can be passed into the app instance.
Specify the parameter index. |
Your Web page has called an app using the API. User’s name and address are passed to the app using external call parameters. |
|||||||||||||||||||||||||||||||||
Will extract a parameter which is included in the URL for the app instance.
Specify the parameter name. |
||||||||||||||||||||||||||||||||||
GetAge |
Will return the age in years from a date input field. |
|||||||||||||||||||||||||||||||||
GetExactDay |
Will return the day number as a string instead of a number. Note: In order to display the result as a string it must be displayed in a text input field. |
Example: It will return 06 instead of 6.
|
||||||||||||||||||||||||||||||||
GetExactMonth |
Will return the month number as a string instead of a number. Note: In order to display the result as a string it must be displayed in a text input field. |
Example: For March it will return 03 instead of 3.
|
||||||||||||||||||||||||||||||||
GetExactYear |
Will return the year number as a string instead of a number. Note: In order to display the result as a string it must be displayed in a text input field. |
|||||||||||||||||||||||||||||||||
Returns the hours value for a specified time field. |
||||||||||||||||||||||||||||||||||
Searches for an occurrence of the text string (search text) within another string (text) and returns the starting position of any matching occurrence. The first character in the text is position 0. A return position of -1 indicates that search text was not found. |
Sometimes it is necessary to extract or define the first or last sentence in a text input. Use this function to find the first or last “.” (period). Then a sub text function can be used to handle the text. |
|||||||||||||||||||||||||||||||||
isHtml |
Returns 'true' or 1 if the app is displayed in the HTML Player. Returns 'false' or 0 if the app is displayed in the Flash Player. |
Flash Player is now obsolete. |
||||||||||||||||||||||||||||||||
isMobile |
Returns 'true' or 1 if the app is displayed on a mobile device. Returns 'false' or 0 if the app is not displayed on a mobile device. |
|||||||||||||||||||||||||||||||||
This refers to the joining or ‘concatenation’ of two or more string values into one value. If the function parameter is a list of values (e.g. a field associated with a column of a table), it returns a single text value which contains all values separated by commas (e.g.. “Row 1, Row 2, Row 3”). If the function parameter is a single value, it returns the value unchanged. |
You may find that you need to send notifications to multiple e-mail addresses. You can do this using the Join function if the target e-mail address field on the app has e-mail addresses separated by commas. |
|||||||||||||||||||||||||||||||||
Searches for the last occurrence (in case there is more than one) of a text string (search text) within another text string (text) and returns the starting position of that last occurrence. The first character in the text is position 0. A return position of -1 indicates that Search text was not found. |
If you have a large text group on your app and need to find a good break point knowing the text will occupy more than one page, the Last Index Of function can help.
You know that the first page only holds 5,000 characters. You can take all characters after 5,000 and place them in another field, then find the last period using Last Index Of. Take all characters from one space after the period up to 5,000 and place them in front of the next 5,000. |
|||||||||||||||||||||||||||||||||
Removes all leading spaces from a text field. |
If you need to run numbers together without adding them to each other (dates for example - 050311) you must use a text input field rather than a numeric input field. A space is required at the beginning of the formula. The Left Trim function will remove the extra space. |
|||||||||||||||||||||||||||||||||
Returns the length (number of characters) of the specified text field. |
The Length function can help you ensure that a telephone number field on your app has at least 10 digits. |
|||||||||||||||||||||||||||||||||
Converts all characters to lower case. |
Use the Lower Case function if the database with which you integrate has a configuration that only accepts lower case text. |
|||||||||||||||||||||||||||||||||
Examines all values contained within the specified column of a Table object and returns the largest value contained within that column. Use the All Rows option when selecting the field. |
The Maximum function can help you determine the amount of the largest sale within a given period. |
|||||||||||||||||||||||||||||||||
Examines all values contained within the specified column of a Table object and returns the lowest value contained within that column. Use the All Rows option when selecting the field. |
The Minimum function can help you determine the amount of the smallest sale within a given period. |
|||||||||||||||||||||||||||||||||
Returns the minutes value for a specified time field. |
||||||||||||||||||||||||||||||||||
Takes the number specified in the first parameter, divides it by the second parameter and returns the remainder. |
||||||||||||||||||||||||||||||||||
Returns the number of the month for the specified date field. |
The Month function makes it easier to create a concatenated date result. |
|||||||||||||||||||||||||||||||||
Returns the month name for the specified date field. |
The Month Name function makes date results easier to read by translating the numeric values into textual representations. |
|||||||||||||||||||||||||||||||||
Raises the number specified in the first parameter to the power specified in the second. |
Using the Power function can help you keep formulas where a power must be calculated, such as with financial calculations, smaller and more usable. |
|||||||||||||||||||||||||||||||||
Generates a random number between the specified minimum and maximum. |
The Random function could be used if you wanted to create a random assignment of leads to a sales team. |
|||||||||||||||||||||||||||||||||
Searches for the specified pattern within a text field and replaces it with replacement text and returns the new string at the end of execution. If there is no matching pattern, the text is not modified. Flags can be used to provide more control and may be chained together if needed, as in 'gis'.
|
In an app dealing with contracts, you may have the need to replace each instance of <company name> with the specific value in a field. The Replace function can help you do this. |
|||||||||||||||||||||||||||||||||
Removes all spaces from a text field starting from the right side of the value. |
A concatenated field may have extra spaces at the end. The Right Trim function will remove those extra spaces. |
|||||||||||||||||||||||||||||||||
Rounds the number supplied in the first parameter to the number of digits specified in the second. |
Often, with retail transactions, you will need to round prices to the nearest cent. The Round function enables you to fulfill this requirement. |
|||||||||||||||||||||||||||||||||
Searches for the specified pattern within the text field and returns 1 or true if the pattern exists and 0 or false if the pattern does not exist. Flags can be used to provide more control and may be chained together if needed, as in 'gis'.
|
||||||||||||||||||||||||||||||||||
Returns the seconds value for a specified time field. |
||||||||||||||||||||||||||||||||||
Calculates the square root of a field value. |
Financial calculations often require square root calculations which would be exceedingly cumbersome without this function. |
|||||||||||||||||||||||||||||||||
Extracts a portion of a text object, starting at the character position Start Index and ending at the character position End Index. The indexes are 0-based, so the first character in the text has an index of 0, not 1. |
If you need to break up a large text input box into segments, one of these two functions (Sub-Text 1 and Sub-Text 2) is necessary. |
|||||||||||||||||||||||||||||||||
Extracts a portion of a text object, starting at the character position Start Index and extending for Length characters. The index is 0-based, so the first character in the text has an index of 0, not 1. |
If you need to break up a large text input box into segments, one of these two functions (Sub-Text 1 and Sub-Text 2) is necessary. |
|||||||||||||||||||||||||||||||||
Examines all values contained within the specified column of a Table object and returns the sum of all values contained within that column. Use the All Rows option when selecting the field. |
The Sum function can help you get a grand total of all sales in a given period. |
|||||||||||||||||||||||||||||||||
Returns the difference between two time fields and returns the result in the specified unit of time.
•Time 1 - First time field. •Time 2 - Second time field. •Units - The unit of time in which the result is returned.
The units available for apps and reports are as follows:
|
The units 'mm' and 'ss' cannot be used on their own to return the difference in minutes or seconds.
To return the difference between two time fields in total minutes or seconds use the Date Difference function. |
|||||||||||||||||||||||||||||||||
Removes all leading and trailing spaces from a text field. |
||||||||||||||||||||||||||||||||||
Converts all characters to upper case. |
Use the Upper Case function if the database with which you integrate has schema's that require upper case input. |
|||||||||||||||||||||||||||||||||
Converts characters back from the ASCII character set to the characters they are referring to. Select the text you wish to decode and this function will do the rest.
URLs can only be sent over the Internet using a limited character set. If you want to use a value from the app and pass it as parameter as part of a URL, you need to encode the value first before you append it to a URL. URL encoding converts the URL into a valid format. URL encoding replaces unsafe characters with "%" followed by two hexadecimal digits corresponding to the character values in the ISO-8859-1 character set. URL encoding normally replaces a space with a %20. |
||||||||||||||||||||||||||||||||||
If you wish to encode a message or Web address for safe submission over the internet use the URL – Encode function. The text field is where you select what to encode.
URLs can only be sent over the Internet using a limited character set. If you want to use a value from the app and pass it as parameter as part of a URL, you need to encode the value first before you append it to a URL. URL encoding converts the URL into a valid format. URL encoding replaces unsafe characters with "%" followed by two hexadecimal digits corresponding to the character values in the ISO-8859-1 character set. URL encoding normally replaces a space with a %20. |
||||||||||||||||||||||||||||||||||
UTC (or, Coordinated Universal Time) converts a time value to the UTC. In the Formula Editor, in the line beginning with 'Date:' insert a time field that you wish to convert to the UTC. |
You may have a support inquiry app that is used internationally or across time zones and requires an accurate response time. The UTC function can help you meet your response time goals. |
|||||||||||||||||||||||||||||||||
Returns the name of the day of the week (Monday, Tuesday, ...) for the specified date field. |
The Weekday Name function is useful for reservation apps that include future dates. |
|||||||||||||||||||||||||||||||||
Returns the number of the day of the week for the specified date field. (Sunday = 0 , Monday = 1, etc.) |
||||||||||||||||||||||||||||||||||
Returns the year for the specified date field. |
The Year function is useful for reservation apps that include future dates. |
Return to: Using the Formula and Condition Editor, Designing an App