Currently, ModifyRows does not set CurrentValue to each row as it goes by. This is unfortunate, because if I want to make, say, a button that adds 1 to every value in a column, I want to put something in this box:
Say:
But clicking this button does not do anything like what is expected. Score finds the entire column, so Score+1 is, apparently, [...]1, where ... are the values of the column. Trying to do it with CurrentValue explicitly doesn’t work:
I don’t even know what CurrentValue is here, but it’s apparently not the row.
ModifyRows should set CurrentValue to each row that it is modifying.
I don’t know if you are just reporting the problem or still trying to find a solution. In case you want to achieve something like, you can use formula map like this
The idea is to make the approach more granular and iterate over each row using FormulaMap(). Then you can get the Score value for each row and increment it accordingly
I made this thread specifically to suggest this improvement to ModifyRows. I did try your workaround, which I found on another thread here, but it is buggy and doesn’t work. See "Exception occured" trying to update row
I have another workaround, though, so all is not lost.
I’ve been using this workaround a lot on other tables, using FormulaMap and then ModifyRows(CurrentValue, ...), and I’ve discovered something else about it: each ModifyRows creates a separate Undo queue, so undoing transformations on a large dataset takes a while and it’s difficult to find your way back to the original change. This is definitely less than ideal!
(It might be nice, separately, to be able to group actions together to run atomically for the purposes of Undo, but that’s a different request!)
I’m sorry I didn’t see this one earlier with your mention CurrentValue. From your first post, if you want to single out an individual row’s value for a column, you can use thisRow.
So in your case in the first post, thisRow.Score + 1 would give you the score for that row plus 1 instead of the total score for that row.
Hey @BenLee, sorry but there’s no thisRow in the context of a canvas button (I just had to check it myself because that’s what I believed but your post made me doubt that).
@Jaisil_Muttakulath_Joy is correct, the proper way to perform the change individually on each row is to use Table.FormulaMap(CurrentValue.ModifyRows(...)). The mass-ModifyRows on multiple rows at once is only possible if you want to set the same value (evaluated once) on all rows, not individually calculated value per row. That’s why there’s no CurrentValue in that context.
And yeah. spamming the undo queue is a known issue. One of the reasons I don’t depend on undo at all, and just prefer to hide that alert altogether. If needed, I’d rather make my own button to “undo” the change.
I ran across another case where I actually did use CurrentValue inside of ModifyRows to refer to something outside of the row being modified, so just making this change straight-up would break that code and not give it a good way to fix it.
I guess, then, the only way to address this in a fully general way would be to add proper first-class functions, in which case ModifyRows could accept a function in place of a new row, in order to generate a new row to update with.
The CurrentValue context issue is a known and perhaps the most discussed one:
Usually there are ways to work around this. E.g. if you need to iterate over X rows and perform some action in each of them, and you both need the CurrentValue as in the I-th row, and CurrentValue as within that operation (e.g. a filter within), you’d most likely just want to add a button column. The outer CurrentValue would then be replaced with thisRow, and you can make a button that would click all the buttons in the column.
For trickier situations the bulletproof solution is this (the tutorial desperately needs updating though):
Has there been any progress on solving this (having access to thisRow or currentValue in ModifyRows)? I’m running up against this when trying to create an automation that applies a formula based on another column’s value.
While I agree that I wish that ModifyRows supported currentValue, I found a solution for my use-case (using ModifyRows in an automation where the final value is dependent on another column in the same row):
I created a new button column and added the transformation as the button action.
Then I updated the automation to:
filter for all of the rows that I want modified,
then apply a FormulaMap to that set of rows that pushes the button
This allows me to filter which buttons I want pressed without encountering an error (if you run an automation on a column with disabled buttons, for some reason it throws an error – you’d think it’d be smart enough to ignore disabled buttons).
I think the solution eventually arrived thanks to the WithName() formula recently launched:
So, finally no more workaround to access directly different level of nested cycles.
However, let me know should you think your use-case cannot be solved with WithName().
Hi @Federico_Stefanato, please correct me if I’m wrong, but I don’t think it does. ModifyRows doesn’t expose thisRow or currentValue so withName doesn’t help.
