Relink Records Multiple Sync bases

[cvoith]

Hey guys,

I am trying to find a way to relink Personnel records on a base with synced tabled from 2 different bases in order to limit interface views off Airtable accounts

Base Personnel - All Personnel info including airtable account using the User Field type
Base Project Tracker - Overall project information including Personnel like producers linked to record on a synced table from the Personnel base. Airtable accounts are a lookup field.
Base Asset Tracker - Synced to Project Tracker to assign assets, pulls over information like producers. Also synced to Personnel base to assign assets to people.

The synced Project table on the asset tracker currently pulls over the producer information but obviously as a single test field with the producers in a string out. Obviously I can't change the field type to relink to the Personnel synced table on the asset tracker. 

I've tired an automation that finds records with the matching name but that does not seem to to work since there can be multiple producers attached to a single project.
I've also tired a scripting automation to pull the string apart and relink, but I can't seem to get it to work.

let projectsTable = base.getTable('Projects');
let personnelTable = base.getTable('Personnel');

// Fetch all records from the "Projects" table.
let projectsRecords = await projectsTable.selectRecordsAsync();

// Create a mapping of producer names to their corresponding project records.
let producerToProjectMapping = {};

// Iterate through each project record.
for (let projectRecord of projectsRecords.records) {
    // Get the string of names from the "Additional Producers" field.
    let additionalProducersString = projectRecord.getCellValueAsString('Additional Producers');

    if (additionalProducersString) {
        // Split the string into an array of producer names.
        let producerNames = additionalProducersString.split(', ');

        // Iterate through each producer name.
        for (let producerName of producerNames) {
            // Store the project record for this producer name.
            if (!producerToProjectMapping[producerName]) {
                producerToProjectMapping[producerName] = [];
            }
            producerToProjectMapping[producerName].push(projectRecord);
        }
    }
}

// Iterate through the mapping and update personnel records.
for (let producerName in producerToProjectMapping) {
    // Fetch the corresponding personnel record for this producer name.
    let matchingPersonnelRecords = await personnelTable.selectRecordsAsync({
        filterByFormula: `AND({Full Name} = "${producerName}", {Additional Producers Project} = BLANK())`
    });

    // Check if there is exactly one matching personnel record.
    if (matchingPersonnelRecords.records.length === 1) {
        let personnelRecord = matchingPersonnelRecords.records[0];

        // Extract the projects associated with this producer name.
        let projectsToLink = producerToProjectMapping[producerName];

        // Extract the project IDs.
        let projectIdsToLink = projectsToLink.map(project => ({ id: project.id }));

        // Update the "Additional Producers Project" field to link to the projects.
        await personnelTable.updateRecordAsync(personnelRecord, {
            'Additional Producers Project': projectIdsToLink
        });
    }
}

Does anyone know of a way to relink or if there's a better way to structure this?

[Ben_Young1 · Accepted solution]

Hey @cvoith! 

A year and a half ago, I noticed an incredibly interesting behavior in how Airtable handles base synced tables. I haven't found too many reasons to leverage it. Despite it answering your question, there are a few reasons why I'm incredibly hesitant to broadcast this behavior in a public forum like this. 

I've created two bases to use as an example.

The first base is called Data Origin. It contains two tables: Records and Samples.
The two objects share a relationship.

In my second base, Data Target, I've created two synced tables of the origin's Records and Samples tables.
In the sync configuration for my Samples table, I've excluded the Records field, since we won't need it in the final product.

This is what things look like at this stage:

 

 

Now, we'll open the field's configuration menu and go through the same steps that you would if you were changing the field type of any field in a non-synced table.

Select the option to change the field type to a relationship. From there, we'll select our synced Samples table.

 

When you do this, you'll find that the relationships have successfully been established with records in the new table.

There are some interesting things to note about its behavior. Firstly, as you'd expect, you cannot edit the Samples field values from the Record object, however, you can link records to samples from the Sample object.
You cannot create new Sample records, as they'd have to be created from the Data Origin base.

Now, if you make an edit to the data that is not in parity with the data being synced to the Records table, the data that is synced from the Records table will take precedence during the next sync refresh interval or if you manually trigger a data refresh. I would recommend that you change the permission settings of that single field that can be edited to restrict anybody from editing the field data.

At this point, you're all set. The links between records will automatically be reflected when the sync is refreshed. You can configure as many fields from both synced tables and leverage rollups, lookups, and formulas to surface all the relevant data you need.

I mentioned before that I'm hesitant to point out this solution. The reason is that it has the potential to encourage poor data design and schema management. I'm highly skeptical of users that create multiple, fractured datasets in various bases. These users tend to over rely on synced tables and end up with an unmanageable number of bases, which directly leads to a decline in user engagement and data quality.

From the limited context I have, I would agree that your three separate bases are justified, so don't take this observation as a critique of your schema. It's mostly a callout for anybody in the future that stumbles upon this post and ends up further stuck in a poorly designed solution.

[cvoith]

Oh man that worked! Thank you so much! I thought they'd stop syncing, but they still are. Thanks man!