The Calculation tool allows the mobile user to perform operations on one or more previously entered fields in a form. doForms provides three different types of calculations: Numeric, Text, and Date/Time. Calculations can also be used to set fields equal to other fields. This will allow you to put the customer name on every page of your form. You can also set calculations equal to User Variables or Account Variables. You can set a User Variable up requiring the mobile user to enter their employee ID the next time they use doForms. The value is stored on their device and the calculation field will automatically be replaced with the employee ID each time the form is opened.
- Numeric calculations: Performs basic arithmetic calculations (addition, subtraction, multiplication, and division) between one or more fields to display a numeric result. You can also use the calculation tool to set the field equal to date parts like day of the month or month number. IF you create user variables you can specify a numeric variable. You will see this variable in your field pick list. Their is a tax rate account variable. You can use this variable in calculations when building invoices. Then if you change the tax rate variable all of your forms will automatically use the new tax rate. You can create and manage variables in the manage section.
- Text calculations: You can either combine fields into a single string or just use this calculation to set a field equal to another field or a user or account variable. You can set the field equal to date parts like day of the week. You can also set the field equal to your mobile users name or Mobile ID. Text calculations allow you to also numeric fields. Create a string that says I sold 10 apples. Where 10 is from the numeric field number sold.
Date/Time calculations: Add or subtract dates and times to display either:
- A value in decimal hours.
- A value using the HH:MM:SS fomat.
This option can be very useful for recording employee work hours.
A numeric calculation allows you to perform arithmetic (= + - / * ) operations on one or more previously entered numeric question values in the form. The result of the arithmetic expression is stored in the calculation field.
In the example below, we have created a repeatable container for entering a list of parts used in a particular task. Each part has a description, unit price, and quantity. The Calculation tool will be used to create a numeric expression that multiplies unit price by quantity to yield an extended price:
(Unit price) * (Quantity) = (Extended price)
To begin the process, we inserted the repeatable container tool (with caption Item), and then inserted into it a Text tool (with caption Description), and two Numeric tools (with captions Unit Price and Quantity). These are highlighted in red, below.
We then moved a Calculations tool into the repeatable container, and entered a caption of Extended Price (highlighted in green, below). We also assigned it a dollar sign ($) Currency symbol, and entered a value of two (2) Decimal places.
Now we need to enter the Expression parameters for the Extended price calculation:
- The Operator drop-down is the equals sign (=).
- From the Field drop-down, select the Unit price field (highlighted in red, below), and then click the Add expression link (highlighted in green, below):
A new Operator drop-down and Field drop-down will appear (highlighted in red, below). Select the multiplication sign (*) from the Operator drop-down, and then select the Quantity field from the Field drop-down.
This final expression:
= Unit price * Quantity
yields a value that will be displayed in the Extended price field. In the example below, the mobile form user has entered a Unit price of $9.00, and a Quantity of 10. The doForms form has performed the calculation and entered the value of $90.00 in the Extended price field.
Sum and Count operators
If your form contains a Repeatable or Table container, you can use Sum or Count functions in a calculation placed outside the Repeatable or Table to sum or count numeric fields inside the Repeatable or Table. (e.g., to calculate a total or to count the number of times data was entered into a field for division of the total to calculate an average).
As an example, we can Sum the value of all the Item_Price calculations to get a subtotal.
We can then multiple the subtotal times a tax rate (a constant) to calculate the sales tax.
And finally add the Subtotal and Sales tax to get a Total Price.
If we want to get a count of the number of items ordered (i.e., number of Item_Price fields that were calculated), we can use the Count function which works the same way as the Sum function, but returns an integer count.
Sum and Count in a Checklist
If your form contains a Table configured as a checklist, you can use Sum or Count functions in a calculation placed inside the Table to sum or count all the duplicate records in the checklist. Show duplicate values should not be selected.
Example: A checklist of orders with the customer as the Lookup field in the checklist Data lookup: If there is an order amount field in the checklist, use the Sum of the order amount to calculate the total order amount for each customer. The records in the data lookup are grouped by customer because customer is the field selected as the Lookup field.
Parentheses group ( )
In other circumstances you might consider using a parentheses group ( ) to organize a complicated expression. If needed, you can nest parentheses within parentheses. The example below shows an alternative way to calculate the Total_Price without explicitly calculating a subtotal and tax.
Numeric system fields
- Day: The number of the day of the month.
- Week: The week number of the date. Uses Sunday as the first day of the week. Week 1 begins on January 1. Follows the Excel WEEKNBR function.
- Month: The number of the month.
- Year: The year.
- Duration hours (Scheduler): The number of hours scheduled for the job. Whole number. Combine with Duration minutes for the complete duration.
- Duration minutes (Scheduler): The number of minutes scheduled for the job. Combine with Duration hours for the complete duration.
- Total duration in hours (Scheduler): The total time scheduled for the job in hours. Decimal portion indicates minutes as a fraction of an hour.
- Total duration in minutes (Scheduler): The total time scheduled for the job in minutes.
A text calculation joins together multiple text fields and displays these fields within another field. For example, you may wish to combine a "First name" field and a "Last name" field into one field called "Full Name." You can also use a text calculation as an input source to launch Google Maps from a form.
The following text calculation joins together four fields, Address, City, State, and Zip code into one field called Full address.
In addition to text fields, you may choose to use Mobile User IDs, Nicknames, Constants (text strings), and spaces in text calculations. The following image shows available fields for use in text calculations:
Note that you may choose to use a text calculation as an input source to launch Google Maps from a form. Using our example from above, we have already created a text calculation called Full address. Insert a Google Maps action button into the form, and reference the Full address field as its Relevant question. Fill in the other settings for the action button using the image below as an example:
- Plus (+): Combine two text values into one.
- Day: Get the name of the day set in a Date/Time field.
- Week: Get the week number set in a Date/Time field.
- Month: Get the name of the month set in a Date/Time field.
- Year: Get the year number set in a a Date/Time field.
Text system fields
- Space: Adds a blank space between two fields.
- Nickname: The nickname of the mobile user.
- Mobile ID: The mobile number of the mobile user.
- Device ID: The unique system ID of the mobile user. Format will vary depending on the mobile device.
- Mobile user email: The email address of the mobile user.
- Web user email: The email address of the web user.
- User variables: Fields beginning with "var_" are user variables.
The calculation tool can also be used with Date/Time values.
- Subtract (-) a Date/Time from a Date/Time to produce a difference in decimal hours.
- Date - Date (Jan 10 2020 - Jan 8 2020 = 48)
- Time - Time (9:00 AM - 8:00 AM = 1)
- Date/Time - Date/Time (Jan 10 8:00 AM - Jan 10 5:00 AM = 3)
- Date/Time - Date (Jan 10 2020 8:00 AM - Jan 8 2020 = 56)
- Add (+) or Subtract (-) decimal hours from a Date/Time to produce a new Date/Time.
- Date (Jan 10 2020 + 10 = Jan 10 2020 10:00 AM)
- Time (10:00 AM + 4 = 2:00 PM)
- Date/Time (Jan 10 2020 2:00 PM - 5 = Jan 10 2020 9 AM)
Display time values can be formatted as hh:mm in the mobile app by using the Display time values as hh:mm property. Note, however, that the actual values will be saved as decimal-hour
When using the Sum operator to sum up a list of differences in decimal-hours, be sure to set Calculation Type to Numeric, as these differences are stored as numbers.
Date/Time system fields
- Start date (Scheduler): The date (with no time value) the scheduled job begins.
- Start time (Scheduler): The time (with no date value) the scheduled job begins.
- Start date/time (Scheduler): The date and time the scheduled job begins.
- User variables: Fields beginning with "var_" are user variables.
Use hidden fields for variables
In other circumstances you might consider using the Hide (Mobile) setting in numeric questions and calculations to store data values which you do not want displayed to the user. As an example, suppose you wanted to pass a tax rate to the form above. You could create a numeric field called Tax_Rate. By making the field hidden, it would not be explicitly displayed in the form, but could still be used in the tax calculation. Hidden fields are also handy for very complex calculations that you want to do in parts.
Calculation type: Specifies the type of calculation created: Numeric, Text, or Date/Time.
Show account variables: In the expression builder, show account variables.
Show user variables: In the expression builder, show user variables.
Show scheduler variables: In the expression builder, show fields that are only populated when a dispatch is created through the dispatch scheduler.
Expression: Used to build the formula based on previously entered number values. Note that the calculation tool needs to be placed after any question fields that will be used in the calculation.
Decimal place: Indicates how many decimal places should be displayed in Numeric and Date/Time calculations. Values are rounded using the round half away from zero method.
Round: Select the direction to round if the calculation has more decimal places than specified by Decimal place. Standard (default) rounds up when the remaining value is 5 or greater. Down rounds down in all cases. Available on request.
Use percent: Treat a numeric calculation as a percentage.
Use currency symbol: For numeric calculations only, this setting places a currency symbol in front of the value. Note that these symbols are for display and PDF report purposes only. The currency symbol is not exported to Excel, CSV or other Save as formats. The currency is, however, exported via web services and Sync & Save.
Show commas: Include thousand separators.
Negative number: These settings determine what the answer looks like if the numeric entered is negative. If "Red Text" is selected it will turn the answer red and if "Bracket" is selected it will put the negative number into () brackets. Both settings can be applied or the user can select one.
Encrypt field: Encrypt the value.
Display time values as hh:mm: For Date/Time calculations only, this setting formats time values as hh:mm in the mobile form. Note, however, that the actual values will be saved as decimal-hours.
Display time values as hh:mm:ss: Same as above, but includes seconds.
Display date values only: Does not display the time portion of a Date/Time calculation.