Multiple Selects that have different values that can be used for a calculation

[Jack_Albers]

I am trying to come up with a formula that when I select an item from my multiple select field it uses its separate value to add to the total quote total.

Any advice would be greatly appreciated.

[Justin_Barrett]

Welcome to the community, @Jack_Albers! :grinning_face_with_big_eyes: This wasn’t explicitly stated in your comment, but I’m guessing you want to possibly select multiple items in that multiple select field, and have all of their related values added together to contribute to the final result. Is that correct? If so, I suggest considering a slight redesign of your base.

Extracting a single value from a single selected item is pretty easy. Extracting multiple values from multiple items is not currently possible, at least using formulas. Airtable’s formula system doesn’t provide functions for iterating through an arbitrarily-sized collection of items, which would be necessary to extract those separate values.

A much easier way would be to build a new [Mobilization & Permits] table for those items. In fact, you could convert your existing multiple select field into a link field to a new table, and Airtable will automatically make entries in that table matching your existing multiple select entries. In that [Mobilization & Permits] table, add a number field that contains the value for that item. Back in your [Projects] table, add a rollup field that sums all of the values of the linked items.

[Jack_Albers]

Thanks Justin! I think I figured it out. I have another question if you do not mind. Is there any way to have a quantity field next to a multiple select for a form view preferably or anything close to this? For example if we need 12 Piling Caps and we look up the item and we can add that one. I know you can do a conditional number field that pops up but with thousands of items that can be visually overwhelming and I didn’t know if there was something I was not seeing.

Thank you in advance!

[Jack_Albers]

I also stumbled across one of your other posts which I believe is what I need but I am still a bit confused on setting it up.
Below was your response to what I think I am looking for.

"Yes, and that’s exactly what this [Line Items] table will let you do. Let me try to break it down further, table by table.

[Products] - Here you have the products you’re selling. Ideally you’ll only have one record per product. Each product record has a price for that product.

[Line Items] - Where you build the list of items for your orders. Each record links to a single product. A rollup field pulls in that product’s price. A quantity field lets you specify how many of that product the customer wants. A formula field multiplies the quantity and the per-item price to get the full price. For example, let’s say someone orders 10 of Product A, and the per-item price (from the lookup field) is $1.50, the formula would output $15. They also order 5 of Product B, and its per-item price is $7.50, so that line item’s total is $37.50. They’re buying 15 total items, but you only need two records to track it because the quantity is stored in each line item’s record.

[Orders] - Here you’ll link to a customer record, set the order date, and link to the line item records from the [Line Items] table. Using a rollup field, you bring in the total price for all line items. Using the example above, you have two line item links: one for 10 of Product A, and another for 5 of Product B. The rollup field shows that the order total is $52.50.

Does that make things more clear? If you’d like one-on-one help setting this up, message me and we can set up a time."

[Justin_Barrett]

I’m not sure that I have a clear picture of what you want. In your first reply, you mentioned something about a conditional number field in a form, but in the most recent one, you quote a post where I’m talking about junction tables. There may be some way to tie those concepts together, but I’m not certain that’s where you’re going with this.

Could you give me a specific detailed example of how you envision this setup would look and act like? From there we can talk in more detail about how to pull it off.

[Jack_Albers]

Sorry for any confusion. My main question is how can I link an item multiple times that adds the price value and just stays with that project. I am trying to be able to make quotes for customers but for example if they need 12 piling caps and I select the piling caps it only does one at its base price. Now I have attempted to place a quantity field and link that field with the item and tie it to my quote total but the only problem with that is if I have another quote with the same items, it adds the quantities from the previous projects.

In plain jane I would just like to add items multiple times to specific projects while maintaining its unique quote total and not messing up other projects.

I hope this clears it up a bit. I appreciate your patience.

[Justin_Barrett]

Thanks for the clarification, and sorry for the delay. This situation is a great use case for creating a junction table. Create a table named something like [Line Items]. In that table you’ll have at least the following fields (aside from the primary field; we’ll get to that in a bit):

• Project - This is a link to your [Projects] table; one link per record.
• Item - This is a link to your [Items] table; one link per record.
• Quantity - This is a number field where you specify the quantity of the selected item that you need for the selected project.
• Total Price - This is a rollup field, bringing in the price of the linked item and multiplying it by the value in the {Quantity} field. The aggregation formula would be: SUM(values) * Quantity

Each record defines a relationship between a project and a specific quantity of an item. This way the same item can be used in varying quantities for multiple projects without mixing data between projects.

For the primary field, change it to a formula. This is just one possible option, but the idea is to make it unique enough that you know all of the core details at a glance:

IF(AND(Project, Item, Quantity), Quantity & " x " & Item & " (" & Project & ")")

Back in your [Projects] table, add a rollup field that adds all of the values from the linked [Line Items] records, using the SUM(values) aggregation formula. That will give you a sum of all line items.