Getting a price from a range of prices that's overly complex?!?!

[TurboSi]

This may be simple and obvious but I'm still learning. (FM 10 Adv)

This is for quoting / invoicing in a screen printing business. I know how to setup up simple invoicing using a table of "Orders" and a related table of "Order Items". The problem I have is in the pricing. Pricing is dependent on two factors...quantity and number of colors.

I just can't figure out how to get the information I need or how I'm even supposed to set this up. It may be confusing to understand so I'll try to explain.

Say I have a line item for a 100 Gildan shirts. I pull the shirt information from a related table that has price and description. The next field is how many colors are in the design...lets say six. Then I choose what type of printing will be done and this will relate to the pricing matrix. I need be able to go to this matrix style table and say 'if quantity is less than 144 but more than 72 get the price value for 6 colors (out of 12 possible color prices).

I can guess it's a calculated field that first checks "Quantity" and then has to check "Colors". I understand the concept of "range" (google is my friend) but the extra range has me stumped.

One thought was each Quantity has a Price repeating field and use the Colors to lookup the corresponding field (Colors 1-12 / repeat 1-12). But everyone says repeating is bad.

Suggestions?

[comment]

The pricing table should not be a matrix.There should be an individual record for each price, with fields for:

• Colors

• FromQuantity

• Price

Then you look up the matching price from this table, using a relationship based on:

Colors = Colors

Qty ≥ FromQuantity

Note that the relationship needs to sort the Price records by FromQuantity, descending.

[TurboSi]

Ok, that makes sense. So I'll have a record for each color And quantity level.

Somehing like...

1 - 72 - $5

1 - 144 - $4

...

1 - 523 - $2.50

and repeated for each color value. Is there a way to translate this to a matrix style display for editing so I don't have to do each color AND each quantity range? The attached pic is from a paid for version that uses what I'm talking about.

The filemaker solution I bought is poorly done but was the only option available. So I'm recreating the basic setup better to fit the needs of my business.

[comment]

Is there a way to translate this to a matrix style display for editing so I don't have to do each color AND each quantity range?

It's possible, with a bit of work - though you still need to enter a value in each record (matrix cell).

A relatively easy-to-build compromise would be using a helper table of Quantities (~10 records in your example) and a global field for the number of colors. That would give you a one-column wide "window" into the prices table.

[TurboSi]

hmmm...doesn't seem to be an easy way to get price/qnty range easily AND have a simple layout to edit my prices.

I really wanted to avoid repeating fields but I may use them here and use a calculation to get repeatingField[x]. Thankfully if I have to modifiy this table in the future it will be just to add more colors so I can just add more repeating fields.

Maybe I should stick with printing shirts...this stuff gives you headaches.

[comment]

If you or another administrator will be available to modify the pricing structure (as opposed to the actual prices), you could simply generate the required records - say 10 quantity breaks x 8 colors = 80 records - and populate them, either by script or even manually.

Then show these in 8 portals, each 10-rows high, with each portal starting at row +10 relative to the one on the left of it.

[TurboSi]

I appreciate the replies. I can get everything else working the way I want except for the pricing matrix. I probably have to concede the fact that skill is above my current knowledge of FM.

Think I have 2 options. One is what I currently have working which is using calculations (taken from a spreadsheet I generate the price stucture) and calculate on the fly. Not my prefered method since if I want to change how I calculate in the future I'll have to re-code the formulas.

The other option is post this problem into the classifieds and see if I can find someone that can build the basic framework and I'll go back and fill in all the piddly extra fields.

[comment]

Let me suggest again the compromise I described earlier. True, it doesn't show an overview of all prices at once - so what? The entry is still very easy (it took me only a few seconds to fill out the first two columns) and it provides full lookup functionality for the day-to-day operations.

PriceMatrixS.fp7.zip

[TurboSi]

Thank you comment. You are right that if I am the only one (I am) so what? I did have to make one change. On top of the 'matrix' are seperate matrixes for the type of print being put on the shirt. It was simple enough to add a 'Print Type' table, add a foriegn key with lookup and add the field to the 'Line Item'.

I guess I'm just overly attached to seeing the whole matrix at a glance. But I'm sure I could WebView from the spreadsheet and with some careful thought I could probably setup the spreadsheet to be directly imported into FM and populate the records that way. I already to that for the 3000 shirts I list.

Just in case anyone is paying attention to this thread I thought I'd pass along the complex way the print cost get calculated...

Base Cost (divided by quantity range and done for each color [aka screen])

First Color (generally higher than others to cover labor for mounting/unmounting shirt)

Additional Colors (a simple bump for appling each additional color)

So the formula for a 3 color design would be

(Base Cost * 3) / Quantity of shirts + First Color + (Additional Colors * 2)

Since setup is a time consuming operation small run prices usually get bumped an additional percentage over the formula price...say < 24 = price * 1.3

Since dark color shirts are harder to print the First Color and Additional Colors will be slightly higher. Some items are just slower or harder to print (Hoodies/Hats) so they have their own matrix.

There are at the most 4 possible print locations so the pricing field has to be done 4 times...but sometimes the run of shirts might be half front and back and half only back so now you need toggles to turn print location prices off. You may also have part of the shirts are a different design so now you need 'groups' that are 'total qnty', 'line qnty', 'group a', 'group b'...etc.

This has been a hard task but it's pretty close to working. I'm currently using print pricing formulas in calculation fields but may change to comment's suggestion. Odds are I'll be back alot asking questions.

[comment]

Well, the thing is you are sending out mixed signals. On one hand, you are "overly attached to seeing the whole matrix at a glance". OTOH, you complain that "this stuff gives you headaches". So I am trying to fix you up with a compromise instead of the real thing.

Unlike Excel (a spreadsheet), Filemaker (a database) is not a "matrixy" application, and for a good reason. In a database, you can have as many rows as you (the user) want - but the number of columns is fixed as part of the schema. That's why setting up a matrix view is always a bit tricky - and setting up a matrix that the user can modify is even trickier.

Still, as I said earlier, as long as the size of the matrix is constant, there is a relatively simple solution:

PriceMatrixS+.fp7.zip

[TurboSi]

Comment...all I can say is brilliant. It's small little tricks like the one you uploaded I would not have figured out on my own. Sorry if I'm sending mixed signals. I agree with your original assesment if I'm the only one managing the database the first file you created works perfect. I think having the matrix display available is a handy tool when working with a customer so I can show them quickly how pricing vs quantity works. It's faster than constantantly changing the quote when they start asking "What if I get 5 more shirts...Or more colors, less colors...etc"

I can always keep a print out handy but the second ZIP is fab.

The headaches are trying to learn FM. I'm an old dawg and new tricks are hard.

On a side note I'm using a FM solution called "T-Works" built on FM11. (http://www.t-worksmanager.com/)

Today I was writting up an order (most of them are single line items, but each line has fields for every shirt size). This time the second line was the identical line item as the first but the looked up cost was different. The second line was correct but the first line was off by around $0.40

Not asking for help on that issue but it worries me how many orders ended up with the wrong pricing! With the other bugs in the solution I'll have to give them a call again (if they answer) and see what they'll do.