FormulaMap() : When and where?

[Pch]

Hi there !

As I’ve said in this post, I’m currently struggling with FormulaMap()
Thanks to the explanations of Paul, I was able to understand what the formula does in this case but I still have troubles to apply it correctly in other cases.

I’ve used it, on my own, a number of times, principally to “merge” different fields into another one but I admit that when it comes to use it for other reasons, I never know if, why, when and how… I can instinctively “feel” I should use this specific formula but I don’t seem to get the general logic behind it (I don’t understand how to link this formula to the problem I have in front of me) , leaving me with the sensation of being stuck somewhere I shouldn’t be …

Another example was this post :

From $1,000,000 to $1M

Hi there, I have a table of numbers going from thousands to billions and want to make more user friendly by abbreviating them. e.g. 1K, 1.5M, 2.5B etc Any ideas how to do this? Thanks!

I was trying to answer the question (as helping others is still, for me, the best way to learn ) in my own way and was slowly getting there but it was really not as elegant than the actual solution.

Does anyone have any tips, examples, or anything else for me to better understand this specific formula (where to use it, why or how…) ?

Sorry for the very vague question here .
Any help will, of course, be deeply appreciated !

[Mario]

Hi @Pch

I’m not the best in answering this question probably but i’ll give you my idea

With formulamap you have to define a list and a formula (as long and complex as you need)

Basically for every item on that list the formula use it as the input and gives you the output

I’ve changed all my formula to use different calculi to obtain the same end results using reference tables and Switch() formulas so now i rarely use it, but it is always present in the trials when looking for a working formula

I think it is useful when you have to deal with some operation made for every item in a list, and then some other operation on it, but as usual let’s wait for the community inputs

[Pch]

That’s not a bad input @Mario , not at all

@Mario

With formulamap you have to define a list and a formula (as long and complex as you need)

I think that part of my problem with FomulaMap() lies there : I actually can’t define the list.
I think that for some reasons, I just enable to see the list where a formula should be applied for each item on the list. So, therefore, I can’t see where and when to use FormulaMap().

Actually, and I’m kind of thinking out loud here, this is a great first enlightenment .

@Mario

I think it is useful when you have to deal with some operation made for every item in a list, and then some other operation on it, but as usual let’s wait for the community inputs

This is a simple, nonthreatening and clarifying way to put it and I thank you for it (and for your input in general too . It is useful ).

And I agree, maybe others will have other inputs .

[Andrei_Kharlanov]

@Pch

I think that part of my problem with FomulaMap() lies there : I actually can’t define the list.
I think that for some reasons, I just enable to see the list where a formula should be applied for each item on the list. So, therefore, I can’t see where and when to use FormulaMap() .

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”.

[Christiaan_Huizer]

@Andrei_Kharlanov

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.

Rather instructive in it’s simplicity and well appreciated. I’am about to apply this logic and would not have thought of adding the ToText().

Best, Christiaan

[Ander]

@Pch

Good discussion. Tossing this in for anyone else having this question.

List(2,3,4).FormulaMap(
  CurrentValue * 2
)

4,6,8 This is the new list comprised of each value from the original list transformed by the formula invoked in FormulaMap().

FormulaMap() is a forEach() loop that returns values. For each value in a source list, FormulaMap() maps a formula to each one of those values, meaning that it runs that formula on each value in the list. The results are returned in a new list containing the transformed values.

Defining the list is the key, and often the hardest part.

Filter() is designed to define lists that meet specific criteria, so I tend to use it frequently for this purpose.

One of the most powerful uses of FormulaMap() is to run it on lists of row objects. After you define and build your list of rows, then you can run a formula on each one of those rows, incorporating data from any of their columns. See the simple example below.

---

Some Additional Details

I consider it a best practice from a manageability standpoint to actually build the list of rows some place, and then reference that list using FormulaMap(), rather than trying to both build the list and run FormulaMap() in the same formula.

Unfortunately, this conflicts with the way Coda uses memory for row references. This shouldn’t appreciably affect most use cases. It only comes into play when you generate lots of row references via lists, which some of my key workflows require. I would be most interested to understand the rationale for row refs consuming so much memory (I’m guessing it facilitates the hover access). This row ref memory issue is damaging to the core functionality of filtering relational tables – I’d like to see it optimized at some point.

[Paul_Danyliuk]

@Ander pretty much explained it.

When: any time you need to iterate over a collection — i.e. do something with each element of the collection individually, such as transform it by running through some functions, or running an action on each item individually.

Where: in any calculation formulas as well as in actions.

A collection: can be a list of rows coming from a Filter(), as well as from a Lookup cell, as well as a hard-coded List(@ref1, @ref2...), or a list of values (strings, numbers etc).

A formula within the FormulaMap() is executed for each element of the incoming collection in order. An element that the formula currently calculates is stored in a CurrentValue variable. Inner FormulaMap(), Filter(), ModifyRows(), and some other functions that do their own iteration and define their own CurrentValue will override that outer CurrentValue and you’ll use access to that — there are some tricks how to address that in different contexts.

[Pch]

Oh my !!! This is absolutely wonderful !!!

I can’t thank you enough , all of you, for the time you took to answer, create concrete examples and, last but absolutely not least, your inputs and explanations , so I’m just going to say :

Thank you very very very much !!!

This is all more than helpful and clarifying to me, as I’m finally able to understand why I was so stuck on this formula and how it works in general (and not case by case) .

With all this, I’m pretty sure I can finally get to Phase 2 : Practice and Exercise ! (more than Concatenate()/BulletList() different fields into anther one )

[Piet_Strydom]

Wonderful example!

Still providing benefit.