FormulaMap() : When and where?

Here is a small example.

Imagine you have two tables (Table 1 and Table 2) and you want to copy all the row values from Table 1 to Table 2 (only from Name column in my example, but you can copy more columns).

You can do it with a button using FormulaMap function. You “list” to use with the FormulaMap function will be all the rows in Table 1.

Code for the button:

[Table 1].FormulaMap(AddRow([Table 2],[Table 2].Name, ToText(CurrentValue)))

Here is an example doc:

Second button copies only rows with value “Human” in the second column (you archive it by filtering Table 1 with the Filter function).

Second button code:
[Table 1].Filter([Human or Alien?]="Human").FormulaMap(AddRow([Table 2],[Table 2].Name, ToText(CurrentValue)))

Third button copies all the row values from the Table 1 to Table 2 and then deletes copied rows from Table 1 (by using RunActions function to do two things with each value in the “list” and DeleteRows function to delete rows):

[Table 1].FormulaMap(RunActions(AddRow([Table 2],[Table 2].Name, ToText(CurrentValue)), DeleteRows(CurrentValue)))

P.S. In my example I use ToText(CurrentValue) to copy just the text value of the rows from Table 1, not references to them, because otherwise when you delete rows from Table 1 you will end up with references to deleted rows in Table 2.

Hope this will give you an idea how to use FormulaMap function and how to define your “list”.

7 Likes