Lookup Table or script generating value based on conditions in four separate fields

fm can do it.
principle: have a table to keep the price list.
in the main table, have a price field to copy the item price from the price list using an auto-entered value from a lookep-up value.
then multiply it with a qty field value to get the resulting price
this keeps the price in the main table separate from its reference which may be altered later on in the price list without changing cost already calculated. This is the classic quotation construction principle, if you increase prices to day, you do not want to alter quotations done yesterday.
Let me know if you need further help to build this up, as I do not know yr skill yet, you seem to be new here.
You should end up with a classic 3 tables system:
quotations
quotations items
itemps price
I can also make an example, but you need a bit of patience, I am not behind my machine but squatting somebody else's who does not have filemaker

Hi. Thanks. Yes I'm new here. I'm probably intermediate with Filemaker. Patience I have, but further help I think I need. I currently have two tables which relate - "Clients" & "ContractsInvoices". "Clients" is just contact information; "ContractsInvoices" contains all the fields/data for making new ad contracts. Not sure I explained it in the best way, but Quantity isn't a field, since advertisers buy only one ad per contract. The "Total Net Rate" is a set list which doesn't have a basic formula of say a base equation made up of ad costs based on the 4 categories (National, Size, Color, Frequency). Rather the TNR is essentially a text list written by the ad manager of the costs of ads. I could probably determine what the actual calculations are she does to determine rates, but I don't think that's the best option as the changes are not always global, nor as I suspect based on any specific equation. Not sure if this changes it, but considering the list of rates is a set thing, a text list, can a relationship be made? The way I interpreted it was through if/then statements. Which rate I choose for a contract is based on those four (multiple) criteria. (National, Size, Color, Frequency). I read the question on multiple criteria from trishaa and that encouraged me this could be done, but in that example there appeared to be only two outcomes and i couldn't figure out how to apply a more complex (ie, 4) criteria determining lookups.  The only way I've been able to think about this is via if/then principles, but there is an easier way? A portion of the huge script for "Total Net Rate" I've built is this:
If (Rate Card = "National"  and Size = "Bleed Page"  and  Frequency = "1X"  and  Color = "4C"; "$10,800")  &
If (Rate Card = "National"  and Size = "Full Page"  and  Frequency = "1X"  and  Color = "4C"; "$10,800")  &
If (Rate Card = "National"  and Size = "2/3 Page"  and  Frequency = "1X"  and  Color = "4C"; "$8,095")  &
If (Rate Card = "National"  and Size = "1/2 Page"  and  Frequency = "1X"  and  Color = "4C"; "$6,680")  &

so that the value returned for Total Net Rate becomes conditional on these four values. It works well, but you can imagine the editing nightmare, not to mention the lengthy script. If you could provide an example that would be great because I just don't think I'm seeing it yet. The ideal solution is there are external text/Excel files that contain the values for the Rate Card - National, Regional, Local - so that the ad manager wouldn't have to get inside Filemaker to edit, but if that's a solution anything is good at this point.  Let me know if you need more information. Thanks so much.

