Hi,
I have a table where the values of some of the cells are pretty likely to be the same as the previous row. To save time when entering data I’d like a new record trigger formula that defaults the value to the value of the current last row. I’ve tried something like =$ColumnName[-1] but I just get circular reference errors. Is there a way to do this?
Thanks,
Mark
Hi Mark!
Great question. To pull the value from the prior row, you can use the row ID. I created an example here:
https://public.getgrist.com/szWB1jxFAsu9/Community-1023
I set up the ‘Family’ column to be a trigger formula column that applies on new records and will pull the value from the Family column of the prior Row ID.
Table1.lookupOne(id=($id-1)).Family
Keep in mind that while Row IDs are assigned in order, they aren’t necessarily shown in order in your table. If you add a row in the middle of existing data, it will pull from the last Row ID (not row). In the screenshot below, I added a new row at what is now Row 3 (Row ID 10). You’ll see that Family was populated using the trigger formula and pulled from Row ID 9 (Fish). Keep this in mind if you are adding rows between existing rows. Because it’s a trigger formula, you can change the value in the Family column at any time after the record is created.
Hope this helps!
Thank you that is helpful.
It’s an interesting point about whether you really want the last item. As it happens the table in question is normally viewed via a SELECT BY so the last item may not be relevant but filtering down the lookupOne just gives blank if the previous row wasn’t part of the same SELECT BY and that’s fine. I only want a best effort to potentially save a bit of typing when in full-flow data entry mode.
Thanks,
Mark
