Calculations

Tap Forms has a special field type called Calculation.

For basic math, use the following operators:

Operator	Symbol
Add	+
Subtract	-
Multiply	*
Divide	/
Logical AND	&
Logical OR	~
Modulo	%

The following mathematical expressions in the Calculation field formula editor are supported:

*In Tap Forms 5.2, for international regions which use a comma for the decimal point, you will need to now use a comma in all the numbers you use in your formulas. You will also need to use a semi-colon character as the delimiter between parameters in function calls. Tap Forms will do this for you when you choose a function, but you will have to go back and edit old formulas to use the semi-colon delimiter between function parameters.

!If you don’t provide a value for every field in your formula, you will receive an empty result for the Calculation field. To resolve this issue, you can either set a Default Value for all the fields used in the formula, or you can use the IFEMPTY() function to return a default value if none is provided. For example IFEMPTY(Price; 0; Price) which will return 0 if the Price field is empty and the actual Price value if it’s not empty.

Date and Time

Function	Description	Notes	Return Type
DATE(X; "yyyy-MMM-dd")	Displays the date in a readable format.	DATE(X; "yyyy-MMM-dd hh:mm:ss a") displays the date with the date and time. The second parameter is for customizing the format. Please see the Unicode Technical Standard #35 for pattern syntax and examples.	Text
DATEADD(Date;Y;M;W;D;H;M;S)	Adds the date components to the specified date.	For example: DATEADD(Date;0;3;0;2;0;0;0) will add 3 months and 2 days to the specified date. Y = Years, M = Months, W = Weeks, D = Days, H = Hours, M = Minutes, S = Seconds.	Date
DATEVALUE(A; "yyyy-MM-dd")	Returns a date value for the specified date text.	For example: DATEVALUE("2017-12-31"; "yyyy-MM-dd") returns the date Dec 31, 2017.	Date
DAYS(X;Y)	Number of days between dates	DAYS(X;Y) returns the number of days between the start and end dates. e.g. DAYS(Start Date; End Date).	Number
EOMONTH(Date;X)	Returns the date for the last day of the month	Returns the last day of the month X number of months before or after the specified date.	Date
HOURS(X;Y)	Number of hours between dates	HOURS(X;Y) returns the number of hours between the start and end dates. e.g. HOURS(Start Date; End Date).	Number
MINUTES(X;Y)	Number of minutes between dates	MINUTES(X;Y) returns the number of minutes between the start and end dates. e.g. MINUTES(Start Date; End Date).	Number
MMDD(X;Y)	Number of months and days between dates	MMDD(X;Y) returns the number of months and days between the start and end dates as a text value. e.g. 1 month, 3 days.	Text
MONTHS(X;Y)	Number of months between dates	MONTHS(X;Y) returns the number of months between the start and end dates. e.g. MONTHS(Start Date; End Date).	Number
NEWDATE(Y;M;W;WD;WO;D;H;M;S)	Creates a date given the specified components.	For example: NEWDATE(2017;11;0;0;0;22;0;0;0) will create a date for 2017-11-22. Y = Year, M = Month, W = Week of Year, WD = Weekday, WO = Week Ordinal, D = Days, H = Hours, M = Minutes, S = Seconds.	Date
NOW()	Inserts the current date & time.	Inserts the current date & time in Unix Epoch date format, which is the number of seconds elapsed since January 1, 1970 00.00.00 GMT.	Date
TODAY()	Inserts the current date.	Inserts the current date in Unix Epoch date format, which is the number of seconds elapsed since January 1, 1970 00.00.00 GMT.	Date
WEEKDAYS(X;Y)	Number of weekdays between dates	WEEKDAYS(X;Y) returns the number of weekdays between the start and end dates. e.g. WEEKDAYS(Start Date; End Date).	Number
WEEKNUM(X)	The week number of the year.	Returns the week number of the year from 1 to 52.	Number
YEARS(X;Y)	Number of years between dates	YEARS(X;Y) returns the number of years between the start and end dates. e.g. YEARS(Start Date; End Date).	Number
YYMM(X;Y)	Number of years and months between dates	YYMM(X;Y) returns the number of years and months between the start and end dates as a text value. e.g. 1 year, 2 months	Text
YYMMDD(X;Y)	Number of years, months and days between dates	YYMMDD(X;Y) returns the number of years, months and days between the start and end dates as a text value. e.g. 2 years, 1 month, 3 days.	Text

