Hi all, I have set up an auxiliary table that has values in columns based on condition (the column name). I have a main table in which I am trying to pull data for items based on the condition of that item. So, I am trying to get Airtable to go to the auxiliary table, look up the requisite item from the main table, and find the value based on the condition that is noted in a field in the main table (please see screenshot attached). There is a field on the Inventory tab that notates the condition and a bunch of columns on the DB tab that have values for each condition. I need to write an automation or formula that tells Airtable to look up the condition of the item on the Inventory table, go to the DB table find the appropriate item, and look up the value based on the condition in the Inventory table. Thanks for any assistance you can give me.
+18Hmm, could you provide screenshots of both the main and auxiliary table, and indicate which two records in the screenshots would be linked to each other? Could you also indicate the field(s) you'd be using to match the records, as well as the fields you'd want to pull over into main table as well?
Hmm, could you provide screenshots of both the main and auxiliary table, and indicate which two records in the screenshots would be linked to each other? Could you also indicate the field(s) you'd be using to match the records, as well as the fields you'd want to pull over into main table as well?
The main table is called Inventory - the auxiliary table is called Comic_DB. The field I am using to match the records is the Issue ID field in the Comic_DB table. I want to be able to pull the value from the appropriate column in the Comic_DB table to fill in the Overstr (53) column in the Inventory table depending on the value in the Grade field of the Inventory table.
+18The main table is called Inventory - the auxiliary table is called Comic_DB. The field I am using to match the records is the Issue ID field in the Comic_DB table. I want to be able to pull the value from the appropriate column in the Comic_DB table to fill in the Overstr (53) column in the Inventory table depending on the value in the Grade field of the Inventory table.
Ah I see. Hmm, for a formula based solutuion, I think you'd need to create lookup fields to the Inventory table to display all of the possible grade prices, and then use a formula field with a SWITCH to determine which value to display
For an automation, it would depend on how many grades there are. The idea would be to have one conditional group per grade, and in each of those groups you'd update the Inventory record with the appropriate value. e.g. the first group would be 'if Grade is 9.8 (Over)', then the 'Update Record action would grab from the '9.8 (Over)' field to update the 'Overstr (53) field. And so if you have a lot of grades this might not be feasible to set up
Finally, you could use an automation with a Run a Script action to to handle it
Ah I see. Hmm, for a formula based solutuion, I think you'd need to create lookup fields to the Inventory table to display all of the possible grade prices, and then use a formula field with a SWITCH to determine which value to display
For an automation, it would depend on how many grades there are. The idea would be to have one conditional group per grade, and in each of those groups you'd update the Inventory record with the appropriate value. e.g. the first group would be 'if Grade is 9.8 (Over)', then the 'Update Record action would grab from the '9.8 (Over)' field to update the 'Overstr (53) field. And so if you have a lot of grades this might not be feasible to set up
Finally, you could use an automation with a Run a Script action to to handle it
Thank you! Could you give me an example of how a Switch formula would look?
Thank you! Could you give me an example of how a Switch formula would look?
Additionally, I was initially going down the road of the automation angle that you laid out earlier; however, I kept running into either an invalid input error or a record not found error. Please see screenshots.
+18Additionally, I was initially going down the road of the automation angle that you laid out earlier; however, I kept running into either an invalid input error or a record not found error. Please see screenshots.
Yeap sure, here's how a formula might look:
SWITCH(
Grade,
'A', {Grade A (from Data)},
'B', {Grade B (from Data)},
'C', {Grade C (from Data)}
)
---
And the automation would look like this:
You're gettin an error as your 'Record ID' input field should have an Airtable record ID instead as can be seen in the screenshot above
I've also set it up for you here so you can see how I did it!
---
The automation one is harder to set up but I think it would be better in your context as it'd let you store the historical price at that pointm (I'm assuming the prices in Comic_DB get updated from time to time?)
Yeap sure, here's how a formula might look:
SWITCH(
Grade,
'A', {Grade A (from Data)},
'B', {Grade B (from Data)},
'C', {Grade C (from Data)}
)
---
And the automation would look like this:
You're gettin an error as your 'Record ID' input field should have an Airtable record ID instead as can be seen in the screenshot above
I've also set it up for you here so you can see how I did it!
---
The automation one is harder to set up but I think it would be better in your context as it'd let you store the historical price at that pointm (I'm assuming the prices in Comic_DB get updated from time to time?)
Awesome, thanks so much! One question though...would it not be feasible/possible to have the grades as columns for each issue rather than having a line item for each grade of an issue? There are approximately 6000 individual issues with 15 grades apiece - that would be a bit unwieldy.
+18Awesome, thanks so much! One question though...would it not be feasible/possible to have the grades as columns for each issue rather than having a line item for each grade of an issue? There are approximately 6000 individual issues with 15 grades apiece - that would be a bit unwieldy.
Hmm, sorry, I think I'm not understanding something. The 'Data' table is set up to have one column per grade, and the 'Inventory' table is set up to have one row per grade per card. This matches the screenshots of your own base, right?
Reply
Most helpful members this week
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.