You can define matching fields for each possible unit price with looked up value settings to copy them into the record from your products table.
Then your calculation field can use a case function to compute the price:
Case ( Qty < 6 ; Qty * UnitPriceLT6 ;
Qty < 12 ; Qty * UnitPriceLT12 ;
Qty * UnitPriceGE12 )
Case will branch on the first true statement, BTW. UnitPriceLT6, UnitPriceLT12, UnitPriceGE12 would be fields that look up your qty based unit prices from corresponding fields in the products table.
So what I should do is create fields in the products table for every instance of a quantity price, including 1 piece, then have all of the "cases" listed between the parantheses? Also, do I assume I would put them in numerical order, lowest quantity being listed first? Then my "price per unit" field in my lineitem table would be the above case calculation?
Also, I still haven't been able to figure out the conditional value list to get only the mfg products to show in the pop up menu for product id based on the mfg that is selected in the order. I'm sure I just don't have the relationship set up correctly.
We have lots of dealers and lots of mfgs. Some of our dealers drop ship their orders so I have an address table with all the different ship to addresses associated with each dealer. I was able to get the ship to address to only pull the address for the dealer selected but still can't get the products to only show for the mfg selected for the order. I have the lineitems set up in a portal. Is there something different that I should be doing for the conditional value list because of the portal?
Thank you for all of your help.
Define a series of fields in Products for this. If the price/quantity breaks are not consistent across all products, you can define pairs of fields--one for the quantity and one for the unit price at that quantity. You define the same fields in your line items table and set them to look up the values from products--that way changes to pricing in products only affects new transactions and doesn't affect past transactions.
Here's a different example, where the quantities at which a price change kicks in might vary with each product:
Case ( Qty < Break1Qty ; Qty * UnitPrice1 ;
Qty < Break2Qty ; Qty * UnitPrice2 ;
Qty * UnitPrice3 )
With this expression, you can have breaks at 6 and 12 for one product and 50 and 500 for another.
You stack your tests in the case function so that the first expression to evaluate as true is the one you need. In this example where we are using the < operator to identify quantities less than a given value, you'd arrange them from smallest limit to largest. With other expressions, this may not be the case.
With regards to a conditional value list, you haven't told me what you've tried to set up at this point nor what relationships you have defined.
For starters, have you read this thread that provides a step by step tutorial on setting up a conditional value list?
I created a template in excel for each mfg to have the same breakdowns. It seemed easier that way. Some mfgs will have the same price across the board so it won't matter what quantity is put in. Here are all of them: 1, 2, 3, 5, 6, 10, 12, 16, 20, and 24. Only one mfg will have all of them and a couple have 1-5 and 6+, the rest all have one price. I think your first example seems like the way to go. I have a table called lineitem for the products on the order with a field for priceperunit. This is the field that will have the calculation, correct?
I have looked at the tutorial you sent me before and I'm still a little confused. I'm not as concerned with that at the moment as I am about the pricing. I figured I can always set that up after.
I'm going to play with what you've given me and let you know if I have any more questions. I really appreciate your help.
I have a table called lineitem for the products on the order with a field for priceperunit. This is the field that will have the calculation, correct?
Not quite, you should have a calculation that multiplies quantity by unit price. The case function example I gave would be the definition for that calculation field. PricePerUnit could be set up for this but it would take a different expression:
Case ( Qty < 6 ; UnitPriceLT6 ;
Qty < 12 ; UnitPriceLT12 ;
Either way can be made to work.
Using the 10 levels you've described is simply a case of adding more terms to either this Case Function and/or the original that I posted.
On your conditonal value list, feel free to follow up with a more specific description of what you set up for the value list, the fields, the relationships and how it failed to work.
Sorry, one last question. On the above example you gave me for priceperunit, since the first 3 price breaks are in a row, 1, 2 and 3 would it go like this:
Case ( Qty = 1 ; UnitPrice1 ; Qty = 2 ; UnitPrice2 ; Qty = 3 ; UnitPrice3 ; Qty <6 ; UnitPriceLT6 ; Qty < 10 ; UnitPriceLT10 ; etc.
What is the last line UnitPriceGE12, would that be the last one, so in my case UnitPriceGE24?
You are correct.
The last line is the "else" clause. If none of the preceding expressions are true, this is the result returned by the function.
For clarity, I sometimes include comment text like this in a Case function:
Case ( Qty < 6 ; UnitPriceLT6 ;
Qty < 12 ; UnitPriceLT12 ;
/* Else */ UnitPriceGE12 )
Anything enclosed in /* */ is ignored by FileMaker so this is one way to document calculation details. (And you can break up your calculations in multiple lines like I have done to make them easier to read, analyze and edit.)
It worked!!! Now I just realized I have another issue. A couple of our mfgs have different discount programs instead of quantity pricing. For example, the customer is either direct dealer, direct dealer plus or distributor, so their pricing is based on their discount program.
So my question is can you have more than one case in a calculation field? Or would it be better to have a script trigger on the priceperunit field so when I tab out of the quantity field into the priceperunit field, the script runs.
And the plot thickens.
Yes, you can "nest" case functions inside of each other, though I suspect you can just add additional pairs of Boolean expressions | Unit price values to the existing case function. Just keep in mind that the value returned is determined by the "first true".
Syntax for a nested expression:
Case ( a = b ; Case ( D < 5 ; //True result here ; //else result here ) ;
a = c ; Case ( D = 3 ; //True ; //else )
You can also combine the value returned by one case with the value returned by another in a larger calculation:
case ( a = b ; 25 ; 0 ) + case ( b = c ; 30 ; 40 )