Conditionals

Function	Description	Notes	Return Type
IF(X;Y;Z)	Provides branching.	IF(X;Y;Z) provides branching capability. If X is not 0, then it returns Y, else it returns Z.	Text/Number/Date
IFEMPTY(X;Y;Z)	Checks if X is empty.	IFEMPTY(X;Y;Z) checks for empty value of X. If X is empty, then it returns Y, else it returns Z.	Text/Number/Date
IFEQUAL(A;B;C;D)	If A and B are equal, return C, otherwise return D.	IFEQUAL(A;B;C;D) will return C if A and B are equal. If A and B are not equal, returns D. A and B must be text.	Text/Number/Date
IFNOTEMPTY(X;Y;Z)	Checks if X is not empty.	IFNOTEMPTY(X;Y;Z) checks to see if X is not empty. If X is not empty, then it returns Y, else it returns Z.	Text/Number/Date

Text

Function	Description	Notes	Return Type
CONCAT(X;Y;...)	Joins strings together.	CONCAT("abc";"def";...) function returns the combined string “abcdef”. There is no limit on the number of parameters.	Text
FORMAT(X; "#.00")	Customize the display of numeric values.	FORMAT(X; "#.00") displays the value of X using the provided number format. Please see the Unicode Technical Standard #35 for pattern syntax and examples.	Text
LEFT(A; X)	Returns the left most X characters from the text A.	For example, LEFT("Tap Forms"; 3) returns the value “Tap”.	Text
LENGTH(A)	Returns the length of A.	Returns the number of characters in the text value A.	Text
POS(A; B)	Returns the position of B within the text A.	For example, POS("Tap Forms"; "Forms") returns the numeric value 5.	Number
RIGHT(A; X)	Returns the right most X characters from the text A.	For example, RIGHT("Tap Forms"; 5) returns the value “Forms”.	Text
STR2NUM(A)	Return the numeric value of the specified text value A.	STR2NUM("25") returns the number 25. STR2NUM("11.5") return the number 11.5.	Number
SUBSTR(A; X; Y)	Returns characters from A for the given range.	Returns a string containing the characters from A for the specified range. X is the starting position. Y is the length of characters to return.	Text
UUID()	Returns a unique identifier.	Generates a Universally Unique Identifer (UUID). For example A432C2F2-A662-545B-16AD-0A357A3E6E5F.	Text

Math and Trigonometry

