Here is a simple demo to show how the formula fields can be used
We have 2 types of fields for calculations:
- Calculated - which is calculated ONLY when the record is created
- Formula - which is calculated and re-calculated each time the record is saved (created or updated)
You can use any of the functions or operations documented here: Supported math operators, formulas and functions
There is also a way to do calculations with ConfiForms Field Definition Rules
- Page to demo score calculation using 2 methods
- Using ConfiForms Rule for Field Definition to calculate the sums online
Here we have a simple form with 4 fields:
2 of number types and 3 of formula types
We have the following formulas:
- ([entry.f1] + [entry.f2])
- (ROUND([entry.f1] / [entry.f2],2))
- CONCAT("Some random: ", ((RANDOM() + [entry.f1] - [entry.f2] + NOW())))
Stored data
f1 |
f2 |
My formula 0 |
My formula 1 |
My formula 2 |
---|---|---|---|---|
3 | 7 | 0.43 | 10 | Some random: 1610439740240.355 |
8 | 9 | 0.89 | 17 | Some random: 1610439740244.929 |
9 | 8 | 1.12 | 17 | Some random: 1610439740247.042 |
.2 | .2 | Expression: [(ROUND(.2 / .2,2))] could not be calculated, Unknown operator '.' at position 8 | Expression: [(.2 + .2)] could not be calculated, Unknown operator '.' at position 2 | Expression: [CONCAT("Some random: ", ((RANDOM() + .2 - .2 + NOW())))] could not be calculated, Unknown operator '.' at position 38 |
2 | 2 | 1 | 4 | Some random: 1610439740249.637 |
1 | 2 | 0.5 | 3 | Some random: 1610439740249.370 |
33 | 2 | 16.5 | 35 | Some random: 1610439740282.655 |
1 | 32 | 0.03 | 33 | Some random: 1610439740221.738 |
1 | 2 | 0.5 | 3 | Some random: 1610439740252.318 |
1 | 2 | 0.5 | 3 | Some random: 1610439740253.536 |
1 | 1 | 1 | 2 | Some random: 1610439740255.748 |
4 | 1 | 4 | 5 | Some random: 1610439740259.599 |
123 | 123 | 1 | 246 | Some random: 1610439740257.666 |
2 | 2 | 1 | 4 | Some random: 1610439740258.469 |
3 | 4 | 0.75 | 7 | Some random: 1610439740259.744 |
3 | 4 | 0.75 | 7 | Some random: 1610439740260.090 |
3 | 2 | 1.5 | 5 | Some random: 1610439740263.065 |
33 | 32 | 1.03 | 65 | Some random: 1610439740264.463 |
5 | 5 | 1 | 10 | Some random: 1610439740265.817 |
3 | 4 | 0.75 | 7 | Some random: 1610439740277.897 |
2 | 2 | 1 | 4 | Some random: 1610439740279.449 |
1 | 2 | 0.5 | 3 | Some random: 1610439740279.214 |
12 | 12 | 1 | 24 | Some random: 1610439740281.412 |
.1 | .2 | Expression: [(ROUND(.1 / .2,2))] could not be calculated, Unknown operator '.' at position 8 | Expression: [(.1 + .2)] could not be calculated, Unknown operator '.' at position 2 | Expression: [CONCAT("Some random: ", ((RANDOM() + .1 - .2 + NOW())))] could not be calculated, Unknown operator '.' at position 38 |
1 | 3 | 0.33 | 4 | Some random: 1610439740282.801 |
1 | 2 | 0.5 | 3 | Some random: 1610439740291.212 |
1 | 2 | 0.5 | 3 | Some random: 1610439740293.730 |
34 | 56 | 0.61 | 90 | Some random: 1610439740273.838 |
3 | 4 | 0.75 | 7 | Some random: 1610439740295.726 |
1 | 1 | 1 | 2 | Some random: 1610439740297.079 |
4 | 5 | 0.8 | 9 | Some random: 1610439740297.145 |
56,56 | 54 | 56 | Expression: [(56,56 + 54)] could not be calculated, Too many numbers or variables | Some random: 56.76590721429542 |
1.1 | 4 | 0.28 | 5.1 | Some random: 1610439740298.006 |
5 | 4 | 1.25 | 9 | Some random: 1610439740302.277 |
15 | 30 | 0.5 | 45 | Some random: 1610439740287.864 |
60 | 15 | 4 | 75 | Some random: 1610439740360.687 |
1 | 2 | 0.5 | 3 | Some random: 1610439740315.947 |
100 | 200 | 0.5 | 300 | Some random: 1610439740217.748 |
2 | 33 | 0.06 | 35 | Some random: 1610439740287.733 |
1 | 1 | 1 | 2 | Some random: 1610439740319.381 |
100.10 | 100.10 | 1 | 200.2 | Some random: 1610439740320.592 |
5 | 6 | 0.83 | 11 | Some random: 1610439740328.989 |
15 | 45 | 0.33 | 60 | Some random: 1610439740300.915 |
3 | 4 | 0.75 | 7 | Some random: 1610439740330.366 |
2 | 3 | 0.67 | 5 | Some random: 1610439740331.153 |
1 | 2 | 0.5 | 3 | Some random: 1610439740352.358 |
3 | 7 | 0.43 | 10 | Some random: 1610439740351.776 |
2 | 3 | 0.67 | 5 | Some random: 1610439740354.110 |
100 | 200 | 0.5 | 300 | Some random: 1610439740256.908 |
1 | 5 | 0.2 | 6 | Some random: 1610439740353.292 |
4 | 5 | 0.8 | 9 | Some random: 1610439740357.359 |
4 | 5 | 0.8 | 9 | Some random: 1610439740359.513 |
123 | 234 | 0.53 | 357 | Some random: 1610439740250.229 |
12 | 12 | 1 | 24 | Some random: 1610439740362.975 |
3 | 4 | 0.75 | 7 | Some random: 1610439740362.942 |
3 | 2 | 1.5 | 5 | Some random: 1610439740365.799 |
11111 | 22222 | 0.5 | 33333 | Some random: 1610439729254.861 |
1 | 2 | 0.5 | 3 | Some random: 1610439740365.430 |
1 | 1 | 1 | 2 | Some random: 1610439740367.807 |
10 | 10 | 1 | 20 | Some random: 1610439740368.480 |
1 | 3 | 0.33 | 4 | Some random: 1610439740367.563 |
0.2 | 0.2 | 1 | 0.4 | Some random: 1610439740370.885 |
1 | 2 | 0.5 | 3 | Some random: 1610439740370.548 |
1 | 4 | 0.25 | 5 | Some random: 1610439740369.527 |
12 | 12 | 1 | 24 | Some random: 1610439740373.255 |
10 | 10 | 1 | 20 | Some random: 1610439740374.510 |
23 | 45 | 0.51 | 68 | Some random: 1610439740353.188 |
1 | 2 | 0.5 | 3 | Some random: 1610439740375.195 |
3 | 7 | 0.43 | 10 | Some random: 1610439740373.943 |
5345345 | 534534 | 10 | 5879879 | Some random: 1610444551189.342 |
43.54534 | 6545.456456 | 0.01 | 6589.001796 | Some random: 1610439733878.992 |
1 | 1 | 1 | 2 | Some random: 1610439740381.224 |
144 | 144 | 1 | 288 | Some random: 1610439740382.950 |
114 | 114 | 1 | 228 | Some random: 1610439740383.243 |
114 | 114 | 1 | 228 | Some random: 1610439740384.395 |
114 | 114 | 1 | 228 | Some random: 1610439740385.313 |
114 | 114 | 1 | 228 | Some random: 1610439740386.390 |
114 | 114 | 1 | 228 | Some random: 1610439740387.282 |
5 | 5 | 1 | 10 | Some random: 1610439740389.801 |
4 | 4 | 1 | 8 | Some random: 1610439740390.783 |
2 | 1 | 2 | 3 | Some random: 1610439740392.414 |
1 | 2 | 0.5 | 3 | Some random: 1610439740391.775 |
1 | 2 | 0.5 | 3 | Some random: 1610439740392.581 |
1 | 2 | 0.5 | 3 | Some random: 1610439740393.387 |
2 | 3 | 0.67 | 5 | Some random: 1610439740395.469 |
1097 | 1079 | 1.02 | 2176 | Some random: 1610439740415.564 |
Storage format for this demo:
<ac:structured-macro ac:macro-id="e44fca82-119b-4e79-b70a-1d521f0f13e3" ac:name="confiform" ac:schema-version="1"> <ac:parameter ac:name="formName">myform1</ac:parameter> <ac:rich-text-body> <ac:structured-macro ac:macro-id="9930843c-b7ee-493d-b93d-6c91cf13da7a" ac:name="confiform-entry-register" ac:schema-version="1"> <ac:parameter ac:name="embedded">true</ac:parameter> <ac:rich-text-body> <p> <br/> </p> </ac:rich-text-body> </ac:structured-macro> <p> <ac:structured-macro ac:macro-id="c1d112a1-145d-4822-9263-57e7f1d95202" ac:name="confiform-field-definition" ac:schema-version="1"> <ac:parameter ac:name="fieldName">f1</ac:parameter> <ac:parameter ac:name="fieldLabel">f1</ac:parameter> <ac:parameter ac:name="type">number</ac:parameter> <ac:parameter ac:name="required">true</ac:parameter> </ac:structured-macro> </p> <p> <ac:structured-macro ac:macro-id="5b285aed-c238-42cb-8fb0-74b504f51edd" ac:name="confiform-field-definition" ac:schema-version="1"> <ac:parameter ac:name="fieldName">f2</ac:parameter> <ac:parameter ac:name="fieldLabel">f2</ac:parameter> <ac:parameter ac:name="type">number</ac:parameter> <ac:parameter ac:name="required">true</ac:parameter> </ac:structured-macro> </p> <p> <ac:structured-macro ac:macro-id="9981f97b-d090-401d-95d2-b19d45a4df9f" ac:name="confiform-field-definition" ac:schema-version="1"> <ac:parameter ac:name="fieldName">myformula1</ac:parameter> <ac:parameter ac:name="fieldLabel">My formula 1</ac:parameter> <ac:parameter ac:name="extras">([entry.f1] + [entry.f2])</ac:parameter> <ac:parameter ac:name="type">formula</ac:parameter> </ac:structured-macro> </p> <p> <ac:structured-macro ac:macro-id="f6b5b3d7-3462-4068-b23c-2ccee3789f66" ac:name="confiform-field-definition" ac:schema-version="1"> <ac:parameter ac:name="fieldName">myformula2</ac:parameter> <ac:parameter ac:name="fieldLabel">My formula 2</ac:parameter> <ac:parameter ac:name="extras">CONCAT("Some random: ", ((RANDOM() + [entry.f1] - [entry.f2] + NOW())))</ac:parameter> <ac:parameter ac:name="type">formula</ac:parameter> </ac:structured-macro> </p> </ac:rich-text-body> </ac:structured-macro> <p>Stored data</p> <ac:structured-macro ac:macro-id="1d573801-7d23-412f-aa1e-c3c04d1fcc63" ac:name="confiform-table" ac:schema-version="1"> <ac:parameter ac:name="formName">myform1</ac:parameter> <ac:rich-text-body> <p> <ac:structured-macro ac:macro-id="dc71204e-5341-4543-af04-45ab036c827d" ac:name="confiform-field" ac:schema-version="1"> <ac:parameter ac:name="fieldName">f1</ac:parameter> </ac:structured-macro> </p> <p> <ac:structured-macro ac:macro-id="12af5c9f-b5bb-475e-b9c1-470d60b3bd6c" ac:name="confiform-field" ac:schema-version="1"> <ac:parameter ac:name="fieldName">f2</ac:parameter> </ac:structured-macro> </p> <p> <ac:structured-macro ac:macro-id="0c76996a-654f-4b77-848b-9d8b2212a85f" ac:name="confiform-field" ac:schema-version="1"> <ac:parameter ac:name="fieldName">myformula1</ac:parameter> </ac:structured-macro> </p> <p> <ac:structured-macro ac:macro-id="9a8cf996-366b-4a8b-be43-da01d32dc449" ac:name="confiform-field" ac:schema-version="1"> <ac:parameter ac:name="fieldName">myformula2</ac:parameter> </ac:structured-macro> </p> </ac:rich-text-body> </ac:structured-macro>