As we’ve just seen, Number items on a WorkMobile form have a value, which is the number in the box. Likewise, other kinds of item have values too. There are three kinds of value. The kind of value a piece of information has determines what you can and cannot do with it in a formula.
Numbers
The value of a Number item is the number in the box.
Calendar and Time items are also numbers. A Calendar’s value a day number, where day 1 is 31st December 1899. So, 30th May 2012 is day number 41,059.
A Time’s value is a fraction of a day. 0 is midnight, midday is 0.5.
One number can, therefore, represent a date and a time together. 41,059.5 is midday on the 30th May 2012.
In a formula, numbers are written simply as numbers. Numbers can be used in a variety of calculations. They can be added, subtracted, multiplied, compared, and so on.
Calendar and Time items are also numbers. A Calendar’s value a day number, where day 1 is 31st December 1899. So, 30th May 2012 is day number 41,059.
A Time’s value is a fraction of a day. 0 is midnight, midday is 0.5.
One number can, therefore, represent a date and a time together. 41,059.5 is midday on the 30th May 2012.
In a formula, numbers are written simply as numbers. Numbers can be used in a variety of calculations. They can be added, subtracted, multiplied, compared, and so on.
Text
Most form items are text. Textbox is straightforward. Drop down lists and radio buttons have a value that is the text of the selected item.
In a formula, text must be written in double quotes. For example, if we have a drop down list called Gender with the entries Male and Female, we can create a text box with a formula like:
="Hello" & IF(Gender = "Male", "sir", "madam")
Two pieces of text can be combined into one, or can be compared.
The confusing part can be when a piece of text looks like a number. Typing the same thing into a number box or a text box gives two different results. This is also why they are written differently in a formula.
Some examples:
In a formula, text must be written in double quotes. For example, if we have a drop down list called Gender with the entries Male and Female, we can create a text box with a formula like:
="Hello" & IF(Gender = "Male", "sir", "madam")
Two pieces of text can be combined into one, or can be compared.
The confusing part can be when a piece of text looks like a number. Typing the same thing into a number box or a text box gives two different results. This is also why they are written differently in a formula.
Some examples:
Formula
|
Result
|
Explanation
|
---|---|---|
= 12 + 5
|
27
|
For numbers, “+” means addition
|
="12" + "15"
|
Error
|
For text, “+” has no meaning
|
="12" & "15"
|
1215
|
“&” is “glue together”
|
=12 & 15
|
Error
|
“&” only works on text. For numbers, it has no
meaning |
=VALUE("12") + 15
|
27
|
The “VALUE” function converts text to a number.
This is useful if you have a list of numbers in a drop down list, for example |
=VALUE("twelve") + 15
|
Error
|
“VALUE” only works when the number is written
in digits – it doesn’t understand numbers written in words. You will be able to save this, but it will not work when it runs in the App |
Logical
Checkboxes have “logical” values – these values are either true or false. A checkbox is “true” if it is
ticked, “false” if it is not.
Logical values are also the results of comparing other values. More on this later.
ticked, “false” if it is not.
Logical values are also the results of comparing other values. More on this later.
Using Form Items
In most cases, you will want to use the values from other items on the form. You do this by using the
caption of the item from which you want the value.
=Age < 18
Note that this is not text data, it a name of a form item, so it doesn’t have double-quotes around it.
However, if the caption has spaces in it, then you must put single-quotes around it. Same if it has a “/”, “+”, “-“ or any other special symbol.
=’Age at last birthday’ < 18
The name needs to match exactly. If the caption is “Age”, then you must write “Age”, not “age”. The form designer will complain if it cannot find an item with the caption you’ve given.
caption of the item from which you want the value.
=Age < 18
Note that this is not text data, it a name of a form item, so it doesn’t have double-quotes around it.
However, if the caption has spaces in it, then you must put single-quotes around it. Same if it has a “/”, “+”, “-“ or any other special symbol.
=’Age at last birthday’ < 18
The name needs to match exactly. If the caption is “Age”, then you must write “Age”, not “age”. The form designer will complain if it cannot find an item with the caption you’ve given.
For Best Results
Only take values from items before (above) the one for which you are writing the formula.
Avoid having double or single quotes in captions.
Don’t have two items on a form with the same caption if you want to use them in a formula.
Watch out for double spaces in captions, or a space at the end.
Avoid having double or single quotes in captions.
Don’t have two items on a form with the same caption if you want to use them in a formula.
Watch out for double spaces in captions, or a space at the end.