When a new row is added to the ‘interactions’ table, I’d like the table to populate the previous score with the ‘final score’ from the last record for that ‘contact name’. For context, the ‘final score’ is a math column that calculates ‘previous score’ + ‘difference’.
This would continue for any new rows that are added (there will be multiple contact names so the ‘previous score’ needs to make sure it picks up the ‘final score’ from the last corresponding record for that contact name, not just the last record for any contact).
I think this may have something to do with pulling the ‘last value’ from an array for that contact, but I’ve hit a mental block, if someone can please assist?
What you need is a multiple relation linking the user to their rows in the Interactions table. Then add a single value column that returns the last score from the relation. That will be the value that you include when adding a row.
Thanks @Jeff_Hager - that makes sense. I’m just stuck with the last part, i.e. the value that is included when adding a new row.
I’ve tried to use a ‘template’ column to pull in the value from the single value column, but I’ve realised that this value will keep changing since the last value from the single value column will always be the latest final score and this will apply across all records. Is there a way to ‘fix’ a value for a record, so that it doesn’t change when new rows are added?
@ThinhDinh to answer your question, in theory the user’s previous score for a particular record shouldn’t change, however the previous score for a new row for that contact will be different since it pulls in the final score from the contact’s previous record.
Having said that, it could change if the user goes back and edits a previous record
What I meant is that when you add the new row, you take that value in the single value column, and pass it through your Form or Add Row action to a basic column that permanently stores the previous value.
