Help

Re: How to pull data from multiple lookup fields into one field

1841 2
cancel
Showing results for 
Search instead for 
Did you mean: 
Fae
5 - Automation Enthusiast
5 - Automation Enthusiast

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 5

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!

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 🙂

> 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