+4Creating a table for bidding purposes and after my first field I realized my formula table is going to be crazy! (Trying to avoid another field option) As seen in the screen shot I have (SF) Costs which is a formula for the cost per SF. However, not all cost are the same. Without adding a tremendously long formula for each product, possibly by SKU as the variable, is there a way to add the difference per SF cost?
Thank you.
You’ll probably want to store the products (“6x6x1/2 Diablo Red”, for example) in a different table of their own. In that table, you would have a “Unit Price” currency field where you enter the current unit price for a 12 sqft box of “6x6x1/2 Diablo Red” (you probably also want any other product specific data in that table as well, such as “(SF) per box”).
Then, you’ll want to pull that value into this table you are working in right now via a lookup to that “Unit Price” field, pull in other values like “(SF) per box” via lookups, and then just reference those lookup fields in your formula.
You definitely don’t want to bake things like “Unit Price” into formulas – those values change frequently, so you want to store them in fields that can be easily adjusted, and then just reference those fields in your formulas. And having a table dedicated to holding those values is the best way to handle that.
This way, whenever you need to reference “6x6x1/2 Diablo Red” in a bid proposal, you only need to link to it, and all the product specific values are pulled into your bid via lookups – no need to type them in each time. However, for the sake of preserving historical bids without changing their values whenever you change the “Unit Price” of a particular product, you’ll also want to look into using a Rate Schedule table for the Products.
Hopefully that makes sense the way I explained it!
+4You’ll probably want to store the products (“6x6x1/2 Diablo Red”, for example) in a different table of their own. In that table, you would have a “Unit Price” currency field where you enter the current unit price for a 12 sqft box of “6x6x1/2 Diablo Red” (you probably also want any other product specific data in that table as well, such as “(SF) per box”).
Then, you’ll want to pull that value into this table you are working in right now via a lookup to that “Unit Price” field, pull in other values like “(SF) per box” via lookups, and then just reference those lookup fields in your formula.
You definitely don’t want to bake things like “Unit Price” into formulas – those values change frequently, so you want to store them in fields that can be easily adjusted, and then just reference those fields in your formulas. And having a table dedicated to holding those values is the best way to handle that.
This way, whenever you need to reference “6x6x1/2 Diablo Red” in a bid proposal, you only need to link to it, and all the product specific values are pulled into your bid via lookups – no need to type them in each time. However, for the sake of preserving historical bids without changing their values whenever you change the “Unit Price” of a particular product, you’ll also want to look into using a Rate Schedule table for the Products.
Hopefully that makes sense the way I explained it!
Thank you, however, I currently have a material list of over 600 items. Creating a different table for each would be well you know. I’ve changed my set-up a little by adding a new field where the (SF) Cost is manually inputted per item. Then create a formula. I have a secondary table I use to generate the bid utilizing lookup, as you suggested. I attempted the upload from spread sheet option but without having individual cell formulas I guess its manual entry time.
Thank you, however, I currently have a material list of over 600 items. Creating a different table for each would be well you know. I’ve changed my set-up a little by adding a new field where the (SF) Cost is manually inputted per item. Then create a formula. I have a secondary table I use to generate the bid utilizing lookup, as you suggested. I attempted the upload from spread sheet option but without having individual cell formulas I guess its manual entry time.
:persevere: Sorry. It feels painful now, but in the long run a solid setup is worth the effort.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
