Calcuation IF or CASE? and nesting together

[rburns77]

I'm trying to create an invoice where different equipment ID's have different prices. I'm trying to put them all in one field, if it is possible. If someone can get me started on the equation, I hopefully can input all of my data.

My fields:

Equipment ID

CM

Expenditure

Equipment ID A8117 = .01

Equipment ID A8115 = .01

Equipment ID A7213 = .20

Equipment ID A7374 = .20

Equipment ID A7924 = .034

Equipment ID A6967 = .034

Most of my Equipment ID's = .034

The calcluation I am trying to do is:

expenditure = CM x .01 or .20 or .034

Is this even possible?

thanks for any help you can give!

[AudioFreak]

Your better off imo to have a seperate table for the prices and look them up with a relationship.

If you do it with a calculation when the price changes it will change all the old prices too. Which I'm guessing you would not want.

Michael

[rburns77]

I thought about that, but I'm not sure how to use that function. Can you help guide me through that?

[AudioFreak]

Seeing that you are using version 6 you will need to make a seperate file we'll call it Item Pricing.

Need 2 fields

Price

Equipment ID

Then in your main file set up a relationship using the Equipment ID for the key field.

Once you have that done you would then make the price field in your main file a lookup based on the relationship you just set up. This is done in the define fields area.

In short when you type in the Equipment ID number in the main file it will lookup the price you have in the Item Pricing file for that matching ID number.

I don't have version 6 loaded but can load it if you need more detail.

Edit: I added 2 sample files for you.

HTH

Michael

Price_Lookup.zip

Edited October 19, 2007 by Guest

[rburns77]

Alright - I think I understand what you are saying. I tried what I thought you meant, but it didn't work.

Frist, I created another database with 2 fields - Price and Equipment ID. I imported from an excel file all of my Equipment ID's and the prices that corralate to them.

Then in my main DB, I went to define relationships and opened my secondary DB. Then I matched the equipment ID in my main to the equipment ID in the seconarday.

Then I went to my price field in my main DB and did a lookup, using the secondary price.

But my price is not filled in.

Am I not doing this correctly?

thank you!

Becki

Edited October 22, 2007 by Guest

[rburns77]

oh! I figured it out. Thanks for your help!!