3 Replies Latest reply on Oct 28, 2008 4:09 PM by bcooney

    Summary field

    rowing190

      Title

      Summary field

      Post

      Please bear with me, as I am new to the Filemaker Pro database. I have set up a rather straightforward database with a company table and a contact table. The tables are related to one another by a company id field. Here is the problem. When querying the contact database - for example, for the number of marketing communications executives in Illinois - for a marketing campaign - I would like to know the number of companies as well as the number of contacts that match the find criteria. I have attempted to create a summary field in a trailing grand summary report, but the number that it returns is the same as the number of contacts. That is, there are say 901 contacts that meet the criteria, but they represent only say 303 companies. The grand total of companies in the trailing grand summary is also 901 since each contact has a company id. The sub-summary section will show the number of contacts for each company id - for example, company id x001 = 6 - but the grand total in the trailing grand summary totals 901 companies. Any idea how to create a grand total field for the number of companies that counts each unique company id only once and gives the correct total of 303 companies? I have been querying both the contact and the company databases in order to get the correct counts, but surely there is some way not to have to query the company database. Any help would be appreciated.

       

      Thank you. 

        • 1. Re: Summary field
          bcooney
             After you find in Contacts, use the gtrr script step to go to the related Companies (all found). That found count is the number of companies the contacts represent. You can do this in a new window, capture the count, store it in a global field and close the window. Once it's stored in a global field, you can display it on your report, if needed.
          • 2. Re: Summary field
            rowing190
               Thank you so much. I have figured out how to run the go to related script step and return the correct # of companies. I will work on how to capture the count and store it in a global field.
            • 3. Re: Summary field
              bcooney
                

              Great. Use the Set Field script step:

               

              (pseudo code)

              gtrr companies

              set field (Contacts::gFound, get (foundcount))