Modifiable alternatives to calculation field?

[barry_c]

Hello all;

I have two tables in a database, ORDERS and CLIENTS.

Each record in the ORDERS table is unique and specific to one of 18 publications in which our clients buy advertising space. Each client is entitled to a different frequency discount level which may be different in each publication depending on how many ads the client promises to buy in a given 12-month period.

Now for example, when I am creating the order for client A in publication 1, I would like to have the correct discount rate automatically entered.

I created a third table called DISCOUNTS with the fields PUB_NAME; CLIENT_NAME; and DISCOUNT_LEVEL. In the "Discount" field on ORDERS I used the following calculation:

Case (not (IsEmpty(Publication) or IsEmpty(Client)); DISCOUNTS::DISCOUNT_LEVEL)

This works but is not 100% satisfactory for the following reasons:

1. It's a lot of extra work to keep up with the new DISCOUNTS table. If a client buys all 18 pubs that's 18 more records to deal with. I'd like to just be able to put the info (publication name, discount level) in the CLIENTS table, and feed that to the ORDERS table. Any alternatives out there?

2. The calculated "Discounts" field on ORDERS is not modifiable after the fact. On rare occasions a frequency discount is not applicable to a certain order, so it would be nice to have some override capability.

Thanks for any suggestions you may have.

best regards

barry

[barry_c]

Ok, I figured out how to make "Discounts" modifiable, by formatting it as a number field then choosing an auto enter caluclation.

But that raises another question:

Any idea why the calculation

Case (not (IsEmpty(Publication) or IsEmpty(Color_or_BW) or IsEmpty(Day_of_week_Insertion) or IsEmpty(Format)); RATES::GROSS_RATE)

would work perfectly fine as an Unstored calculation, but when I try to change it to an auto-enter calculation it doesn't return any result? I've switched other calculations around like that and they all seem to work fine.

thanks

[-Queue-]

Is the calculation listed before or after the fields it references when the field definitions are sorted in creation order?

[barry_c]

It's kind of in the middle. It comes after (Publication) and (Day_of_week_insertion) but before (Color_or_BW) and (Format).

[-Queue-]

Are either Color_or_BW or Format calculations?

Try duplicating the auto-enter field and see if the new one works correctly.

[barry_c]

BINGO.

That did it, thanks alot. I didn't realize the creation order could have that kind of effect.

So now (...shameless hint...) still working on # 1 above...

[-Queue-]

From what I've experienced, auto-enter calcs and lookups occur in the order in which the fields are created, which does make some sense. It is definitely an issue to consider when creating audit log solutions or auto-enter calcs that are based on other calcs, lookups, etc.

Regarding #1, I think it may be best for you to add a Publications table and a join table from Clients to Publications, with each record containing the discount for that particular Client/Publication combination. If you could stuff and attach a stripped-down copy of your file, we could review its structure and offer more specific assistance.

[barry_c]

Great, thanks alot. It is attached. Excuse the mess. Besides #1 above regarding the Discounts, I am also struggling with a way to get both the discounts and the rates to obey their validity dates. For example, if an insertion is scheduled for Oct 15 2004, it would select that rate, rather than the rate that expired on Oct 14 2004.

(BTW, the unique IDs are not numerical because they match what we use in a mainframe database, and I hope someday to introduce them to each other)

Thanks for any assitance or guidance anyone cares to provide.

orders.fp7.zip