Looks pretty complex at first glance, but I think it can be done, I've done much more complex things like quotations based on over 200 parameters.
First, if items description are too complex texts to be used for relationships, don't use that field to link it to the price list: use an ID field instead. fm8 allows to enters IDs while using a text popup to select the corresponding ID (I am not gonna say more, difficult to explain while a example will be obvious)
Then to calculate the price from a price list, either you can work out price using ratios (if size=2/3 page, then price = ref price * 0,75) or you mention all cases in the price list: in ths case, the price list would contain all combinations of the 4 parameters, ie N records where the amount depends on the amount of possible values for each of the 4 parameters:
so how many values for rate card? size (seems to be 4 here), frequency? and colour (probably 4 also here) This last solution is the one to use if the prices have no liogic way but a market price.
This is probably not very clear, but will become obvous with a example. Shall I do that from scratch with a dummy database, or do you want me to make it based on yr existing database, provided you post it for me (presumably without any confidential data like customers names, etc...?

Thanks so much.
i'm hoping the complexity is low.
I've uploaded the database I've made, as well as an Excel file containing the Local, Regional, National rates.
So the values for a Rate Card are as follows:

3 Rate Cards (Local, Regional, National)

Sizes: 14 options per rate card (though the sizes are the same across all three rate cards)

Frequency: 3 options (1X, 3X, 6X); the option to do 2X would be calculated as a quantity (2) of the 1X Frequency and that is something i've scripted to calculate on the invoice, simple enough.

Color or Black and white: 2 options, one or the other.

So I guess looking at this now, there are a total of something like 252 combinations.

The Filemaker database is available at
http://www.intertopia.org/upload/AD_DBASE_MZ.fp7

and the Excel file of Rate Card data is available at
http://www.intertopia.org/upload/RATE_CARDS.xls

Let me know if it's clear in the database. I can rework it if necessary.
Thanks again!
m

ok, got the files ok.
I'll work on it asap but this may be on saturday, not before.
it seems that yr prices grid can be simplified as the prices decletions look calculated with a fix ratio after the basic price, so we could keep only the basic prices in the price list and calculate the others.
tell me if this is an acceptable method or if I shall allow arbitrary prices for any combination?
fir instance, can we say that the 6x frequency is always 90% of frequency 1x for all items. it is true now, is it a coincidence or will it stay like this? if yes then we would store for instance the 1x price for all items and a ratio of 0.9 for 6x frequency. of course this ratio could be altered...

Saturday is totally good. I can't get back to the database until the weekend myself.
Excellent question and good to notice that. You're correct.

National 1X is the standard; 3X National is 5% off; 6X National is 10% off

Regional 1X is 30% off the National; 3X Regional is 5% off the 30% Regional; 6X Regional is 10% off the 30% Regional

Local 1X is 40% off the National; 3X Local is 5% off the 40% Local; 6X Local is 10% off the 40% Local

Amounts always round to the nearest $5.

Color is 50% over the black and white costs

I can see the opportunity in these numbers for calculating prices. Would constructing the equations for these calculations be better than a lookup table?

you still need a look up table, but it will contain the ratio and then do the calculations for you, so yes, it is simpler for the same result.

can this not be done in a different way?

four parameters in the cost calculation >>> four tables, each with one field for the value of the parameter (for examples the sizes table has got records with the values 8x10, letter, A4, whatever), and then one field for a cost multiplier which is different for each record of course

a fifth table where you make records for each quotation, with drop down value lists for each of the four parameters and a global field with the base cost rate for an advertisement. Relationships add the cost multipliers to four more fields in the quotation record.

a calculation field multiplies the base rate with the cost multipliers on each quotation record.

goodluck
rogier.

that exactly what I did if you check my file:
the 4 tables are format, frequency, colour, and rates, the 5th table is invoice_lines...

sorry, did not check your file.... lesouef the honour is yours of course. Hopefully the pricing is based on a linear function with simple multipliers. Otherwise mzurolo might be better off with an excel based solution that then still can export the resulting quotation to FMP. But in that case he could do nice graphs and such showing progressive discounts and a graphical representation of various optional scenarios and whatever.

I know the rebates are linear, I checked that with him before.
Even is the price list would end up with a price grid with all prices to be entered manually, I thing it is better to keep it in fm, always.
And if you desperately need graphs, you can export data just for graphics, or use the excellent x2max plug-in.
I already did some price lists using classic column rebates where the user can enter a ratio for a given range of qty per article. qties can be all different per article, and the price calculation is always the same; basic price * ratio determined according to qty range in the price list; and I always found this much easier than excel sheets where user are necessarily add a new column one day, ruining yr application imports!

when it's linear no graphs are needed. I guess everyone can imagine what a straight line looks like...
it's a bit off topic but I'm actually surprised to hear such prices go up linear. You'd expect a progressive discount.

rogier

not my decision! you're right most of the time, but here there are only 3 qties, all quite high, like 1000/2000/5000. and it is not necessarily linear to the qty, they have 1 rebate /qty, it does not mean the rebate is proportionnal to the qty, it is a rebate per qty range, so the resulting qty/rebate curve is a 3 slopes line.
I admit it would be easier to use a nice equation, but most of sales depts provide you with charts you can't convert to a reasonnable formula, so I use a price range system most of the time, and manage to get a monotonic curve with that to avoid the price of 999 pieces > 1000 pieces. every company has its own way I am afraid!

Thanks for all the feedback and examples. Extremely helpful. The setup is much more streamlined than any approach I would have taken. I do have some questions about the example: Instead of value lists the fields are generated via indexing. I've always constructed value lists, so what is the reason for using indexing instead? This may be a real Filemaker 101 question, but it's not a method I've used. How do the values become indexed in the first place? I have to add two other rate cards, which seems simple enough, adding a "2" and "3" to "rate_card" but where does the ratio to determine those second and third rates reside in this example? Sorry is these are obvious questions.
thanks.

> so what is the reason for using indexing instead
weel, this is the way to make it dynamic, and modiable by anybody
a static value list can only be changed by a dev guy in design mode while using a table field is done in normal mode by regular users (to be more accurate, it follows the account rights for this table, which is perfect, you can decide who has the right to change that table.
statis lists should be used for stuff you're sure it will never change after solution design; if you work for customers, they won't appreciate if you charge them to a name in a list!
> How do the values become indexed in the first place
look at the lists definitions
> where does the ratio to determine those second and third rates reside in this example
in the rate_card layout, add 2 new line for rate 2 and 3, then set the corrsponding ratio for this rate.
say you want to multiply by 1.5 for rate 2, insert a new record where rate=2 and ratio=1.5.
immediately, the popup in invoice lines will have new entries, and if you select rate 2, the price will be * 1.5
reload the example I modifed as such with 2 new rates:
http://www.lesouef.net/files/contracts_invoices.fp7

This has been great. I rebuilt your example myself so I could understand all the parts and it is very clear. I encountered two final questions though. The problem is that the portal will not display the fields when in browse mode. I see them in a "Find" but in browse with a new record added they are missing from the portal. Can't figure out why. Any clues? The price_ref field is part of the startup script and is, in theory, the modifier for how all the prices are calculated, correct? So the only adjustment in future rate increases would be to this single field/script correct? Many thanks again. This has been so helpful and educational for me. I can totally see the value in building this database this way as oppose to my initial lengthy scripts and value lists.

m

> but in browse with a new record added they are missing from the portal.
a new record created in which table?? if you create a new record which is not visible immediately in its portal, its because its field which is used to link it with the parent table is empty. It can be made automatically if the relationship "enable records creation via this link" setting is enabled. have a look in my example (I guess this pb is in yours, not in mine)
and yes for the price ref.
you don't have to ask the question at every startup if not necessary; it could also be stored in a table with 1 table/1 record, up to you, but for only one parameter, I tend to use a variable rather than a table, but the right way is to use a table in any other case.
glad it's been helpful.

Just when I thought I was clear, I encountered a problem in the calculation of rates. The figures for rate_card_ratios went in the wrong direction, instead of 1.3 and 1.8 it turns out to be .6 and .7. Once that fix is made the amounts begin to become correct. But they aren't all correct. In the rate card file set by the ad administrator, the rates sometimes round to 0, others to 5. But in the equation in the database the round is always to the nearest whole number it seems, so where a total should be $860 it is $861. I've reposted the database....

http://www.intertopia.org/upload

....I built from your example making the rate_card_ratio changes, but my question is, in the "Round" function is it possible to specify a direction which to round? Instead of everything always rounding to zero or the nearest whole number, is it capable to specify whether it rounds to 0 or 5 depending on the total? Since the equation is sound, I'm wondering now, if there was an arbitrariness in the rate setting whereby it doesn't follow the logic of the equation, but an administrator decision which direction to round up or down to?

Rounding can be removed or set to 1 or 2 decimals. You could of course make the rounding accuracy set by the admin. You'd need an extra field used to modify the price calculation. If you want a custom rounding method, you must define it yourself, but everything can be done, provided I understand what you need, and so far, I an not sure. I'll review yr excel file to see what to improve later one today

Now, I understand yr point, you need negative rounding, but fm can only do to nearest 0, the breakpoint being around .5
I'll try to make a custom function for this, rounding to nearest 0 or 5$

ok I did it this way:
rounded value, accordign to yr requests is:

cas (
mod (table::value;10) < 2,5 ; arrondi (table::value;-1);
mod (table::value;1) < 5 ; arrondi (table::value;-1) + 5 ;
mod (table::value;1) < 7,5 ; arrondi (table::value;-1) -5 ;
mod (table::value;10) >= 7,5 ; arrondi (table::value;-1) ;
0)

(sorry for the french copy/paste)

I have upload a modified version of yr file using this method. I could have done it in the price calculation, I found simpler to make a custom function if you'd need in another situation. function is called rounding05 ()

forgot:
http://www.lesouef.net/files/UPLOAD_DBASE.fp7

yes, I think I deserve this one!