Calculation Headache!!!

[justifi]

Hello,

I'm having problems with working out this calculation. Ultimately I need the discount which is determined by a field called parsing price and will then be subtracted from the product subtotal. So far I'm able to calculate half of it to work and then the other half which is almost identical to the other parts of the same calculation is not working. What I want to do is calculate the discount based on the parsing price. I have copied an example of the problem and an explanations of the different components of the calculation.

This is the calculation for the discount which only works up until I want it to multiply the subtotal by .15. This would only apply when the subtotal price is based on item price four. The part in bold type is the problem area. :

If(Item Price CS = Parsing Price, Product Subtotal * 0, If(Item Price CS > Parsing Price, Product Subtotal * .10,If(Item Price CS Three, Product Subtotal * .10, If(Item Price CS Four > Parsing Price,If(ProductSKU ="Four_Pairs_With_15_Percent_Off" and Product Subtotal = Item Price CS Four , Product Subtotal * .15, If(ProductSKU = "Four_Pairs_With_10_Percent_Off" and Product Subtotal = Item Price CS Four, Product Subtotal * .10, If(ProductSKU = "CS_Four_Discounted" and Product Subtotal = Item Price CS Four, Product Subtotal * .15, If(ProductSKU = "One_of_Each" and Product Subtotal = Item Price CS Four, Product Subtotal * .15, 0)))), 0))))

The values above are all calculated field names with the exception of the data which is placed in double quotation marks, the number which I am using to multiply the subtotal, and the parsing price which is a plain number field that has been parsed from a sales reciept. The parsing price is how much the customer actually paid including discounts when applicable.

Item Price CS refers to the actual cash value of all items priced at $40.

Item Price CS Three refers to the cash value of all items priced at $120.

Itme Price CS Four refers to the cash value of all items priced at $160.

Sometimes the parsing price is equal to the item price. When there is a sale, the item price is more then the actual price/parsing price.

ProductSKU is a text field that has been parsed from another document. The data in this field refers to the name of the item which has cash value.

Product Subtotal = If(Product Sku CSI, Item Price CS,If(Product Sku CSMB, Item Price CS, If(Product Sku CSFG, Item Price CS, If(Product Sku CST, Item Price CS, If(Product Sku CS Three, Item Price CS Three, If(Product Sku CS Four , Item Price CS Four, 0)))))) * Order Qty

Product Sku CSI and similar fields above have been calculated based on the data in the field of ProductSKU.

I hope I have inclueded enough information so that someone can understand what I'm trying to do and where I have gone wrong.

Thank you

[justifi]

Hello Kurt,

The problem is this part of the calculation.

If(Item Price CS Four > Parsing Price,If(ProductSKU ="Four_Pairs_With_15_Percent_Off" and Product Subtotal = Item Price CS Four , Product Subtotal * .15, If(ProductSKU = "Four_Pairs_With_10_Percent_Off" and Product Subtotal = Item Price CS Four, Product Subtotal * .10, If(ProductSKU = "CS_Four_Discounted" and Product Subtotal = Item Price CS Four, Product Subtotal * .15, If(ProductSKU = "One_of_Each" and Product Subtotal = Item Price CS Four, Product Subtotal * .15, 0)))), 0))))

The subtotal is only being multiplied by .10 for Item Price Four. The calculation is not recognizing when I want to it multiply by .15 for Item Price Four. Sometimes different ProductSKUs are in the same price range of Item Price four which has different percentages off depending on the sale period and the productsku. I have tried to connect the calculations based on the actual name of the ProductSKU amongst my many other attempts which I did not list here.

Thank you, Stef/Just

[BobWeaver]

Well, Filemaker is doing exactly what you are telling it to do. The problem is, what you are telling Filemaker is obviously not what you really want.

I have a couple of suggestions.

1. Replace all those nested If() functions with a single Case() function. It will be a lot easier to trace the logic.

2. Your specific problem may be with this part:

...If(Item Price CS Three, Product Subtotal * .10, If(Item Price CS Four > Parsing Price,...

I think you may have intended:

...If(Item Price CS Three > Parsing Price, Product Subtotal * .10, If(Item Price CS Four > Parsing Price,...

[Kurt Knippel]

quote:

---

Originally posted by Just:

[QB]I hope I have inclueded enough information so that someone can understand what I'm trying to do and where I have gone wrong./QB]

---

Not quite. In as simple a statement as possible, what is the result that you are trying to achive. Also what problem do you believe that you are having?

[justifi]

I just wanted to let you know that you were right. The case functions works and the area of the calculation that you had mentioned was throwing everything off. I was also able to get some help from a filemaker Pro club in Yahoo. In addition some one suggested that Item Price Four become a seperate calculation field which the product discounts is linked to for the answer for Item Price Four. It worked perfectly. Thanks again for your direction!