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.

Read More

Get Japanese Address from post code using Zoho Creator Deluge

The requirement

Input 7 digit Japanese postal code (###-####) and get the address.

Language used

Zoho Creator Deluge

Data Source -> https://github.com/aorborc/jp_code_english/

Deluge Code

Below is the function that takes 7 digits postal code as two inputs (first 3 digits and second 4 digits) and returns an address.

map postal.get_address(string first_three, string second_four)
{
    this_address = map();
    first_three_len = ifnull(input.first_three,"").length();
    second_four_len = ifnull(input.second_four,"").length();
    if ((first_three_len  ==  3)  &&  (second_four_len  ==  4))
    {
        postal_code = input.first_three + input.second_four;
        data_url = "https://raw.githubusercontent.com/aorborc/jp_code_english/0804e74d63ec902eccfcf847ea49a0a6a7488d16/data/" + input.first_three + ".js";
        str = getUrl(data_url);
        value_map = str.toMap();
        my_map = (value_map.get(postal_code)).toMap();
        jp_address = ((my_map.get("perfecture_jp")) + my_map.get("jp_add_1")) + my_map.get("jp_add_2");
        eng_address = ((my_map.get("prefecture")) +" " +  my_map.get("city")) + " " +  my_map.get("town");
        this_address.put("jp_address", jp_address);
        this_address.put("eng_address", eng_address);
    }
    return this_address;
}

Images

Screen Shot 2017-02-07 at 8.38.49 AMScreen Shot 2017-02-11 at 6.07.50 PM

Read More