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:
Mailing = 123 Happy St. Anytown NY 12345
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)