Use a Script, a Relationship , Both or what..?

[AbsoluteVoice4u]

I'm working with a rather large database with many fields.

My "price guide" field will contain either the letter A or B.

If "A" is in the "price guide" field then the following will be true for that record:

starter price = $1,000

maintain price = $1500

If "B is in the "price guide" field then the following will be true for thet record:

starter price = $1,250

maintain price = $1,750

The "price guide", "starter price" and the "maintain price" fields exist in all 23,000 records. At my discretion, I place either an A or a B in the "price guide" field. I want the two other fields in each record to populate automatically dependant upon the "price guide" field being either an A or a B...

What is the proper way to do this.......scripting?....a relationship?...what?. Is it so simple I do not see it?

[stanley]

AV4:

I might assume you want those prices to stick no matter what, in which case a simple Case[] calculation will do for either one.

Starter Price can be defined as a calculation field with the value:

Case[PriceGuide="A"; "1000";"1250"]

Maintain Price can then be:

Case[PriceGuide="A";"1500";"1750"]

Now, that will only work if you're not going to change from your A/B system. You could instead set up a relational system using a table (let's call it PriceGuide) which would have (at the moment) two records, one with a Key Field "A", the other with Key Field being "B". They'd have fields Starter Price and Maintain Price defined as in your post, and be related via your Price Guide fields. Now, in your main table, when you pick "A" or "B" in your price guide field, you could have the related Starter Price and Maintain Price fields in your layout, which would show the related data, as you wish. The thing about this system is that you could add "C", "D", etc, with very little difficulty.

Hope I've made sense.

-Stanley

[AbsoluteVoice4u]

You made perfect sense Stanley and now I have it working.......thank you so VERY much for taking the time to answer my cry for help. I used your second suggestion. It was aperfect fit for my application. This forum is amazing!

By the way.....GREAT avitar!!!