3 Replies Latest reply on Jun 29, 2015 3:31 PM by philmodjunk

    Counting Records



      Counting Records




      I am developing a solution for my team of managers and have run into a snag that I cannot seem to overcome.  What I need to accomplish is to create a layout on which it displays a list of all Employees on the left, and EntryTypes across the top, with a count of the number of records found of each type in a specified Fiscal Period.


      I have a table called Coaching, with the following fields:
      • Serail (Text, Auto-Enter UUID)
      • ReferencedEmployee (Text)
      • Date (Date)
      • FiscalPeriod (Text)
      • EntryType (Text)
      • Logged (Number) 

      Table:  Personnel
      • Serial (Text, Auto-Enter UUID)
      • FirstName
      • LastName
      • PreferredName
      • CompleteName

      An approximation of what I'm looking to do: 

      I have a feeling that this will involve use of summaries and sub summaries, however this is an aspect of FM I'm not well versed in.  Any insight or direction would be greatly appreciated.  Thanks! 

        • 1. Re: Counting Records

          What you have described is a "cross tab" or "pivot table" report. These are possible in FileMaker, but take a bit of work to set up as there isn't a built in tool for generating the report (or at least the underlying query) found in some other database systems.

          From what I see here, it appears that you have this relationship:

          Personnel----<Coaching    ( ---< means "one to many")
          Personnel::Serial = Coaching::ReferencedEmployee

          There is more than one way to set this up. The simplest to describe in a forum like this is probably to use filtered one row portals and summary fields.

          1) Define a summary field in coaching, sRecordCount as the "count of" ReferencedEmployee (any field that is never empty works)
          Define cFiscalPeriod as a calculation field that returns the correct matching text for fiscal period from your date field. (I don't know your fiscal year and am not sure what "09" means in your example, the 9th month of the fiscal year or ?? ) It needs to return exactly the same text as that shown in your example.

          2) Define a global field, gFIscalPeriod, in one of your tables. (Since it will have global storage specified, it can be defined in any table in your file and it will still work. I'll put it in Personnel for this example.)

          3) Set up a list view layout based on Personnel. In the body layout part, put CompleteName on the layout as your first column of data. Add a one row portal to Coaching as your 2nd column. Specify a 1 row portal and give it this portal filter expression:

          Coaching::EntryType = "One-on-One" And Personnel::gFiscalPeriod = Coaching::cFiscalPeriod 

          Put sRecordCount into the portal row.

          4) Once you have your first portal correctly sized and set up to look like you want and you see the correct total in it, return to layout mode and make copies of it. (option or ctrl drag or copy/paste). Open Portal set up and change the portal filter expression to refer to a different entry type value.. Repeat this until you have the additional columns that you need.

          5) Add a new relationship to Manage | Database using a new table occurrence of Coaching:

          Personnel::anyfield X Coaching|All::anyField (Double click the relationship line in order to change = to X.)

          In this relationship, you can literally use any field in the table as a match field on both sides of this relationship. Add one more set of single row portals to a trailing grand summary or the footer that show related records from Coaching|All and that use same summary field, but from coaching|all. Keep the same portal filters as before. These will give you your column totals.

          Note that it's also possible to produce this report using a series of calculation fields, each with a different SQL query as a parameter of the ExecuteSQL function. (Requires FMP 12 or later.)

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Counting Records

            Thank you for the direction, Phil!  At first glance, I'm not sure this will work, as I am already using a portal on form view layout. The actual layout I'm working with is here:

            What I have done is create a table called ProfileAndSettings.  This table houses all of the information for the user of the database, as well as their preferences.  All tables in the solution have a global field called magicKey, with a value of 1.  All tables are related using this field.  

            My layouts are based on ProfileAndSettings, and use portals to pull related information in from various other tables, including Personnel and Logs.  The purpose of this is to enable the menubar seen on the left, and to enable a consistent one-window experience.  My intention is to have any secondary windows open strictly for creation of new records.  

            I can use the method you describe above to accomplish generating the report I'm looking for, however it would require a new window be opened (unless my thinking is off?).  Do you have any other ideas, or is my best bet to suck it up and make this report display in a separate window?  

            • 3. Re: Counting Records

              You would need to use a layout that does not use a portal like that. Set up a list view layout and put the one row portals for your columns of data inside the body layout part.