Jump to content

Modifiable alternatives to calculation field?

This topic is 6786 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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


Link to comment
Share on other sites

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.


Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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.


Link to comment
Share on other sites

This topic is 6786 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.