I think this is a brain teaser that can be done

[Baylah]

My company is in the wholesale embroidery business. The way our pricing is based is by the number of stitches in a particular design, and the quantity of garments we produce. This is on a sliding scale.

The way it works is "X" amount is charged for the first 3,000 stitches and then a factor is applied to the additional # of stitches based on quantity. Price list looks like this:

(sorry I can't get this table to format in the forum)

First 3K Sts Base Price

1-11 12-23 24-47 48-71 72-143 144-576 577-1,200

$5.50 $3.45 $2.90 $2.10 $1.90 $1.70 $1.50

Price per additional 1000 stitches

.55 .35 .30 .27 .27 .27 .27

So, if customer brings us a job with 48 pieces and a 5,000 stitch logo the price would equal=

$2.10 + .54 = $2.64

What I have never been able to figure out is a calculation that could automate pricing if the only information/fields I "GIVE" for the calculation is quantity of garments and stitch count of logo.

It seems like it could be done with a complex calculation of strung together if/then statements, but this is beyond me to figure out.

Anyone wnat to take a stab at it?

Thanks in advance for your help. I am trying to develop a quote generator and this could be an incredibly helpful tool.

Steve

Thanks,

Steve

[comment]

Nah, this is basic stuff.

Create a PriceList table with the following fields:

MinimumPieces;

BasePrice;

Add1kStitches;

gBaseStitches (this can be global, if the 3k limit will always apply to any number of pieces).

You need 7 records in your PriceList table. This is an example of record #2:

MinimumPieces: 12

BasePrice: 3.45

Add1kStitches: 0.35

gBaseStitches: 3000

Define a relationship between your Orders/Invoices/Quotes table/s (where you enter the number of pieces and stitches) and PriceList:

Orders::Pieces = PriceList::MinimumPieces

Then define the following fields in Orders:

BasePriceLU (Number, Lookup, lookup from PriceList::BasePrice, copy next lower value);

Add1kPriceLU (Number, Lookup, lookup from PriceList::Add1kStitches, copy next lower value);

BaseStitchesLU Add1kPriceLU (Number, Lookup, lookup from PriceList::gBaseStitches, copy next lower value);

cPricePerPiece (calculation, number) =

BasePriceLU

+

Case (

Stitches > BaseStitchesLU ;

Ceiling ( (Stitches - BaseStitchesLU ) / 1000 ) * Add1kPriceLU

)

cPrice (calculation, number) =

cPricePerPiece * Pieces

[jrb12]

Steve,

Because the calculations in this problem will be performed over and over again during the life of the app, its worthwhile, I think, to add extra effort in the setup of the solution in order to save in processing during the execution of it. Not that Comment's PriceList table would necessarily make a noticable difference in response given the load you may have. Still performance may be an issue and this is just an alternative way of doing this. One that does not require a dedicated table or any relationships.

My first thought in reading your post was to use arrays to map the input (pieces) to the calculation parameters (BasePrice and CostPer1kStitches). What to use for the mapping function really determines how efficient the lookup is going to be. I thought that since your cutoff points for the Pieces are divisible by 12, the BasePrice and CostPer1kStitches fields could be defined as repeating fields with the number of repetitions set at 48 (12*48=576). Preload these fields with the corresponding value for the given number of Pieces. For instance Base Price would hold [5.50,3.45,2.90,2.90,2.10,2.10,1.90,...]. Most of the fields would hold 1.50, in fact the last 36 repetitions would. Then use the integer value of Pieces divided by 12 as the index.

Then it seemed a waste to use fields at all. You don't really need the repeating fields, Something like this in a calculating field would work:

Let( BasePrice = Choose( Int(Pieces/12); 5.50; 3.45; 2.90; 2.90; 2.10; 2.10; 1.90; ... ); ...

Ok, forget all that, use a Case function with a series of progressive tests like so:

Let([

BasePrice = Case( Pieces<12; 5.50; Pieces<24; 3.45; Pieces<48; 2.90; ...; 1.50 );

CostPer1kStitches = Case( Pieces<12; .55; Pieces<24; .35; Pieces<48; .30; .27 );

kStitchesCount = If(NoOfStitches>3000; Int((NoOfStitches-2001)/1000); 0)];

BasePrice + kStitchesCount * CostPer1kStitches)

All this considering fields called Pieces and NoOfStitches as input to the calculation.

It may seem bizarre to setup something that so many redundant memory cells. And probably not appropriate here but its likely pretty quick.

Cheers,

John ... whose remember those long nights tuning a Fortran nuclear reactor simulator.

[comment]

John,

The reason for having a separate PriceList and looking up the price is to enable the prices to change - without affecting existing Orders, Invoices etc...

Also, for the very purpose of setting up the solution once for extended use, it is better, IMHO, to provide the USER with a way to change the prices, without having to call in the developer.

[jrb12]

Comment,

Your point is well taken. It is important to give the user a method of updating the constants in the calculation. My intent, I suppose, was to give Steve an example of the calculation he requested. With his advanced skill level I assumed he would take it at face value and implement it where he saw fit.

Having said that, the calculation does rely on embedded constants. Something that, as you point out, is not accessible to the end user. For me, the number of times the user needs to access and change these constants does not warrant the overhead of the incessant table accesses to retrieve them. This has been a major area of inefficiency in systems I've worked on over the years. There could be a method of satisfying both objectives in Filemaker and that's the interesting problem that Steve presents. The reason why I responded on this thread. This is the Brain Food forum after all, not Define Fields.

Its all academic for Steve's app as he only needs the result of the calculation at the time the record is stored. If this calc were to run several million times per day, what's the best way to satisfy both objectives in Filemaker?

Cheers,

John.

[comment]

Well, it's a matter of priorities. Efficiency is certainly important, but user interface and ease of maintenance come first in my book. I cannot imagine providing someone with a business solution, and when being asked "How do I change my prices?" answering "You will have to call me". I don't believe I could justify that by "Oh, but it's much more efficient this way".

Also, in a business application as described here, I believe that we need more that just "the result of the calculation at the time the record is stored". The poster mentioned a quote generator, but even for billing, the end result must be properly justified to the customer. If I were the customer, I would certainly raise an eyebrow at getting a bill that says:

48 pieces, 5000 stitches .. $126.72

As a customer, I would expect a reputable business to show me how such a princely sum was computed, before I would consider paying it.

Now, if maximum eficiency were the highest priority, then of course the Case() function would be out, because it cannot be optimized with branch prediction. So I would go back to Choose and try very hard to find a formula that would fit the pricing structure (I won't do it now). Perhaps the user would even find it necessary to modify his pricing structure. In worst case, "dummy slots" in Choose() do not compute, so one could have even Choose( Div ( pieces ; 12 ) ; ...[a very long list here]..., with no hit on performance.