AnsweredAssumed Answered

Help me write this calculation....

Question asked by vicioustruth on Dec 15, 2009
Latest reply on Dec 16, 2009 by philmodjunk

Title

Help me write this calculation....

Post

I have two tables: Customer, and AddressLineItem. Customer has fields like CustomerID, FirstName, LastName, etc. AddressLineItem has fields like CustomerID (key), Address Type, Address Line 1, Address Line 2, City, State, Zipcode

 

A Customer may have multiple address records, or just one. For example:

 

Amy Smith

Mailing = 123 Happy St. Anytown NY 12345

 

Bob Barker

Mailing = 100 Maple Lane Boston MA 01234

Job Site = 5505 Oceanview Drive Hyannis MA 02673

 

I want to create a calculation that will do the following:

 

Look at all the address records for one customerID

Determine if an address record exists where type = Job Site - if one exists, set a variable named $JobSite with that address 

If not, set a variable named $JobSite using the address record where type = Mailing

 

So in Amy Smith's case, the $JobSite variable would = 123 Happy St.

 

In Bob Barker's case, the $JobSite variable would = 5505 Oceanview Drive

 

 

Any and all help is greatly appreciated.

 

(Filemaker 10 Advanced, WinXP)

Outcomes