Linking multiple tables by different columns... hitting a wall!

[Martin_Obreteno]

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?

[Kamille_Parks · Accepted solution]

Since {Uid} (probably) doesn’t change over time but the {Email} might, you should probably leave {Uid} as the primary field in the [Users] table.

Try setting up an Airtable Automation (or since you’re already using Zapier, an extra zap step) to find the record in the [Users] table that has an {Email} value that matches the incoming [Orders] table record’s {Email} value. Then insert the matching User records into a link field to connect your records together.