AnsweredAssumed Answered

multi-criteria calculation problem - filemaker 5

Question asked by agreer on Dec 31, 2008
Latest reply on Jan 8, 2009 by TSGal

Title

multi-criteria calculation problem - filemaker 5 & 5.5

Post

I have been struggling for several weeks (off & on) with this problem:

 

I have 4 tables: Press Monkey, Customers, Products, and Special Plates. From Press Monkey you select which customer wants which product, and then 3 fields automatically fill out the size, which press, and whether or not the order requires a special plate (or it's supposed to).

 

My trouble is automatically filling in the 3 fields. When the orderer fills in the customer and the product number, I need FileMaker to search the Special Plates database for a record that matches both criteria (customer and product #), and if it finds it, fills in the appropriate rest of the information. If it doesn't find it, then it should use the information from the Products database.

 

Here's what I have, which only *sometimes* works - the size, press, and special plate fields (all type text) are calculations:

 

Size:

If(Exact(Trim(Product Number),Trim(Special Plates::Product Number)) and Exact(Trim(Customer),Trim(Special Plates::Customer)), Special Plates::Size, Products::Size)

 

Press:

If(Exact(Trim(Product Number),Trim(Special Plates::Product Number)) and Exact(Trim(Customer),Trim(Special Plates::Customer)), Special Plates::Press, Products::Press)

 

Special Plate:

If(Exact(Trim(Product Number),Trim(Special Plates::Product Number)) and Exact(Trim(Customer),Trim(Special Plates::Customer)), "Yes", "No")

 

All the fields are of text type and all the calculations are text results, and no two customers have the same name. Customers can have more than one special plate and one product can have more than one special plate; each record is unique, but no specific field is unique.

 

 If anyone has a solution or partial solution, I'm all ears!

 

April

Outcomes