Suggestions on IF (maybe) implementation

[Holli_Younger]

To create a more efficient process in pricing materials I’m working through different ideas and it’s just becoming messy. I’m trying to figure out if a formula would provide the best option or possibly a drop-down based on conditions.

Example : I’d post a picture of my base, but it’s not quite finished, which is why I’m reaching out.

If I need to purchase paint and paint supplies to bid materials needed correctly I’m trying to find the best option to fill in QTY TO ORDER

gallon of paint is $30.00. Coverage area is 200 SF

paintbrush is $2.00 needs replaced after 100 SF

All materialS, coverage/price breakdown, is listed in a materials table.

In the Bidding table I’d like to:

COVERAGE AREA	MATERIAL	QTY TO ORDER	PRICE
Manual input SF, LF, tile count, etc.	Manual input based on linked field	based on the details from the materials table, this info is auto calculated	Calculated by formula
EXAMPLE OF WHAT I’M TRYING TO ACCOMPLISH
400 SF	PAINT	2 GALLONS	$60.00
400 SF	PAINTBRUSH	4	$8.00

[Jason · Accepted solution]

Hi @Holli_Younger,

Here’s a base structure that you could use to get started:

Qty to Order
This field uses the SWITCH function to conditionally display a different quantity to order based on the material, and how fast it runs out (by sq.ft.). You can add in more conditions as needed following the same structure here.

SWITCH(
   Material,
   "Paint", Coverage/200,
   "Paintbrush", Coverage/100
)

Total Price
This is just a multiplication of Quantity x Price / Unit. You could build in the Price / Unit into the total price formula instead of having it separate, but it may be a bit easier to enter in the prices in its own separate field.

{Qty to Order} * {Material Price / Unit}

Hope that helps!

[Holli_Younger]

Thank you @Jason.
Few questions:
Is this a new table and linked or is this in the material or bidding table?
Does the switch function only work if the material is a drop down?
Will I have to list every single material in the Switch function?

Thank you for your time.

[Jason]

Holli_Younger:

Is this a new table and linked or is this in the material or bidding table?

This is a standalone table that doesn’t rely on any other tables.

Holli_Younger:

Does the switch function only work if the material is a drop down?

No, it will work with single line, long text, and multiple select fields (any field that stores text as a string).

Holli_Younger:

Will I have to list every single material in the Switch function?

With this approach, yes. You could take another approach where you list out all of the materials their coverage areas in a separate table, and using linked records + a lookup field you can pull in those values into this table. That could be an easier way to manage your list of materials if its a large list.