Suggest I have two tables called ‘Grades’ and ‘Students’, and in Grades a column named ‘ID’ references the ID number column from table ‘Students’. However as a teacher I have different students every year, so when I delete records in ‘Students’ that were referenced elsewhere, the referenced data would also be deleted.
Is it possible to do a ‘lazy reference’, where the reference column is only updated when certain circumstances are met, for example when I hit the return key. Or make the ‘reference’ column actually a copy column, which only references the data once and for all (i.e. copies the data). This way, I can delete or update the table without messing columns in other tables.
Hi there,
I would not recommend deleting the data in the referenced table unless you are deleting the referenced records as well.
Instead, I would recommend adding a column to archive the old data - then it’s still there if you need it in the future and you won’t lose that link between your data.
I created an example for you here: Community #2932 - Grist
I have a table of Students and a table of Grades. The Grades table has a reference column that points to the Students table.
I have a column Archive? in the Students table where I can toggle to archive a particular student (or list of students) when the year/semester is over.
The Archived? column in the Grades table is a formula column that returns the value from the Archive? column for the student listed in the Name column.
With these two Archive columns, you can filter out archived students and grades.
This way, the historical data is still available and you don’t lose the link between the Student and Grades.
Because the Archived? column in Grades is a formula column, this will continue to work in the background so we can hide this column from view.
Please let me know if you have any follow up questions.
Thanks,
Natalie
Hey! Thank you for the detailed reply!
This ‘teacher-student’ thing is only an over-simplified abstraction of my current project. Would you kindly refer to this post to get a quick idea of what I’m building?
Now I’m getting somewhere. I combined NetBox’s Webhook and n8n, as @ dmitry suggested. Now the data I need for cross-referencing will be populated automatically whenever I made a change on NetBox side, or I can just trigger the n8n automation manually.
The hitch 2.0 is, as you can see in the workflow chart in my previous post (image). The way I ‘update’ my table, is by first deleting all the records, then re-populate the whole table, even if I changed merely one record on NetBox.
I know n8n has a built-in ‘Grist Update’ node, but this node mandates a Row ID. The problem is, because the body of the webhook sent by NetBox only contains the asset’s internal ID (plus other info ofc), there is no way to ascertain the ID of the row that I want to update.
I then thought of adding an extra ‘ID’ column to connect NetBox and Grist, but before I even did anything, I realised that I need a way (like a formula or a function) to return the ID of the row that contains the asset’s unique identifier provided by NetBox (the aforesaid ‘ID’ column).
So now the problem becomes: How to return the row ID based on the NetBox asset ID (which has its own column already) to n8n, so I can ‘update’ the records in Grist.
I know I have worded this post as bad as I could because it’s already midnight in my city. I’ll try revising this comment tomorrow.
It seems like you could do a GET request from n8n to load the contents from the table and filter the results in n8n to get the row ID that matches the asset ID you’re trying to update. Have you tried that?
Oh, great! I’m glad you got it working!
Anybody got an idea?
