AddOrModifyRows() and Current Value problem

[Saul_Garcia]

Hello Community!
I was helping a fellow member with a AddOrModifyRows() and stumble upon a wierd behavior, not sure it is a bug or I don’t quite understad @CurrentValue.

As some of you may know what this formula does is make a filter form a table, if there are no matches then it creates a new row, but if it does find a match it modifies it.

What I wanted to do was to create a counter, every time I press the AddOrModifyRows() button it would find the row and add 1 to the counter. If you want see what I mean here’s a link to the post I replied to.

When you fill the AddOrModifyRows() one of the first things you add is the filter to find the row you want to either add or, well, modify so when you set the values and want to do what I did, the obvious thing to do (for me at least) would be to get the currentValue.Days in this case and add 1

currentValue.Days + 1

Even the “formula helper” (not sure is the correct name) gets the correct value

but when I press the button I get this:

To get this to work you have to write the same filter you already wrote at the beginning of the AddOrModifyRows() formula to get the current value of the Days to add 1 to.

Another thing I found out is that when you simply put Days without the CurrenValue at the beginning what you get is a list of all the days in the table you are modifiying instead of the filter you are appliying to that table

I leave the file so you can play with it:

[BenLee]

Thank you for the write up here!

This does seem more difficult than it should be. I’ve been testing out your doc and a formula I got to work was more complicated than I expected…

If(
[Days in Each Status].Filter([Project Name]=thisRow).Count() = 0,
AddRow([Days in Each Status],
  [Days in Each Status].[Project Name],thisRow,
  [Days in Each Status].Days,1
),
[Days in Each Status].Filter([Project Name]=thisRow).
FormulaMap(
  CurrentValue.ModifyRows(
    Days,Days+1
  )
)
)

This does seem to be operating as the formulas suggest though. Both ModifyRows() and AddModifyRows() are looking to “bulk edit” a filtered set of rows and not necessarily go through them one by one and edit on a per-row basis.

I think this is the reason FormulaMap() is needed. I’ll pass along this challenge to the team though and see if other solutions or insights popup.

[alden]

Hmm, yeah this is tough.

The issue here is that you’re getting column references when you need row references:

Here Days in each status.Project Name is the names of all the project names, not just the ones you’ve filtered to.

A first class fix to this involves adding better language-level support for nested scopes.

In this case though, the easiest workaround I see is to use multiple buttons. Here’s a working example:

Note how the the Add Day button on the second table is bound to the row scope.

[Paul_Danyliuk]

I’m not sure if this qualifies for the best practice or not, but I never use AddOrModifyRows(). Instead I’m doing this explicitly with an IF:

If(
  thisTable.Filter(...).Count() = 0,
  thisTable.AddRow(...),
  thisTable.Filter(...).ModifyRows(...)
)

This would also give you more flexibility should the match conditions change — e.g. you don’t just want to match whether the row exists by this and that column, but e.g. when you’re already looking up that thisTable.Filter(...) part into this row (perhaps with a complex condition that you don’t want to copy over) and your formula would simplify to just

If(
  LinkedItem.IsBlank(),
  SomeTable.AddRow(...),
  LinkedItem.ModifyRows(...)
)

which is a pretty common scenario in my experience.

---

P.S. Actually it is going to be one of the best practices. Linking data via lookups is generally a good practice, so it only makes sense to reuse the linked (looked up) item or collection of items within the If(IsNotBlank(), ModifyRows(), AddRow()) idiom.

[Saul_Garcia]

@alden

A first class fix to this involves adding better language-level support for nested scopes.

I hope this happens!

@BenLee thanks for the formula! in this case I only generate one line per project in Days in Each Status Table so the formulamap isn’t needed

@Paul_Danyliuk that’s interesting! I may start doing this instead! Thanks!

[Pawel_Niewiadomski1]

If you stumble on this thread and wonder how to make AddOrModifyRows work, look no further there’s a solution to this nightmare called WithName, take a look at Launched: Reuse logic and create nested loops with the new WithName() formula - #44 by Tamerlan_Kagarmanov to learn more about it.