Formula Map Sequence + DEREF

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 :grimacing:

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.

1 Like

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.

3 Likes

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

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

1 Like

Thanks for the explanations!

1 Like

Well, the function is still there :man_shrugging:

1 Like

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

2 Likes

Thank you for reporting.

1 Like

So what is the bug here?

  • Formula editor that determines 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)