I tried using the formular that was sent to me yesterday
But it was saying error
How do I resolve it.
Also, how do write a formular such that when a record is deleted in contacts table, every associated record in interactions table
Thanks
The formula you need for the ‘Due’ column is:
items = Interactions.lookupRecords(Contact=$id, Type="To-Do")
return min(items.Date) if items else None
In the first line of the formula, we use a lookupRecords function to find all records in the Interactions table where this Contact is listed and where the Type is “To-Do”. Because the Contacts column is a reference column, it stores a record’s unique ID. The Name is just used as a value label. This is why we use ‘Contact=$id’. We assign the list of records found to the variable items.
In the last line of the formula, min(items.Date) finds the lowest date of the list of records in items (found above).
The formula you need for To-Do Items is:
items = Interactions.lookupRecords(Contacts=$id, Type="To-Do")
return "\n".join(items.Notes)
The first line is the same as our prior formula - we use lookupRecords to find all records in the Interactions table where this Contact is listed and where the Type is “To-Do” then assign this list of records to the variable items.
In the last line of the formula, we pull the value from the Notes column for all records in the items list and join them together.
The article linked below is especially helpful in understanding how lookup functions work
It is not possible to delete or create records using formulas. if a contact is deleted, the interaction would still exist but the link to the deleted contact would be gone.
Often it is best to ‘Archive’ these contacts and associated interactions.
You can see an example of this here: Community #2687 - Grist
I added an ‘Archive’ column to the Contacts table and applied a filter so when a Contact is archived, it is filtered out of the view.
The Interactions table has an Archive column as well but this one is a formula column that pulls the value from the Archive column of the Contacts.
