1 2 Previous Next 20 Replies Latest reply on Nov 25, 2014 11:23 AM by TTerz

# Need serious help with a calculator.

### Title

Need serious help with a calculator.

### Post

Hi all

I've been working on this for some time now and haven't made much headway. I'm pretty much at a loss from this point on.

If you take a look at the 'quote-page' pic (It's the dark themed one) You can get an idea on what I'm trying to do. I'm working on a quote calculator that has to look up a value in a range of data. You select the product ID (which is 2000 in the pic) and that pulls up the price on the right (3.99). Easy enough. Then you need to select a quantity. This is the tricky part because the quantity falls into ranges and needs to look up a value in said range based also on a number from 1 - 8. The other picture 'print-prices' shows this range.

So for example, in the first picture (we'll call it quote pic) the sample quantity is 50, number of colors is for location one is 1, and number of colors for location two is 2. On pic two, A qty of 50 falls in the qty range of 48 - 71 and 1 color for that range is \$2.03. Two colors in that range is \$2.97. Those prices are reflected on the quote pic on the right side. The sum of these prices adds to the price of the apparel and we get the grand sum of the order. Does this make sense?

To use this thing, you select the product, qty, and number of colors per location and those get added up.

There is a third pic, which just shows the product itself. Really doesn't mean much for this example but it's attached non the less.

pic 2

• ###### 2. Re: Need serious help with a calculator.

pic 3

• ###### 3. Re: Need serious help with a calculator.

Nobody eh?

• ###### 4. Re: Need serious help with a calculator.

Your question is not exactly clear. I suspect the issue is you can not get the correct C1 C2 C3 ... C8 quantity break prices to populate when you enter number of colours in print locations 1-4 yes?.

I would need to see how you are relating the order quantity in your quote to your print prices

If I was doing it, I'd have a dropdown list for your order quantity with the range 1 to 14 as per your key field. I would redesign your 2 quantity fields to a single range field showing the range say 1-14 15-23 ... and so on. I would then have a value list based on the field key but also showing values from the range to identify the correct quantity break.

The relationship should be pretty straight forward Quote::QuantityBreak --> PrintPrices::FieldKey

Each of your 4 Print Price Location fields should be calculations perhaps based on a case statement such as this.

Case(Quote::ColoursLocation1 = 1 ; PrintPrices::C1; Quote::ColoursLocation1 = 2 ; PrintPrices::C2 ; ...; Quote::ColoursLocation1 = 8 ; PrintPrices::C8 ; 0 )

I'm not saying this is the correct way to do it or if it would even work. But off the top of my head that is the way I'd approach it. You may have a specific need to show the exact quantity ordered, but from what I see with what you've shown, you're generating a per piece price based on the price break quantity range.

Anyway good luck, I hope it gives you food for thought and an answer :)

• ###### 5. Re: Need serious help with a calculator.

Eoin

I changed up the quantity field to make it a pop up menu for the quantity range, and also made a relationship between Quote::QuantityBreak --> PrintPrices::FieldKey. I attached a screen shot of that relationship.

I think you understand what I'm trying to do, for the most part. The 'Print Price Location 1' field would ideally be auto-calculating the correct print price based on the information they find in the 'Print Prices' table. 'Print Price Location 2', 3, and 4 will be using the exact same calculation as well. That calculation is based on the 'quantity range' (0-14, 15-23, ...) and the number of colors (C1, C2, ...) That being said, wouldn't your case formula need a lookup function of some sort to help it calculate the correct row and column of data?

• ###### 6. Re: Need serious help with a calculator.

I am sorry, it was late when I replied and perhaps I didn't explain myself clearly.

If your quote had a quantity break field linked to your quantity break in your print prices table then a case statement in each of your print cost 1 - 4 would evaluate based on the colour counts in locations 1 - 4.

I'm not to clear on how to upload multiple images so I will post in separate posts to better show you how it can be achieved. I am in no way saying this is the best method or the most efficient, but with my limited knowledge this is how I get it to work.

• ###### 7. Re: Need serious help with a calculator.
/files/4be3b6eafc/2.jpg 2748x1004
• ###### 8. Re: Need serious help with a calculator.
/files/e6c2aaef8d/3.jpg 2824x1624
• ###### 9. Re: Need serious help with a calculator.
/files/c1c65b5376/4.jpg 2132x1186
• ###### 10. Re: Need serious help with a calculator.
/files/900af46a03/5.jpg 2124x1402
• ###### 11. Re: Need serious help with a calculator.

Do you have a picture of what your 'Print Prices' table looks like in the manage DB screen? I tried to recreate what you have done here, but I'm still only getting zeros calculated.

• ###### 12. Re: Need serious help with a calculator.

I suppose I just don't understand how you are using your 'QuantityBreak' field on the 'Quotes' page. Do you have to enter a number into it for the whole thing to work? Is it auto-entered? I just says that it is a text field on your manage database screen. Is it somehow tied in with the 'Order_Quantity' or 'QtyRange' field? I've attached all of my work thus far. It seems like I am close but something is still snagging the whole thing from calculating properly. Thanks for you help.

• ###### 13. Re: Need serious help with a calculator.

The reason for the quantity break field is simplicity, call them IDs or quantity bands. In my mind it makes it easier to deal with a simple number 1, 2, 3, 4, ...  24 than deal with 1-14, 15-23, 24-39 .. and so on. in the case statement it's easier to deal with also when coding.

In my initial post to you I suggested a drop down list on your quotes layout showing the values from two fields, the quantity break and the quantity range but store the value from the quantity break. By storing the value from the quantity break (which is the relationship) the quote layout is able to look across to the print prices and see only the one related record and the prices in C1, C2, C3 ... C8 fields.

Here is a screen shot you asked for.

• ###### 14. Re: Need serious help with a calculator.

Any chance you could email me your file so I can look over it? My email is terzdesign@gmail.com

1 2 Previous Next