How To Create Automated Calculations in Forms

Important note: creating automated calculations requires our Workflow Editor add-on

Creating automated calculations in forms can significantly streamline data entry and analysis processes. Integrating automated calculations can save time and minimize errors.
With Labguru's automation tool (the Workflow Editor) you can incorporate automated calculations in form elements, enabling you to gather and process information efficiently.

In this guide, you will learn how to create automation that performs automatic calculations in:
- Simple Forms

- Form tables with multiple rows.
In both cases the initial steps are similar however the approach for parsing the data, retrieving the values, and updating back the calculated result is slightly different.

Step 1: Create a new Workflow with an External Trigger

Go to the Workflow Editor add-on, create a new flow, and set the trigger to "Trigerless - External". The provided External URL will be used to create a button within your form, that will activate the flow.

Now that we have the external URL, we will leave the workflow aside and jump to setting up the form fields and the button. Once done, we will come back to the newly created workflow to set up a script for retrieving data, calculating, and updating the required form fields.

Step 2: Create a Form

Add a form element to your protocol and create:

- fields for data entry

- fields that will be auto-populated when the automation kicks in.

In this example, we will create a simple Form that includes 3 fields, as presented in the image below:

The 'Avg.' field will be auto-populated with the calculated average of the values entered into the 'Value 1' and 'Value 2' fields.

Creat from input fields
When creating the form input fields ('Value 1', Value 2' and 'Avg'), make sure to name them by populating the 'Name' field, as it will be necessary for accessing these fields via the API:
Create a button
When creating a button, type the button name in the 'Btn label' field and paste the external URL (from step 1) in the 'Workflow URL' field:

Step 3: Continue with the Workflow setup

At this stage, we are getting back to the Workflow that was created in Step 1 to incorporate a script that performs the automatic calculations.

Add a Scripter Step
Retrieve the ID of the Form element.
Once the button is clicked, the ID of the Form element where the button is located is sent to the Workflow as part of the 'trigger data':

The element ID can be accessed using the following syntax:
```
element_id = variable('item')['element_id'] 
```
Retrieve the entire Form element to access its data
Using the retrieved element ID, use a GET element request to retrieve the entire Form element (in JSON format), and store it as a variable:
```
form_element = requests.get(f'{base()}/api/v1/elements/{element_id}', json={'token': token()}).json()
```
Parse the Form's data, calculate and update the Form
Once you have the Form element's JSON stored as a variable, you can parse its 'description' attribute and access the 'form_json' key, which contains the data that was entered into the form fields.
As a reference, please see below a screenshot of the form element's JSON, where you can find the 'description' attribute and the 'form_json' key:

Parsing the Form's data and retrieving the values from the fields starts with converting the "description" attribute from a String to a Dictionary, using the json.loads method:
```
# Parse the element's 'description' attribute and access the 'form_json' key to retrieve the data within the form fields:

form_data = json.loads(form_element['description'])['form_json']
print ("form_data", form_data)
```
Then, the values within the Form fields can be retrieved easily and used in calculations (in this case, average):
```
# Retrieve the values from each form field using the fields' name:

if form_data.get("Value 1"):
  value_1 = float(form_data.get("Value 1"))
  print ("value 1", value_1)
  
if form_data.get("Value 2"):
  value_2 = float(form_data.get("Value 2"))
  print ("value 2", value_2)

#Calculate the average: 
avg = float((value_1 + value_2)/2.0)
```
Next, the 'Avg' field is updated with the calculated result:
```
# Update the 'Avg' form field with the calculated result:
  update_form = requests.put(f'{base()}/api/v1/elements/{form_el["id"]}',json={
    "token": token(),
  	"form_json": 
  	  {'Avg': avg
  	  } 
  })
  
print ("update_form", update_form.status_code)
```
Please refer to the screenshot below to see the above script as a whole in the context of the Scripter step:

Advanced: automated calculation in tables with multiple rows

The above referred to simple Forms and Form tables without the "Allow duplicate rows" option.
For tables with this option enabled, as presented in the screenshot below:

data parsing is performed differently using the Form's "form_data_json" attribute which lists the table rows:

Parsing the data and retrieving the values from each row starts with converting the "form_data_json" attribute from a String to a Dictionary using the json.loads method:

form_data_json = form_element["form_data_json"]
form_data = json.loads(form_data_json)

Next, the "tables" and "table_1" keys are accessed in order to iterate over the table rows and retrieve the values from each row.
Calculating the average and updating the result in the 'Avg' field is performed as part of the iteration:

for row in form_data["tables"][0]["table_1"]:
  for field in row["fields"]:
    if "Value 1" in field["name"]:
      value_1 = float(field["value"])
      print ("value_1", value_1)
      
    if "Value 2" in field["name"]:
      value_2 = float(field["value"])
      print ("value_2", value_2)
      
    if "Avg" in field["name"]:
      update_form = requests.put(f'{base()}/api/v1/elements/{form_element["id"]}',json={
      "token": token(),
    	"form_json": {
    	  field["name"]: (value_1 + value_2)/2.0
    	  } 
      })
      print ("updated row", update_form.status_code)

Guru Tips:

When adding rows, the row number will be added to the names of the fields in the following format: "Value 1_2".
The names of the fields in the first row will stay the same as was defined when creating the fields i.e. "Value 1" (without the underscore and row number added to it).
In this example, since we have only one table within the Form element, we are accessing the key "table 1". If you have multiple tables within the Form element you might need to access also to "table_2", "table_3", etc. as follows: ["tables"][0]["table_2"]

For further information on how to use Labguru's Workflow Editor refer to our Workflow Editor User Manual