Hello Community!
I’m having a hard time figuring out a formula to add multiple rows. I know this would use formulamap() and sequence() but I just can’t get my head around it. I made an example of what I’m trying to accomplish. It’s basically a formula that looks at how many variants there is for a product and then adds multiple rows if there’s multiple variants (1 row per variant)
Thanks a lot!
I’m not getting it completely. Do you want the target table to have one Tshirt, then two more Tshirts, then 3+4+5=total 12 Hoodies, and 8 Vests? Why there are multiple rows with the same Product name then?
I’m just curious if the complexity of the task can be reduced. This is in fact not so trivial, since nested CurrentValues are not supported.
Hi Paul, thanks for taking the time to look at this.
I’m looking to send data from the Push table to the Result table. My example looks simple, but the tables wouldn’t look that similar in reality. This would be useful in our reporting if each item in each order was displayed as a separate row (1 order with 4 items: 4 rows). Then, i’d like to extract data from the item (in this case variant) like I would if it was a product (Shopify Pack). Hope this makes sense 
Sorry, still confused. Does the Result table need to look like the Database table in your example when all buttons are pushed? Is Variant just a piece of data that needs to be copied? Or is it the number of times the row in question needs to be copied?
No the Result table can be plain text and variant is just a piece of data for this example. The result table is all text, no lookups. I just want to split the product for each order. I edited the example to make things clearer. The shopify backend table is just used to created the products. This wouldn’t be in our doc. We would only have the Shopify pack table and the results table.
Okay. Well, I think the only way to implement that now is with a loop with an extra step to extract the Nth product in the list into a separate column. Here’s the formula for the button that would work:
thisRow.Products.FormulaMap(
RunActions(
thisRow.ModifyRows(thisRow.[Loop - current product], CurrentValue),
Result.AddRow(
[Order #], thisRow.[Order #],
Product, thisRow.[Loop - current product].Product,
Price, thisRow.[Loop - current product].Price
)
)
)
Demo:
UPD: You can actually achieve the same result much easier if you can add a lookup column to the database in Results — copying CurrentValue in AddRow() or ModifyRows() works, even though dereferencing doesn’t.
UPD2: Just tried this and it worked. You can actually use a hidden _DEREF() function to get the values from CurrentValue where the latter is a reference to the product row:
thisRow.Products.FormulaMap(
Result.AddRow(
[Order #], thisRow.[Order #],
Product, _DEREF(CurrentValue, [Shopify Backend].Product),
Price, _DEREF(CurrentValue, [Shopify Backend].Price)
)
)
@Krunal_Sheth just want to get your attention here. Interesting that the CurrentValue injected still refers to the item in the FormulaMap iteration, but formula editor treats it like referring to Result table. It’s either a bug with CurrentValue variable, or with formula editor misinterpreting it.
That’s awesome! This works like a charm. I thought the _deref function was sunsetted? If you have time (and i’m sure other users would like to know this) what does _deref do exactly?
I haven’t heard of it being sunsetted — it’s just an experimental hidden function I found a while ago. It may go away at some point though.
_DEREF(input, column) means take the input and extract the column from it as specified by Table.Column notation. If not found, return blank. Think of it as forced query you have to resort to when Coda is acting up.
_DEREF(@SomeRow, Users.Name) is the same as simply writing @SomeRow.Name in a formula, where @SomeRow is a row from the table Users. However, the function comes in handy when the first parameter does not resolve to a row from a correct table for some reason. This is exactly what happens above with CurrentValue when the formula editor mistakenly reports CurrentValue as a row from Result and not Shopify Backend table, and suggests you the wrong set of columns for autocomplete.
See here how formula editor says CurrentValue is a row from the table Result, while in fact it’s a row from Shopify Backend, coming from FormulaMap.
_DEREF(CurrentValue, [Shopify Backend].Price) still reports a warning, but it works.
Also, if a path (as defined by the 2nd param) isn’t found in the input object (1st param), the result won’t be a broken formula but simply blank.
Thanks for the explanations!
Well, the function is still there 
Hi @Jay_Lefebvre,
We’re working on making this easier to pull in from the Shopify pack. Please check back at the end of this week or early next.
The _DEREF is a hidden formula and could get removed at any time. It’ll work while it remains in the codebase, but it’s not officially supported.
Hoping to have a better solution for you soon anyway!
Ben
Thank you for reporting.
So what is the bug here?
CurrentValue's context wrong?CurrentValue doesn’t point to a row that’s being added, while it should? (although what would be the point of that? the row is empty at this point anyways)Loks like _DEREF is died 
- return blank value
Nope, still works:
Perhaps you’re trying to deref something that doesn’t exist?
The original issue should’ve been already solved though. The formula editor became better at inferring data types. I don’t remember using _Deref() for a while now, except in some canvas formulas to prevent broken formulas.
Hi Paul,
You’re right I check it in the simple formula too.
Could you be so kind to see where is my mistake then?
Your mistake is in the attempt to read a Table 1 field from a row coming from Table 2. CurrentValue within your deref is set by the inner FormulaMap iterating over Table 2. Even the color coding tells you this:
You lose outer CurrentValue when you nest iterating contexts. This is a known issue.
Here’s a megathread on that, featuring some workarounds (snoop for my answers):
And here’s my tutorial for one bulletproof pattern how to approach similar scenarios, when you need to iterate with nested loops in a button:
