Show/Hide Toolbars

PerfectApps™ Help Guide

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

Absolute

Returns the value of a number without regard to its sign. Absolute(2) returns 2 as does Absolute(-2).


Average

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.


Ceiling

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.

Concatenation

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.

Count

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.

DateAdd

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:

 


Apps

Reports


y - years

yy - years


l - months

q - quarter


w - weeks

m - months


d - days

wk - weeks


wd - working days

d - days


h - hours

hh - hours


m - minutes

n - minutes


s - seconds

s - seconds



ms - milliseconds

 

flag_green

It is possible to force a subtraction by having a "-" in front of the amount. For example, if the amount is "-3" then three days will be subtracted rather than added.

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.

Date Difference

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:

 


Apps

Reports


y - years

yy - years


l - months

q - quarter


w - weeks

m - months


d - days

wk - weeks


wd - working days

d - days


h - hours

hh - hours


m - minutes

n - minutes


s - seconds

s - seconds



ms - milliseconds


Day

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.

Extract QueryString Parameter

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.


Floor

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.

Get Custom Parameter

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.

Get External Call Parameter

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.

Get URL Parameter

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.


Hours

Returns the hours value for a specified time field.


Index Of

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.


Join

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.

Last Index Of

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.

Left Trim

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.

Length

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.

Lower Case

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.

Maximum

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.

Minimum

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.

Minutes

Returns the minutes value for a specified time field.


Modulo

Takes the number specified in the first parameter, divides it by the second parameter and returns the remainder.


Month

Returns the number of the month for the specified date field.

The Month function makes it easier to create a concatenated date result.

Month Name

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.

Power

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.

Random

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.

Replace

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'.

 

g

Replaces all occurrences of a pattern rather than just the first one.

i

The function is executed without case sensitivity.

s

Within the pattern or replace text parameters, the dot '.' character matches new-line characters.

m

The caret (^) character and dollar sign ($) match before and after new-line characters. (This modifier corresponds to the multiline property of the RegExp instance.)

x

White space characters are ignored in the pattern so that you can write more readable constructors.

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.

Right Trim

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.

Round

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.

Search

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'.

 

g

Replaces all occurrences of a pattern rather than just the first one.

i

The function is executed without case sensitivity.

s

Within the pattern or replace text parameters, the dot '.' character matches new-line characters.

m

The caret (^) character and dollar sign ($) match before and after new-line characters. (This modifier corresponds to the multiline property of the RegExp instance.)

x

White space characters are ignored in the pattern so that you can write more readable constructors.


Seconds

Returns the seconds value for a specified time field.


Square Root

Calculates the square root of a field value.

Financial calculations often require square root calculations which would be exceedingly cumbersome without this function.

Sub-Text 1

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.

Sub-Text 2

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.

Sum

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.

Time Difference

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:

 

Unit

Data Returned

hh

Hours

hh:mm

Hours : Minutes

hh:mm:ss

Hours : Minutes : Seconds



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.

Trim

Removes all leading and trailing spaces from a text field.


Upper Case

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.

URL-Decode

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.


URL-Encode

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

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.

Weekday Name

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.

Weekday Number

Returns the number of the day of the week for the specified date field. (Sunday = 0 , Monday = 1, etc.)


Year

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