Perform Calculation on Lookup

[Jarvis]

I have a cabinetshop.

My customers like to have their specifications in inches.

We, however, actually build the cabinets in millimeters.

My database has one table for CUSTOMER SPECIFICATIONS and one table for CABINET ENGINEERING. These tables are related with the match field PROJECT NUMBER.

Is there a way to start in the engineering table, LOOKUP the customer's preferred countertop height & automatically multiply the Inches X 25.4 millimeters?

For example: If CUSTOMER SPECIFICATIONS list countertop height at 36 inches, CABINET ENGINEERING reports this result as 914mm.

If possible, I would like to keep this as a simple lookup without additional calculation fields.

Any ideas?

Thanks,

Jarvis

Edited January 14, 2007 by Guest

[Lee Smith]

You need to have a relationship between the two TOs (based probably on the Project ID)

Make your MM a calculation field and based on your relationship. The calculation would then be something like this in the CABINET ENGINEERING for millimeters field.

CUSTOMER SPECIFICATIONS::Inches * 25.4

HTH

Lee

[Jarvis]

Thanks Lee!

I did as you suggested, with one slight change.

Instead of a calculation field I set this up as a number field,

with a calculated value in the auto-entry dialog box.

This gave me the durability of a lookup.......(if I ended up changing the counter height later, the early records stay intact)

It also gave me the flexibility to override the customer specification sheet if need should arise.

Thanks again. You saved me a lot of time!

Jarvis

[Lee Smith]

Excellent modification, and I should have suggested that to you too. Funny thing is, I usually choose that method if the version being used is 7, 8 or 8.5.

Glad I got you started though.

Lee