TEST : Simple calculation with formula field

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


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>