Skip to main content

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

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!

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.

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.



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



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



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.

Reply


Terms & Conditions