Using FormulaMap with AddOrModifyRows

[Doug_Brown1]

Hi All!

I’m working on a project where I have a couple of tables: one for products and another for call transcripts. Each transcript may mention multiple products, and I want to create a table that captures every unique combination of product feedback associated with each call.

Currently, I have a button that triggers a formula using FormulaMap and AddOrModifyRows to split these mentions into individual rows in the feedback table. Ideally, this button should populate the table with each unique combination of product and call, ensuring that no duplicate rows are created.

However, the issue I’m encountering is that instead of updating an existing row when a product-call combination already exists, the formula keeps creating new rows every time the button is clicked. My goal is to have it check for an existing match and, if found, update the row rather than create a new one.

Here’s a brief breakdown:

• The formula uses FormulaMap to iterate through the mentioned products.
• It should look for an existing match in the feedback table where both the product and call reference already exist.
• If a match is found, I want to override the content of that row to add additional details.
• If no match is found, then it should create a new row.

Despite this setup, the formula seems to always create a new row, resulting in duplicates whenever the button is pressed more than once.

I would really appreciate any advice or alternative approaches to ensure each combination of product and call is unique.

Thanks in advance for your help!

Example Doc:

Loom example

[Niko_Nyman]

Hi Doug, unfortunately I don’t have enough time to look more deeply into this, but I would suggest you check whether the problem is in the AddOrModifyRows() expression parameter.

This is just a hunch… but if you look at the small icons next to the table references here:

…you can see there are three different varieties.

• Referenced Product outputs “a row from table Products”
• thisRow.Products outputs “a list of rows from table Products”
• Associated Call outputs “a list of rows from table Call Transcripts Table”
• thisRow.Call Name outputs a text value

So you might be comparing a row reference to a list of row refs, or a row reference to a text value of the rows key column name.

[Niko_Nyman]

Hi Doug, I took the librety of editing your example doc as you left it open. (You can always revert it back from the history if you want.)

I changed the Button formula so it now works the way I think you intended. The changes are explained in the comments below:

thisRow.Products
  .FormulaMap(
    // Name the FormulaMap's CurrentValue for clarity
    CurrentValue.WithName(CurrentProduct,
      AddOrModifyRows(
        [Feedback Breakout],
        // Compare Feedback Breakout table's Referenced Product
        // to the Product in the current FormulaMap iteration
        [Referenced Product] = CurrentProduct AND
        // Compare Associated Call to the row the button was 
        // clicked on, not just the Call Name of this row
          [Associated Call] = thisRow,
        [Associated Call],
        thisRow,
        [Referenced Product],
        CurrentProduct
      )
    )
  )

Let me know if this works for you!

[Doug_Brown1]

So incredibly helpful! I think this does exactly what I need it to.

Much appreciated!