3 Replies Latest reply on Mar 5, 2017 1:36 PM by philmodjunk

    Count Function with Global Field

    rbickings

      Good evening everyone,

      I have a church client for which i have been developing an FMP database and in which they desire a rather intricate attendance module.  This is not just keeping attendance, but reports with details like, how many parishioners have attended only 3 times in the past six months, and so on.  In order to develop this I've had to search for the fields used in a date range and count how many "X"'s are in each set of fields for each parishioner.

       

      So far I've been able to grab these field names from the date range and put them into a global field, using the GetFieldName Function, along with the Evaluate function and a calculation that produces the correct Table::Field name.  The problem is that when I use the Count function with that global field, it doesn't work.  For instance:

       

      Count ( gAttendanceHoldFields)  //which contains two table::Field names: Attendance Year 2017::_1_1_2017; Attendance Year 2017::_1_8_2017.

      The answer always comes back 1 for every parishioner.

       

      However, if I hard code it: Count ( Attendance Year 2017::_1_1_2017; Attendance Year 2017::_1_8_2017), I get the correct varied attendance values unique for each parishioner.

       

      Does anyone have any idea?  The global field contents and the hard code contents look exactly the same???

       

      Thank you,

      Rbickings

        • 1. Re: Count Function with Variables
          philmodjunk

          It sounds like you need to restructure your database so that each attendance "mark" is in it's own record. That would make counting values and reporting those counts much simpler.

           

          Count ( gAttentionHoldFields )

           

          Can only return either 1 or 0. If gAttentionHoldFields is empty, it returns 0. If it is not empty, it returns a 1. If you look up count in Help, this is exactly what it does, count the number of fields that are not empty. It has no way to evaluate the text in this field to get multiple values to count.

           

          Consider this expression instead:

           

          Evaluate ( "Count ( " & gAttentionHoldFields & ")" )

          • 2. Re: Count Function with Global Field
            rbickings

            Thanks my friend, that is exactly what I needed...works great!  BTW, I may not have been very clear in my explanation but each attendance mark is a field in each record.

             

            Blessings,

            RBickings

            • 3. Re: Count Function with Global Field
              philmodjunk

              If so, then you do not need to use such a complicated method. For one thing, the field name would be the same for every attendance mark and you could use:

              Count ( related table::field),

              ExecuteSQL, or

              a summary field

               

              to count your attendance.

               

              PS. I once set up an attendance solution for a children's ministry and did not need anything but th above to get my counts.