Skip to main content

Sync bases - changing the primary field

  • February 16, 2022
  • 1 reply
  • 328 views
M
Forum|alt.badge.img+5

So I’m trying sync some records from one base (base 1) into another (base 2). Base 1 contains my inventory, base 2 contains my service records FOR that inventory. I can’t have them in the same base due to record limits.

My idea is to have two tables in base 2. Table one will be a sync back to my inventory, so as I add or remove units the sync takes care of adding them/removing them for me. Table two in Base 2 will contain my service records themselves, and through a combination of formulas and lookups in Table one, I’ll see the information I’m after within Table one. Primary field in Table two is just an autonumber, so that way the second column can be a linked field back to the inventory in Table one. This is where I’m running into issues.

In base 1, I use a concatenated formula for my primary field to pull in location #, year, make, model, etc. as an easily findable and searchable value for a few other processes we have. So when I sync that into base 2, that concatenated value is there in the primary field. My service records (table two ibn base 2) are ONLY organized by serial #, so I use an autonumber for the primary field, and the for the column that contains the serial #, I link the record back to table one. But since the primary field in table 1 is the concatenated value and not JUST the serial #… nothing works.

Long set up for a short question - how can I change, edit, omit, etc. the primary field off of a sync?

    1 reply

    A
    Forum|alt.badge.img+17
    • Andy_Lin1
    • Inspiring
    • February 16, 2022

    I’m afraid this might be a long answer for a short question…

    For your purposes, I would not try to change the primary field; instead, I’d work with the synced data you do have: the serial number.

    So, we’re going to use this guide, and I’m going to name my steps the same way:

    Prerequisite setup

    Create a new field to link Base 2 Table 2 to Base 2 Table 1 (the synced table), let’s call it {Link}.

    Also, I’ll be referring to {Serial # - Service} and {Serial # - Inventory} to distinguish between the fields in the two tables.

    Choosing a trigger

    Your trigger should look something like this:

    Trigger type: When record matches conditions
    Table: Table 2
    Conditions: (click “Add condition” for each condition)

    • When {Serial # - Service} is not empty
    • and {Link} is empty

    After that’s done, test your trigger.

    Setting up our actions

    “Find records” action

    Your action should look something like this:

    Action type: Find records
    Table: Table 1
    Find records based on: Condition
    Conditions: (click “Add condition”)

    • Where {Serial # - Inventory} contains (see instructions)
    1. Click the little gear icon to the right and select “Dynamic”
    2. Click the blue plus icon
    3. Hover over “Record (from Step 1…” and click “Continue >”
    4. Hover over {Serial # - Service} and click “Insert”

    After that’s done, test your action.

    Update records action

    Your action should look something like this:

    Table: Table 2
    Record ID: (see instructions)

    1. Click the blue plus icon
    2. Hover over “Record (from Step 1…” and click “Continue”
    3. Hover over “Airtable record ID” and click “Insert”

    Fields: (see instructions)

    1. Click “+ Choose field” and select “Link”
    2. Click the blue plus icon
    3. Hover over “Records (from Step 2…” and click “Continue”
    4. Hover over “Records” and click “Continue”
    5. Scroll down to “Make a new list of…”, hover over “Airtable record ID”, and click “Insert”

    After that’s done, test your action.

    If everything works out, then you should be good to go… with one little catch.
    The automation only runs on service records that have their serial number field edited after this point. To catch all the records that have already been created, we’ll need to use an older trigger (and one that’s no longer recommended for everyday use, I’ll explain).

    To start, we’ll create a new view, let’s call it “Update Old Records”:

    We’re going to add the filter:
    Where {Link} is empty

    And now we’re going to create an automation, exactly as before, except this time we’ll change the trigger to “When record enters view”.

    To start, you’ll duplicate the original view:

    Next, click on the trigger and change it as follows:

    Test the trigger, and then turn on the automation.

    One last step is to make the records “enter” the view. They’re already there so we need to filter them out first and then remove them, by adding a condition that we know no records satisfy, e.g. a Name filter with a random string of characters:

    Once the records disappear from the view, click the little trash can icon beside the filter. The records should pop back in and trigger the automation. It may take a few seconds before you see anything happen.

    After all of your records have been filtered, you can safely delete the duplicate automation and the view that we created to trigger it.

    Oh, btw, here are the bases I used for the screenshots:
    Base 1 (Inventory)

    Base 2 (Service Records)

    Terms & ConditionsAccessibility statement