Formula: Possibility to access different tables via Java Code?

Options
Hofsaess_167797
Hofsaess_167797 Posts: 5
edited April 2021 in Questions

Hi all,

hope you can help me.
There is currently a function called "Apply formulas to columns" where I first have to select a table and then can do something like this:
columns['rowIndex'] = rowIndex

But I can only adjust columns in this specific table I have selected.

My question is:
Is it possible to use the "Field Formulas" Function and calculate formulas with different tables, columns and rows?
Please find attached an example script to make it clear what the purpose is:

var table1column1row1 = table['table1'].columns['column 1'].row[1]
table['table2'].columns['column 2'].row[1] = table1column1row1

Thank you :smile:

Tagged:

Best Answer

  • Sean_510793
    Sean_510793 Posts: 69 admin
    Answer ✓
    Options

    Hi @Hofsaess_167797 , unfortunately, that is not currently supported (although it's a cool idea!). From the script example, it looks like you want to set the value of table 2 based on the value of table1. Depending on how the match is made, there may be a couple of approaches:

    • If you can first combine the data via a unique value column, then use apply formula, you could look at converting the two tables to CSV, then using the Excel: Join two sheets action to combine the CSV, and finally use CSV: Apply formulas to columns. If you want to save it as a table, you can use Excel: Save Spreadsheet to Table.

    • If it's more of a lookup situation, perhaps you could first get the data from table1 and save it into a field, then use it in the apply formulas.

    Happy to help brainstorm some more if you can provide some more details on how you will match up the table values.

    Hope this is a helpful start!

Answers

  • Hofsaess_167797
    Hofsaess_167797 Posts: 5
    Options

    Hi @Sean_510793,

    thank you very much for your quick answer :) .

    I try to specify the problem I had a little bit more in detail.
    In my Bot I have 95 fix steps and 4 steps which will be looped once for every line item the Bot detects.
    The 4 steps do:
    1) lookup column1 in table 1 (based on an ID)
    2) write the value in column1 in table 2 (based on an ID)
    3) lookup column2 in table 1 (based on an ID)
    4) write the value in column 2 in table 2 (based on an ID)
    --> in case of let´s say 100 line items the Bot really took a while due to the looping.

    That was the reason why I thought about solving it with Java Code to be even faster and to get rid of the loop.

    BUT: your first proposal works perfectly fine. Now the workflow do:
    1) Create Spreadsheet from a data table for table 1 (as .csv)
    2) Create Spreadsheet from a data table for table 2 (as. csv)
    3) Excel: join two sheets (table 1 and table 2)
    4) save spreadsheet to table (to have it again as a table)
    --> no loop needed

    Once again: thank you very much for your help.

    Best regards,
    Felix

Categories