3 Replies Latest reply on Dec 16, 2009 8:29 AM by philmodjunk

    Help me write this calculation....



      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)

        • 1. Re: Help me write this calculation....

          Define a text calculation in Customer table, JSKey, that returns the text "Job Site"

          Define a second relationship between Customer and AddressLineItem:

          Customer::CustomerID = AddressLineItem 2::CustomerID AND

          Customer::JSKey = AddressLineItem 2::Address Type


          You'll need to create a second table occurrence of AddressLineItem to do this.


          Now the calculation can be defined as:

          AddressLineItem 2::Address Line 1


          You can define this as a calculation field or put it in a script step:

          Set Variable[$JobSite; Value: AddressLineItem 2::Address Line 1 ]


          Many folks get confused over the terms Table and Table Occurrence. To learn more, click the following link:

          Table vs. Table Occurrence (Tutorial)

          • 2. Re: Help me write this calculation....

            Hey Phil,


            Thanks for the reply. I understand the difference between Tables and TO, thankfully. 


            I'm looking at your suggestion, but I dont see how this will deal with those customers who only have one address that is of type = "Mailing"...


            Maybe I'm just blind... did I miss something?


            The whole point of this is that when a salesperson creates a Proposal for a customer, the Proposal needs to show both a mailing and job site address. In those customers where the mailing address IS the job site address, then the Proposal needs to show the same address in both mailing and job site. 

            • 3. Re: Help me write this calculation....

              Sorry, I missed that detail.


              In that case, I'd specify a sort order on the relationship that lists the addresslines in this order:


              Job Site



              If these are you're only two values, simply specifying an alphabetical sort order based on this field will do the trick. (This is set in the Edit Relationship Dialog.)


              Now a reference to your existing relationship will do the job:

              Set Variable[$JobSite; Value: AddressLineItem::Address Line 1 ]


              This works because a reference to a child table with more than one matching record will return the "first" matching record and the relationship's sort order can be used to determine what record is "first".