How to fetch a field value from the row above in a “Sub-form”

The Question:

Is there a way to fetch a field value from the row above in a subform and input into the row below?

The Answer:

You need to think about the ways to identify the previous row. One way is to assign a custom row ID (On Add Row) for each row and get value based on that row ID. Another way is to get value based on multiple field value combo (Product Category and Product, for example). Depending on whichever way works best for you, you can write the script.

Below workflow will allow you to assign a custom row ID and get values based on that.

1) Create the Row ID field in the SubForm (Row_ID as field deluge name)

2) In the parent Form (where the SubForm is placed) go to On Add Row of the SubForm field and add the below script

  1. max_row_id = 0;
  2. //Find the previous max row ID
  3. for each subform_row in input.SubForm
  4. {
  5.       if(subform_row.Row_ID > max_row_id)
  6.       {
  7.             max_row_id = subform_row.Row_ID;
  8.       }
  9. }
  10. //Assign the row id for current row
  11. row.Row_ID = max_row_id + 1;

3) In the on user input of SubForm’s field, you can identify the previous row id and then assign the value to the current row. Below script does exactly that.

  1. prev_row_id = row.Row_ID – 1;
  2. //Find the previous max row ID
  3. for each subform_row in input.SubForm
  4. {
  5.       if(subform_row.Row_ID == prev_row_id)
  6.       {
  7.             row.Field_Name = subform_row.Another_Field //add expressions here
  8.       }
  9. }

If a row above is deleted, you will need to handle delete action as well.