3 Replies Latest reply on Aug 26, 2012 6:12 AM by Oliver_Reid

    Count Function (FMP12)

    developertech

      Hello everyone,

       

      In FMP12 I have a solution which includes a Clients table with 2 related tables - one for email addrresses, and one for physical addresses. Each of the related tables appears in its own portal (with each relationship allowing the creation of related records). In each portal you indicate whether the email address or the physical address you have entered is for Home, Work etc.

       

      In the Clients table I wish to create a calculation field that compiles, based on what email and physical addresses are entered, a list of possible choices for the client's preferred method of correspondence. To be clear, if there was a home email address for the client and a work physical address, the field would provide two choices for the preferred correspondence address, "Home Email" or "Work Address".

       

      To compile the calculation, I have created a group of relationships to enable me to count the number of each type of address by using the Count function on the ID field of each related table. So the calculation is basically:

       

      If Count(ID of home email addresses table) = 1, "Home Email") & "returnsymbol" &

      If Count(ID of work email addresses table) = 1, "Work Email") & "returnsymbol" &

      If Count(ID of home physical addresses table) = 1, "Home Address") & "returnsymbol" &

      If Count(ID of work physical addresses table) = 1, "Work Address")

       

      with the result of the calculation set to text.

       

      Well that's almost the truth - what I actually did, for reasons I will outline below, is I simplified the calculation above to refer to 4 other calculation fields which each are a Count (related table ID) calculation. So its

       

      If FieldwhichCountsHomeEmailAddresses = 1, "Home Email") & "returnsymbol" &

      If FieldwhichCountsWorkEmailAddresses = 1, "Work Email") & "returnsymbol" &

      If FieldwhichCountsHomePhysicalAddresses = 1, "Home Address") & "returnsymbol" &

      If FieldwhichCountsWorkPhysicalAddresses = 1, "Work Address")

       

      The calculation is of course unstored because it includes related fields.

       

      Now here is the problem. If I, for example, enter a home email address in the appropriate portal, FieldwhichCountsHomeEmailAddresses will update as I exit the new portal record (I don't have to exit the primary record for it to update). But the main calculation explained above , which DIRECTLY REFERS TO THE FIELD WHICH UPDATES, will not cascade update until I exit the whole record. My question is why?

       

      Is there a better way in which I could continue to handle contact information in these related tables, to be able to compile a preferred address field, and to not have to commit the record for the appropriate values to appear in the field.

       

      Any help appreciated.

       

      PS I realise that for the system above to work it would require me to check that multiple addresses of any given type are entered.

      PPS Even if it does not turn out to be related to the issue outlined above, if anyone can point me to information about what script steps and what functions work and what don't when working with uncommitted records (whether the primary or related records) I would appreciate it.

        • 1. Re: Count Function (FMP12)

          This should be able to be simplified to something like:

           

          ExecuteSQL ( "SELECT distinct address FROM email WHERE fkID=? UNION SELECT distinct address FROM physical WHERE fkID=?"; "";""; Clients::Client_ID; Clients::Client_ID  )

          • 2. Re: Count Function (FMP12)
            developertech

            This was my bad.

             

            I made the mistake of thinking that uncommitted records in one portal (all email addresses for a contact) would exist, before being committed, in a similar relationship (a relationship designed to check if just a home email address existed). But of course they do not until they are committed.

             

            Thanks mr_Vodka for your alternative idea.

            • 3. Re: Count Function (FMP12)
              Oliver_Reid

              How about in each address table having a calc "Address_Type" which caluates and stores th lable you wan tto use for that address e.g. "Home Email"

               

              Then use

               

              list(list(emailaddresses::address_type);list(snailmaildresses::address_type))

               

              List() simply drops null values so you won't need the IF's