Formulas
Formulas let you link fields together and perform calculations on them. All formulas follow the same format and are made up as follows:
=formula(tag)[n]{p} e.g. =SUM(tagOne)[2]{3}
The tag can be anything you like, any text that identifies the source fields for the foumula. So if you want to add up all the amount fields on your form, set the tag for all these fields to a value that means something to you e.g. tagAmount. Then in the field you want to hold the result of the addition enter the formula =sum(tagAmount).
The number of decimal places to calculate amounts is stored between square brackets, [0] is the default and mean no decimal places, usually [2] is used for monetary amounts.
The order of precedence is held in curly brackets. This is only needed if you want a formula to be the result of another formula. In this case Form Maker needs to know to do one formula before the other one. For example you may want to sum up all the amount fields, then apply a percentage to it. In this case the summing up could be done using =sum(tag)[2]{1}, the field with this formula would then need a tag to make it the source field for another formula. The result of the multiplication could then be specified as =multiply(tagTwo)[2]{2}.
Here are a list of formulas available:
value: Copy the contents of the field e.g. =value(tagOne) will copy the contents of the field with its tag set to tagOne.
sum: Add together the contents of the fields as numbers e.g. =sum(tagOne) will add up all the contents of the fields with their tags set to tagOne.
multiply: Multiply together the contents of the fields as numbers e.g. =multiply(tagOne) will multiply together all the contents of the fields with their tags set to tagOne.
difference: The difference between the minimum value and maximum value of fields with the tag. e.g. =difference(tagOne) will find the minimum and maximum value of the contents of the fields with their tags set to tagOne and calculate the difference between them. This is particularly useful for dates and times as it can be used to calculate time worked for example. This could then be used to calculate a wage by multiplying by a wage rate per time period, e.g. dollars per hour worked.
average: Average the contents of the fields as numbers e.g. =average(tagOne) will average all the contents of the fields with their tags set to tagOne.
negative: The negative value of another field e.g. =negative(tagOne) will be the negative of the field with tag tagOne.
reciprocal: The reciprocal value of another field e.g. = reciprocal(tagOne) will be the reciprocal of the field with tag tagOne.
index: Lets you link together two pickers or segmented controls so as you change the selection in one, it is reflected in the same selection in the other. e.g. =index(tagOne) will make this picker or segmented control be set to the same selection position as that of the picker or segmented control with the tag tagOne.
Subtraction and Division
If you put a hyphen (minus sign) at the start of a tag it will be interpreted as a negative number when a formula is evaluated. This is helpful if you want a field to contain a positive number, but be treated as negative in a total. For example:
Gross Value Tag: T1
Discount Amount Tag: -T1
Net Value Formula: =Sum(T1)
This would result in for example:
Gross Value 100
Discount Amount 10
Net Value 90
If you put a slash (division sign) at the start of a tag it will be interpreted as a reciprocal number (1/x) when a formula is evaluated. This is helpful if you want a field to contain a whole number, but be treated as a fraction in a multiplication. For example:
Capital Amount Tag: D1
Shareholders Tag: /D1
Share Value Formula: =Multiply(D1)
This would result in for example:
Capital Amount 500
Shareholders 20
Share Value 25
If you put a hash tag # (called a pound sign in the U.S.) at the start of a tag it will be interpreted as a percentage number (x/100) when a formula is evaluated. This is helpful if you want a field to contain a whole number, but be treated as a percentage in a multiplication. For example:
Invoice Amount Tag: D1;D2
Tax Percentage Tag: #D1
Tax Value Formula: =Multiply(D1) Tag:D2
Total Amount Formula: =Sum(D2){2}
This would result in for example:
Invoice Amount 500
Tax Percentage 10
Tax Value 50
Total Amount 550
We have taken this example slightly further with a Total Amount field. Note that the Invoice Amount now has two tags D1;D2 separated by a semi-colon. This makes it available to both the =Multiply(D1) to calculate the tax amount, and also the =Sum(D2){2} to calculate the Total Amount (being the sum of the Invoice Amount and the Tax Amount). Note that the Sum needs to take place after the Multiply (i.e. calculate the Tax Amount first and then the Total Amount), this is controlled by the {2} in the curly brackets of the =Sum formula, which indicates that this formula takes place second.
Here is a similar example for calculating a percentage discount. This is almost the same as the previous example except the calculated percentage amount now needs to be interpreted as negative, hence the -D2 tag on the Discount Amount field.
Invoice Amount Tag: D1;D2
Discount Percentage Tag: #D1
Discount Amount Formula: =Multiply(D1) Tag:-D2
Total Invoice Amount Formula: =Sum(D2){2}
This would result in for example:
Invoice Amount 500
Discount Percentage 20
Discount Amount 100
Total Invoice Amount 400
Automatic Numbering
You can create an automatic sequence number using the formula =Autonum() - dont forget the brackets !
This formula can be used in a text field to automatically allocate a sequence number for each response.
