Skip to main content
Solved

Auto-linking between tables?

R
Forum|alt.badge.img+12

Hello Airtablers,

I need to automate links between a table full of User records, including a unique user ID field, and a table full of Invoice records, each Invoice also includes a user ID field. New Invoice records get created more frequently than User records.

For each new Invoice record that gets written to my Invoices table, I want Airtable to recognise the User ID and use this to auto-link to the corresponding User. A single User will have multiple Invoices, and each Invoice uniquely belongs to a single user.

I need to be able to open a User record and have access to all the invoices that correspond to that user, and perform rollup calcs etc on the various fields in the Invoice records.

I can do all this by manually linking each new Invoice record to a User record, but have not figured out how to automate this.

Best answer by JonathanBowen

Hi @Richard_Foxworthy - unfortunately, there isn’t a way to automatically link two tables, at least not within Airtable. However, there’s at least two options to remove the burden of manually linking each invoice record with a user.

From your description, I’m guessing you’ve got a table structure somewhat like this:

Maybe the data has been imported from another system or added manually, but the two tables, whilst containing some common data (the user ID) are not yet connected.

The easy, but manual, way to make the link is to create a user link field on the Invoices table (linking to the Users table):

Now, just copy and past the value in User into User Link. This doesn’t need to be done cell by cell, but can be multiple in one go. You end up with this:

The second method is to create an automation in Zapier or similar. I tend to user the “new record in view” method as I think this gives you more control over when stuff happens. When this Zap fires, you can find the new record, and update it, setting the User Link value to be the User value (essentially mimicking what you are doing manually with the copy and paste).

JB

View original
    Did this topic help you find an answer to your question?

    4 replies

    JonathanBowen
    Forum|alt.badge.img+18
    • JonathanBowen
    • Inspiring
    • 1110 replies
    • Answer
    • October 29, 2019

    Hi @Richard_Foxworthy - unfortunately, there isn’t a way to automatically link two tables, at least not within Airtable. However, there’s at least two options to remove the burden of manually linking each invoice record with a user.

    From your description, I’m guessing you’ve got a table structure somewhat like this:

    Maybe the data has been imported from another system or added manually, but the two tables, whilst containing some common data (the user ID) are not yet connected.

    The easy, but manual, way to make the link is to create a user link field on the Invoices table (linking to the Users table):

    Now, just copy and past the value in User into User Link. This doesn’t need to be done cell by cell, but can be multiple in one go. You end up with this:

    The second method is to create an automation in Zapier or similar. I tend to user the “new record in view” method as I think this gives you more control over when stuff happens. When this Zap fires, you can find the new record, and update it, setting the User Link value to be the User value (essentially mimicking what you are doing manually with the copy and paste).

    JB

    J
    T
    R
    3 people like this
    • J
      Joyce_Lam
    • T
      Tomas_T
    • R
      Richard_Foxwort
    R
    Forum|alt.badge.img+12
    • Richard_Foxwort
    • Author
    • Known Participant
    • 45 replies
    • October 29, 2019
    JonathanBowen wrote:

    Hi @Richard_Foxworthy - unfortunately, there isn’t a way to automatically link two tables, at least not within Airtable. However, there’s at least two options to remove the burden of manually linking each invoice record with a user.

    From your description, I’m guessing you’ve got a table structure somewhat like this:

    Maybe the data has been imported from another system or added manually, but the two tables, whilst containing some common data (the user ID) are not yet connected.

    The easy, but manual, way to make the link is to create a user link field on the Invoices table (linking to the Users table):

    Now, just copy and past the value in User into User Link. This doesn’t need to be done cell by cell, but can be multiple in one go. You end up with this:

    The second method is to create an automation in Zapier or similar. I tend to user the “new record in view” method as I think this gives you more control over when stuff happens. When this Zap fires, you can find the new record, and update it, setting the User Link value to be the User value (essentially mimicking what you are doing manually with the copy and paste).

    JB


    Thanks Jonathan, this looks like exactly what i need! One question - does it rely on usng the ID field used for matching as the primary field in users table?

    Thanks!

    S
    1 person likes this
    • S
      Saul_Pacheco
    JonathanBowen
    Forum|alt.badge.img+18
    Richard_Foxwort wrote:

    Thanks Jonathan, this looks like exactly what i need! One question - does it rely on usng the ID field used for matching as the primary field in users table?

    Thanks!


    Hi Richard - yes, the value you copy has to be the primary field in the Users table. It could be anything - ID, email, whatever, but would need to be unique so that the link works properly. The key to this method (either by copy and paste or via Zapier) is that you have some unique value in both tables that you can use to link on.

    If I remember correctly, if you have a User ID in the Invoices table that isn’t in the Users table, Airtable will create a record in the Users table (which may or may not be desirable).

    Jonathan

    R
    1 person likes this
    • R
      Richard_Foxwort
    R
    Forum|alt.badge.img+12
    • Richard_Foxwort
    • Author
    • Known Participant
    • 45 replies
    • October 29, 2019
    JonathanBowen wrote:

    Hi Richard - yes, the value you copy has to be the primary field in the Users table. It could be anything - ID, email, whatever, but would need to be unique so that the link works properly. The key to this method (either by copy and paste or via Zapier) is that you have some unique value in both tables that you can use to link on.

    If I remember correctly, if you have a User ID in the Invoices table that isn’t in the Users table, Airtable will create a record in the Users table (which may or may not be desirable).

    Jonathan


    Thank you for you help. Much appreciated

    Reply


    Most helpful members this week

    Terms & Conditions