Create a Picklist from a Parts Table

[Bobby_Gay]

Dear Community,
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.

Examples:
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.

[TheTimeSavingCo]

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:

1. 2 x Product 1

• Product 1 consists of:
  • 3 x Item 1
  • 2 x Item 2
  • 10 x Item 3

The final packing list will be:

• 6 x Item 1
• 4 x Item 2
• 20 x Item 3

===

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:

1. Use “Find Records” to find the parts that are needed for the sale
2. Use a script to create records in the Pick List table

However, unless you want to learn JavaScript or know someone who does, using this option would mean you’d have to hire someone to make changes

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!

===

Workflow:
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

Setup:
We’ll have to create a record per product in the Products table

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.

In the 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!

[Bobby_Gay]

Thanks Adam! I’ll give this a try and let you know how it works.