Create relationship from two different sources to one base

[dwdusharme]

I am creating a contact management database.

The master database contains all the fields related to a person. The unique field in each record will be the email.

This database needs to be updated from two different source. Each source has the same data but the header fields (field names) are labeled differently, and are different from the master.

Example:
Source one:  First name is labeled  "f_name"
Source two:  First name is labeled  "first_name"
Master:          First name is labeled  "First Name"

What I want to be able to do is create a relationship between each of the sources to the master using the email (which is unique in all databases) as the key field. Each source will have (needs to have) it's own field mapping to the master. Whenever any of the source databases is updated, a single table in the Master database will be updated. I am talking one master table that is updated by two different sources.

I have done this in many other database programs but can't figure out how to do it in Airtable. I have been back and forth through table sync, multiple sync, etc., but can't figure it out.

Any help is welcome.

Thanks

[TheTimeSavingCo]

Try using an automation for this.  Set it up so that it triggers whenever any of the relevant fields in Source One are updated, and make it:
1. Find the record in Master with the same email
2. Update that record with the relevant data

And you'd create the same automation but for Source Two

[dwdusharme]

Thanks. I haven't looked at automation but will do so.

Can you not build relationships in Airtable like you can in other database programs?

[Alexey_Gusev]

Hi,
Inter-base connections established via sync.
Connections between tables inside the base - with links.
Their functions are different.
It seems like functionality you described is exactly what 2-way sync doing with "Allow to edit record in source" enabled, but I'm not sure.
Are you talking about 2 different sets of the same-formatted data?
Like 40 records in Source 1  and 60 records in Source 2 and summary of 100 records in Master?
Or you mean all same 100 records in Source 1, 2 and Master? And if you edit something in Source 1,  same change happens in Master and then synced to Source 2?
The first is usual multi-source sync, second is 2-way Sync , new feature with some limitations.

to be honest, I don't understand how automation triggered in Source should find a record in Master if it's 2 different databases. 

[TheTimeSavingCo]

Yeap, you'd do that via linked fields: https://support.airtable.com/docs/linking-records-in-airtable

You want the data from "f_name" and "first_name" merged into a single field though, and so using an automation to update the fields seems like the easiest approach (to me anyway!).  If you wanted to use linked fields for this you'd:
1. Create linked fields in Master to Source One and Source Two
2. Create last modified time fields in Source and and Source Two for the "f_name" and "first_name" fields
3. Create lookup fields in Master that display "f_name" and "first_name"
4. Create lookup fields in Master that display the lookup fields from Step 2
5. Create a formula field that will see which lookup field has the latest time, and from there decide whether to use the value from "f_name" or "first_name"

And you'd need to repeat this process for every field you want to display in Master which seems quite tedious

---
@Alexey_Gusev 

re: to be honest, I don't understand how automation triggered in Source should find a record in Master if it's 2 different databases. 

Ah, I was assuming Source One and Source Two were tables that were synced into Master.  You're right, that's not mentioned in the original post, sorry!

[dwdusharme]

Alexey_Gusev.

Perhaps an example will help. Currently, with desktop relational database software, I have something like this.

Table 1 = Master list with 30,000 records. Unique field is email

Table 2 or Table 3 = A new database that I bring in. The ONLY thing I know about these databases is that they ALSO have a unique email field. And they also have most of the same fields (f name, l name, company, address, etc)

So, I create a relationship (connection) between Table 1 and Table 2 using the key field of "email" in both. I map similar fields from Table 1 to Table 2 (e.g., f name > first name, l name > last name, company > co, etc.) And I set up the relationship so that if there are matching records (same key exists in both) between Table 1 and Table 2 the following happens:

Any records that exist in Table 2 but not in Table 1 are added to Table 1
If a record exists in both tables, Table 2 will overwrite the data in Table 1.

Basic relational database stuff.

Other than creating a relationship based on unique key in both tables, and mapping the fields, I don't need to do anything else. The updating of Table 1 based on Table 2 is purely due to matching keys (emails). If I reimport new data into Table 2, Table 1 will be automatically updated. Ditto process for Table 3 and Table 1.

Make sense?