AnsweredAssumed Answered

Help me write this calculation....

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


Help me write this calculation....


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)