Modifying another table when using formulamap

1

I have a scenario where we have a partner who can sell multiple products and each product has different rules. I created a formulamap button that works well that says take the column with the list of products and create a new row in another table. In the other table each row is a list of products with partners name in another column.

So if, for example, we have two partners and they each sell the same 3 products, the second table will have 6 rows (3 products with one partner and 3 with another partner) So far no problem.

The issue is that sometimes we remove a product and I can use the same button since it will just add duplicate rows. So I am trying to use modify. I have tries multiple ways but there seems to be a problem with how I am using “currentvalue” so it will modify every row overwriting them with the 3rd product. I tried nth but couldn’t get that to work either.

Grateful if anyone can take a look and see what is happening or perhaps I need to take a more complex approach. T

Thanks in advance. I have added it here and since it is an example I will keep it live for the community.

Thanks

1 Like
2

Hi Chris,

I would recommend you to build a Products table and use always relation columns instead of text values when you want to filter/modify rows etc. You can hide those columns and display the text values if that pleases you more aesthetically, but it will make your logic much more robust.

For your use-case, I think modify doesn’t make sense, you need to either add them if they are not in the table and remove them if they are but the product is not there any more.

I just created an extra button just using your text values, but I would really encourage you to adapt your doct to use relations.

RunActions(
  thisRow.Products.FormulaMap(
    CurrentValue.let(currentProduct,
      if(
        [Product Table].Filter(
          currentProduct=ProductsToo AND thisRow.[Partner Name]=[Partner NameToo]
        ).IsBlank(),
        [Product Table].AddRow(
          [Product Table].ProductsToo,currentProduct,
          [Product Table].[Partner NameToo],thisRow.[Partner Name]
        ),
        NoAction()
      )
    )
  ),
  [Product Table].Filter(thisRow.Products.Contains(CurrentValue.ProductsToo).not()).DeleteRows()
)

Hope this helps,

Pablo

1 Like
3

Thanks Pablo, I will take a look and give it a go. Appreciate the help.

You raised an important issue I was dealing with as far as the “relation table”. In my real document it is a relation table but I noticed something strange that when I use a relation to another table where that table is a synced shared table from another spreadsheet it doesn’t seem to inherit the values in the same way so I found myself, in a another use case going from the synced table and then recreating another local table that converts the synced table back to selectable values and referencing that. Have you experienced this? If worthy of some more discussions I can open up a new topic.

1 Like
4

Wow, this is great. I am going to study this one since it is beyond my current skils but seems very helpful and will be really appreciated by the group!:grinning_face:

2 Likes
5

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.