Skip to main content

How to pull data from multiple lookup fields into one field

  • December 29, 2022
  • 5 replies
  • 266 views
F
Forum|alt.badge.img+1
  • Fae
  • Participating Frequently

I have a base where I track my inventory, sales, and purchases. Because of the nature of the way data is uploaded to the base, I have multiple tables for different purchase sources. They are all linked to a master inventory table. What I am currently doing is using different views to see the relevant info, but this is frustrating and not really working for what I'm trying to do. 

The problem is that I have the sales tables relying on the inventory tables to populate data, and it's ugly. I'm trying to use rollups and other table-to-table formulas, but because the data is all in different fields it's cumbersome and requires a ton of work arounds. Is there a way I can combine all the same fields into one field at any step of the way (on one table, or from upload table to aggregate table, or aggregate to sales)??

The reason I don't upload everything to one table is because each one is named so differently, with 2/4 being csv's I regularly upload and the other 2 being very different ones I built

 

    5 replies

    TheTimeSavingCo
    Forum|alt.badge.img+31

    Hm, assuming the setup is:
    1. Linked field 1
    2. Lookup for Linked field 1
    3. Linked field 2
    4. Lookup for Linked field 2

    Then you could potentially try a formula field with something like

    IF( {Linked field 1}, {Lookup for Linked field 1}, IF( {Linked field 2}, {Lookup for Linked field 2} ) )

    And then just extend that as needed?

    Not sure I fully understand your problem tho, apologies if I've misunderstood

    Adam, Airtable Consultant - thetimesaving.company
    F
    Forum|alt.badge.img+1
    • Fae
    • Author
    • Participating Frequently
    • January 8, 2023

    Hm, assuming the setup is:
    1. Linked field 1
    2. Lookup for Linked field 1
    3. Linked field 2
    4. Lookup for Linked field 2

    Then you could potentially try a formula field with something like

    IF( {Linked field 1}, {Lookup for Linked field 1}, IF( {Linked field 2}, {Lookup for Linked field 2} ) )

    And then just extend that as needed?

    Not sure I fully understand your problem tho, apologies if I've misunderstood


    it's just giving me the link to the photo- I should have mentioned a big part of this is photos. But i will try it for the non-attachment fields, thank you!

    TheTimeSavingCo
    Forum|alt.badge.img+31

    it's just giving me the link to the photo- I should have mentioned a big part of this is photos. But i will try it for the non-attachment fields, thank you!


    Ahh, yes that's a problem.  If it's feasible, you could use an automation for this?

    So you'd have one "Attachment" field in your main table, and you'd have an automation that would run on update of any of the fields: `Lookup of Attachments for Linked field 1`, `Lookup of Attachments for Linked field 2`, etc

    And it would update the triggering record's "Attachment" field with all of the attachments from the lookup fields

    So if `Lookup of Attachments for Linked field 1` had 2 attachments, and `Lookup of Attachments for Linked field 2` had 3 attachments, you'd end up with an "Attachment" field with 5 attachments

    The downsides of this would be:
    1. Might use up a lot of automation runs if this data is updated frequently
    2. Would use up a lot of attachment space in your base

    Adam, Airtable Consultant - thetimesaving.company
    F
    Forum|alt.badge.img+1
    • Fae
    • Author
    • Participating Frequently
    • January 9, 2023

    Ahh, yes that's a problem.  If it's feasible, you could use an automation for this?

    So you'd have one "Attachment" field in your main table, and you'd have an automation that would run on update of any of the fields: `Lookup of Attachments for Linked field 1`, `Lookup of Attachments for Linked field 2`, etc

    And it would update the triggering record's "Attachment" field with all of the attachments from the lookup fields

    So if `Lookup of Attachments for Linked field 1` had 2 attachments, and `Lookup of Attachments for Linked field 2` had 3 attachments, you'd end up with an "Attachment" field with 5 attachments

    The downsides of this would be:
    1. Might use up a lot of automation runs if this data is updated frequently
    2. Would use up a lot of attachment space in your base


    That got me thinking- maybe this would be more suitable for a script? Since I could then just run it whenever I add a new csv, which is maybe monthly so not really something I need fully automated. Do you know anything about scripting? Thank you so much for your time with this btw 🙂

    TheTimeSavingCo
    Forum|alt.badge.img+31

    That got me thinking- maybe this would be more suitable for a script? Since I could then just run it whenever I add a new csv, which is maybe monthly so not really something I need fully automated. Do you know anything about scripting? Thank you so much for your time with this btw 🙂


    > That got me thinking- maybe this would be more suitable for a script? Since I could then just run it whenever I add a new csv, which is maybe monthly so not really something I need fully automated
    Ahh...yeah, you could create it as an extension and then just run it whenever you uploaded the CSV like you said, no automations needed

    > Do you know anything about scripting?
    Yeap, I do

    Adam, Airtable Consultant - thetimesaving.company
    Terms & ConditionsAccessibility statement