Perform calculations on-demand within nested fields

By default, formulas can reference fields from the same nested row where the current field is located or parent levels only. With only a few extra steps, you can also include data from previously entered rows within nested structures, by creating "tracking" fields for background calculations.

Example 1

A field technician manages a field drilling log throughout the day. Each time additional drilling is performed, the technician adds a new item in the nested field with the new measurement. The drilling interval between measurements is automatically calculated when the new item is added.

Instructions

1. Customize your application (e.g. Groundwater Well Drilling).
2. In the Form section, add a Nested field.
   
3. Click the button "Customize nested form."
   
4. Add the necessary numeric fields (e.g. Depth Drilled (m), Last Depth (m), and Interval (m)). Please note that the Last Depth (m) field is necessary for background calculation purposes, but can be hidden from the user to prevent confusion.
   
5. Add another numeric field at the top (e.g. Entry Number).
6. Beside the "Type" dropdown, click "Abc" and select "Formula calculation."
   
7. Type in the formula. In this example, the formula rownum() will capture the unique number for each row, starting with 1. This will help determine the last entered depth drilled measurement in the nested structure.
   
8. Click save.
9. In the Form section, add a new numeric field at the bottom (e.g. Last Depth Drilled (m)). This will be the "tracking" field at the top level of the form, not within the nested structure, to properly capture the most recent depth drilled measurement.
   Please note that this field is used for background calculation purposes, and does not need to be visible to the user.
   
10. For the field name, set it to something easy to remember such as "LastDepthTop."
11. Beside the "Type" dropdown, click "Abc" and select "Formula calculation."
    
12. Type in: 'last(FieldDrillingLog.Depth, FieldDrillingLog.EntryNumber)'. This formula determines the last record entered within the nested structure "FieldDrillingLog" by looking at the "Entry Number" field. It then retrieves the value entered in the field "Depth", and stores it.
    Each time a new record is entered, this field's value will be automatically re-calculated.
    
13. Save the form.
14. Go back into the nested field and select "Last Depth (m)." In the "Default value" of the field, click "Abc" and select "Calculated value."
    
15. Enter the name of the field created in step #9 (e.g. LastDepthTop). This field will retrieve the last drilled depth measurement that's currently stored on the top level of the form, outside the nested structure.
    
16. Next, select the field "Interval (m)". Beside the "Type" dropdown, click "Abc" and select "Formula calculation."
    
17. Type in the formula. In this example, the interval is calculated by taking the current depth drilled measurement, and deducting the last depth drilled measurement.
    
18. Click save.
19. Save the form, and publish the application. Here's the final look: