get price into field based on info in other fields

[barry_c]

Hi, I've just started with Filemaker 7 and developed a useful database at work. Now I would like to improve it but am breaking my head over how to do it. I haven't yet come across a similar topic in books or this forum. Thanks in advance for any help.

I work for a company that buys ad space in 18 different publications.

My current table is called "INSERTION ORDERS". Each record in this table is 1 order (like a purchase order) for 1 ad in 1 publication. The order includes information which determines the Gross Rate, such as:

"PUBlication NAME", the "DAY" the ad is to run, the ad "SIZE" (Full page, half page, etc) and if the ad is "4 COLOR" or "BLACK and WHITE".

Now I would like to have the Gross Rate field automatically filled in upon completion of the 5 determining fields listed above. Currently AD SIZE is a pull-down menu with 4 options. "4 COLOR" and "BLACK and WHITE" are radio buttons.

My questions are:

1) What is the best way to set up the new RATES table which this field would access?

It seems a bit much to make a field for each rate, because then each Publication would have (potentially) 4 (SIZES) x 2 ( BW or Color) x 5 (Days of Week) = 40 different rates. Bleah. Is there a better way?

(Note: For the days of the week the prices usually only vary by one day: For example, one publication might be more expensive on Monday but cost the same the rest of the week, another might only be available on Wednesdays (a weekly); one might not publish at all on Fridays, etc.)

2) What is the best way to get the correct rate from the RATES table into the Gross Rate field?

Thanks alot for any advice.

Barry

[Chuck]

Create a Rates table with fields for the publication name, day of week, size and color. Create a relationship from your insertion orders table to the Rates table matching all four of these fields. Basically, you'll have, I think, 40 records (instead of 40 fields) for each of the publications. Once the relationship is set up with those four match fields, you can either bring in the rate by using a calculation or lookup in the insertion orders table or by directly displaying the related field on the layout.

Chuck

[barry_c]

Ah, I see, so each rate would be a unique record. I guess that makes sense, since that table is called RATES.

But it seems this would generate alot of records which are redundant except for one field, DAY OF WEEK. For example, if PUBL 1 had the same BW price for every day of the week, I would still need five records.

Maybe in those cases I could have a field called something like "MONDAYTHRUFRIDAY" and use that as the default with the LookupNext Function?

Thanks alot Chuck for your time, this definitely points me in a good direction, I will play around with it this weekend...

Barry

[barry_c]

OK, so now I can get those 5 fields to come in from RATES to INSERTIONORDERS using Lookups and the RatesID key field. For example, I type in "01" in the InsertionOrderID field, and the four determinants and the rate all show up in their proper fields.

But I am having trouble with multiple match fields. Are you saying if they are all 4 related to each other, and I put in for example PUB A, Monday, Full Page, Color, then the Rate should just pop into the "gross rate" field with a lookup? I tried that and couldn't get it to work.

[barry_c]

I couldn't get the multiple match fields to work on my DBs, so I started two new ones and it works. Strange. Now the problem I have is that the lookups seem to stop at the first matching record, and don't wait for all the criteria to be complete before coming up with the rate. I put in the PUB name, and it just fills in the rest, SIZE, DAY, COLOR, and RATE with the first record listed that matches.

How can I get the lookup in the 5th field to wait until the 4th field is complete before looking up the rate?

[-Queue-]

Case( not (IsEmpty(field1) or IsEmpty(field2) or IsEmpty(field3) or IsEmpty(field4)); keyfield )

where keyfield is the lookup's key field. Change the lookup to be based on the calculated key field.

[barry_c]

Thanks alot Queue!

I couldn't really figure out what you meant by "Change the lookup to be based on the calculated key field" - I can't find how to make a field both calculated and a lookup.

As it stands now, though, it works without a lookup: I have the following in the calc field of GROSSRATES in the INSERTION ORDERS table:

Case( not (IsEmpty(PUBNAME) or IsEmpty(DAYOFWEEK) or IsEmpty(ADSIZE) or IsEmpty(TYPE)); RATES::rGROSSRATE )

All 5 fields are related to their counterparts in the other table. I am using multi-key fields to reduce the records in the RATES table. For example, if a pub has a unique rate only on Monday, then in the next record I put the rest of the days of the week separated by returns, and that seems to do the trick.

Thanks for letting me know if you see any potential problems with this, or if there is a better way to do it with lookups.

Otherwise, thanks very much to both of you for your help on this. So far my experience with FM Pro is one of the most rewarding I've ever had with a software program!

Kind regards

Barry

[bruceR]

"I couldn't really figure out what you meant by "Change the lookup to be based on the calculated key field" - I can't find how to make a field both calculated and a lookup."

Then you need to work on your understanding of lookups, becasue that is not how they work. A lookup system has a trigger field and one or more fields that we could call target fields. For instance Description and Unit Price are commonly set up as lookup when the Product ID is entered. Queue is talking about the trigger field.

[barry_c]

On my rates table I also added date fields "valid from" and "valid thru". Unfortunately now I don't know what to do with them to check that the calculation references the correct record.

I thought I could use some sort of "valid from"? "date of insertion" ? "valid thru" construction?

But now I'm thinking maybe I will have to use some type of script and sort the rates table records first...

There must be a common and easy way to access correct prices according to the date of the purchase, no?