Jun 28, 2022 04:44 PM
I’m new to Airtable. I have a table that includes parts and tasks. In other words, if Task 1 includes 5 different parts each record will have a value of “Task 1”. The same is true for parts associated with Task 2.
What I’m trying to accomplish can be done in Google Sheets using a Query function, but I’ve not figured out how to do this in Airtable. When our company makes a sale of a new Air Conditioning system, there is a product description list from which the sales person makes a selection.
3.0 ton Complete Gas System HVAC and Furnace - 14 SEER
3.0 ton Complete Gas System HVAC and Furnace - 16 SEER
3.5 ton Complete Gas System HVAC and Furnace - 14 SEER
3.5 ton Complete Gas System HVAC and Furnace - 16 SEER, etc.
Each product has a list of Parts and the appropriate Quantity(s) to deliver the job. So, if the first product is selected, there may be 10 parts related to that product - all of which have the same product name in each of those records. The goal is to produce a picklist of those 10 parts so our purchasing team can order the correct parts for the installation team to use upon delivery.
How should I set things up so that the user can select from the list of products we offer (either as a single-select field or a lookup). Once the product is selected, the appropriate parts and quantities are shown.
I appreciate any ideas any of you can offer.
Jun 28, 2022 09:50 PM
Hi Bobby, I’ve put something together here that I think does what you need
This setup will handle having multiple products of differing quantities per sale, where each product is composed of different parts and different quantities of each part.
Consider the following sale:
The final packing list will be:
Before we go any further, I’d like to note that if you’re on a Pro plan, we could also accomplish this via an automation where we:
As such, I prefer this method that uses only automations and formulas. Let me know if you’d like to explore the scripting side of things though!
To use this, we will first create a record in the
Sales table. After that, we go to the
Line Items table and create a record per product that is being sold as part of that sale, and we link the
Product record and set the quantity that is being sold
Records will automatically be generated in the
Pick List table
We’ll have to create a record per product in the
We will also have to create all the appropriate records in the
Parts table, and link each one to the correct
Product record, as well as set the number of parts needed for that product.
How it works:
For each record, we’re formatting all of the relevant data into a single line that we then roll up on in the
Line Items table.
We then use an automation to paste the unique comma separated values into a linked field to the
Pick List table, which creates a record per part.
Pick List table, we have formula fields that will extract each piece of data (e.g. the product that was sold, the quantity of said product that was sold,etc)
And finally, we multiply the number of parts needed per product by the number of that product that was sold
Let me know if you have any questions!
Jun 29, 2022 01:41 PM
Thanks Adam! I’ll give this a try and let you know how it works.