I have a table for Users, the primary field is UID, I have email, name, created time. I have a table for Orders. It has Order IDs, email, final price, order place date. I have a table for Cancellations. It has cancellation ID, email, cancellation date. I have a table for User updates. This ha User ID, email, new email (if updated), new name (if updated). All tables are populated automatically from Zapier upon events in another system.
I would like to show the SUM of all order placed for the specific User ID under Users, however I can’t seem to link this via UIDs? Does this mean I have to change the primary column in both places to Emails?
I need a custom field under Cancellations, that looks up the last date an Order was placed and adds a month to it. However, Cancellations does not have user IDs… will I also have to use emails here?
Last but not least, User Update rows might indicate an email update. E.g. a user might now have a new email. What do you suggest I do here if I start using email as the primary field so I can do look ups? Overwrite emails across all tables for the user whose email used to be = old email? Is there a way to do this automatically based on new records added to Airtable?