Function	Description	Notes	Return Type
ABS(X)	Absolute value of X.	ABS(X) returns the absolute value. e.g. ABS(4) = 4 and ABS(-4) = 4.	Number
ATAN(X)	Arctangent of X.	ATAN(X) returns the arctangent value of X.	Number
AVG(X;Y;...)	Returns the average of the parameters.	AVG(X;Y) returns the average of X and Y. e.g. AVG(5; 3) is 3.5.	Number
CEIL(X)	Rounds the value of X up.	CEIL(X) rounds up the value of X to the nearest integer. e.g. CEIL(-3.2) = -3, CEIL(3.2) = 4.	Number
COS(X)	Cosine of X	COS(X) returns the cosine of the angle X in radians.	Number
COSH(X)	Cosinus Hyperbolic function.	COSH(X) returns the cosinus hyperbolic value of X.	Number
COTAN(X)	Cotangent function.	COTAN(X) returns the cotangent of X.	Number
EXP(X)	Exponent function.	EXP(X) computes e**x, the base-e exponential of x.	Number
FLOOR(X)	Rounds the value of X down.	FLOOR(X) rounds down the value of X to the nearest integer. e.g. FLOOR(-3.2) = -4, FLOOR(3.2) = 3.	Number
INTPOW(X;Y)	Raises X to the power of Y returning an integer.	INTPOW(X;Y) raises X to the power of Y returning an integer. e.g. INTPOW(2; 3) = 8 and INTPOW(2; 3.4) = 8 also.	Number
LN(X)	Natural log function.	LN(X) natural log function.	Number
LOG(X)	10 based log function.	LOG(X) 10 based log function.	Number
LOGN(X;Y)	Log base X of Y function.	LOGN(X;Y) returns the log base X of Y. e.g. LOGN(10; 100) = 2.	Number
MAX(X;Y;...)	Returns the maximum of the parameters.	MAX(X;Y) returns the maximum of X and Y. e.g. MAX(2; 3) is 3.	Number
MIN(X;Y;...)	Returns the minimum of the parameters.	MIN(X;Y) returns the minimum of X and Y. e.g. MIN(2; 3) is 2.	Number
POW(X;Y)	Raises X to the power of Y.	POW(X;Y) raises X to the power of Y. Supports decimal values.	Number
RND()	Generates a random number between 0 and 1.	To generate a random number between 0 and 100, just multiply the result by 100.	Number
SIGN(X)	Returns -1 if X<0; +1 if X>0, and 0 if X=0.	SIGN(X) returns -1 if X<0; +1 if X>0, and 0 if X=0.	Number
SIN(X)	Sin of X	SIN(X) returns the sine of the angle X in radians.	Number
SINH(X)	Sinus Hyperbolic function.	SINH(X) returns the sinus hyperbolic value of X.	Number
SQR(X)	Square of X	SQR(X) returns the square of the value of X.	Number
SQRT(X)	Square root of X.	SQRT(X) returns the square root of X.	Number
SUM(X;Y;...)	Returns the sum of the parameters.	SUM(2;3;5;...) Returns the sum of it’s arguments. There is no limit on the number of parameters.	Number
TAN(X)	Tangent function.	TAN(X) tangent function.	Number
TRUNC(X)	Truncates the fractional part of X.	TRUNC(X) discards the fractional part of a number. e.g. TRUNC(-3.2) is -3, TRUNC(3.2) is 3.	Number

Text Concatenation

In addition to the above mathematical expressions, Tap Forms also supports text concatenation. With this feature, you can create Calculation fields which return a Text value instead of a Number value.

For example, you could create a Calculation field called Full Name (or whatever you want to call it) which has the following formula:

First Name + “ “ + Last Name

As long as you set the Result Type property on the Formula Editor to Text, then Tap Forms will evaluate the above formula and return the string with the First Name and Last Name fields joined together with a space character in between. It’s especially useful for creating labels. You can keep your First Name and Last Name fields as separate fields, but for printing labels, you would want them to be joined together into a Full Name field to make your labels look nicer.

Eliminating blank lines from a mailing address label if certain values are empty

First Name + " " + Last Name + "\r" +
IFNOTEMPTY(Company; Company + "\r"; "" ) +
Address 1 + "\r" +
IFNOTEMPTY(Address 2; Address 2 + "\r"; "" ) +
City + ", " + Province + "  " + Postal Code

In the above example, the IFNOTEMPTY ( Company; Company + “\r”; “” ) part checks to see if the Company field is not empty. If it has a value, it returns the value in the Company field and adds a linefeed character to it. If it has no value, it simply returns nothing (an empty string).

The "\r" part inserts a new line into the result.

Quoting Special Characters

There are some special characters in Tap Forms which require you to prefix them with a \ character. The following is a list of characters that require escaping in order to be used within quotes:

", ,, and ;

For example,

CONCAT("Last Name\, First Name")

The comma between Last Name and First Name must be escaped with the \ character.

Copyright 2016, Tap Zapp Software Inc. All Rights Reserved.