Here’s the problem. I imported a user’s existing data so it already has ID numbers so using ROWID() won’t work in this case.
I need to have a button that adds multiple rows using ForEach and a Sequence. I have a button that does exactly what I want with one issue I can’t figure out.
No matter what technique I use to get the next number in the table, the ForEach is not reevaluating the equation on each iteration.
So. If I add 10 rows all 10 rows wind up with the same number.
Even if I use a helper table that stores the max() of the ID column so it can add 1 each time.
I can’t generate 10 new numbers and store them in a table and use those because other users might add items to the table once I create that temp table and then again we get duplicates.
The table has an ID column and a formula for new rows that is ID.Max() + 1. This works when adding rows to the table. But it seems when using a button and adding rows with a ForEach, Coda seems to completely ignore the new row equation in the table. It also seems to only calculate the formula one time on the first iteration of the ForEach and then uses that same number for every added record.
I’ve asked Coda support and haven’t gotten a useful answer back. So I thought I’d see if anyone else has found a workaround. I can’t be the only person who needs a unique sequential number but cannot use rowID because of old data transferred over that doesn’t line up with ROWID numbers.
Help!
I also looked at adding a button on the table to do the AddRow step and then just call that button from the ForEach. Unfortunately, I need some data coming from the step that would call that button and I don’t know how to pass data into that other button.
Right now the only solution I can fathom is a column on the table to store the information for the iteration, then modify the current row, save that information, then push the second button to add the row, then back to the foreach button that would modify that row again and push the button again, rinse and repeat. But that seemed like too much work. There’s got to be a better way to do this in one button.
Finding it a little hard understanding your core problem here, but if you want things to recalculate within a given formula then try changing your ForEach from ForEach( ... ) to ForEach(RunActions( ... )), but I’m sure there’s a better solution if we get to your core issue.
I found a post with @Paul_Danyliuk talking about using the RowID() and showing that it wasn’t always unique. But I couldn’t find a post about adding rows and them not processing the formula in each iteration of a ForEach loop. I tried searching everything I could. If someone has a post, please point me there.
GENIUS! And now I feel stupid. I use RunActions all the time to wrap things but usually only do it when I have multiple things or some huge nested formula. I never thought to add it to a simple one step Addrow. I think that might have solved it. It worked in my test doc. I’ll see if it works in the complicated version of my other doc.
Effectively, I create a sequence of IDs - starting at ID.Max() + 1, and ending at ID.Max() + 10 (thus adding 10 rows).
I then use the current value as the RowID
I considered something like that, but since other users could add new rows while the button is running, I think it could still wind up with duplicate numbers.
A way to reduce that possibility is to create the rows in reverse order.
Let’s say we already have 10 rows (with IDs 1 to 10) and we would like to create the next 10 (IDs 11-20)…
Create ID 20 first, then 19, then 18 etc. If another user is attempting to create the next set of rows, they will start counting from 21…
If we create the IDs incrementally, there is a chance that a second user will try to create new rows whilst the first user is still in the process of creating additionational rows, but if we create the additional rows in reverse order, any second user start their next ID at the right place.
@Agile_Dynamics That’s the issue. I had a formula in the New Row option and if you use a button to addrow that adds multiple rows in a foreach loop it didn’t respect that new row formula. It generated all the new rows with the same number. The solution is to wrap the AddRow in a RunActions and that solved it. Simple but I missed it. I typically only RunActions if I do multiple things so I didn’t have that in my ForEach loop before the AddRow. I would expect no matter how a new row is added that the new row value calculation always be done, but apparently that is not the case and Coda doesn’t consider that to be a bug apparently. Not sure why.