9 Replies Latest reply on May 21, 2014 1:01 PM by lijnbach

    Record count with condition

    lijnbach

      Title

      Record count with condition

      Post

           Hello everybody,

           In a lay out I would like to show the total number of records, but only the total number of records with a certain condition. 

           If a user performs a find, the lay out shows the records with the appropriate conditions. I can show the number of records with "Found count" function.

           But I also want to show the Total Number of records, that is also no problem. But, users can not remove records because of consistency reasons. They only can put records in archive.

           So, I want to show the total number of records that are not in archive. And I can't find a function for this one. I can solve this problem with calculated fields. But I was wondering if there is a function for this without creating additional calculated fields.

           Thanks in advance,

           Hans Lijnbach

        • 1. Re: Record count with condition
          philmcgeehan

               I assume you have a field that is changed when a record is archived.
               I'll call this field Status and assume it's contents is changed to Archived when that record is archived.

               Create a calculation field called IsArchived:

               if ( Status = "Archived" ; 1 )

               Then count IsArchived, this will be the number of Archived items which you can take away from the FoundCount.

          • 2. Re: Record count with condition
            philmodjunk

                 You can use a summary field to count or sum the Calculation field that Phil_1986 suggests, and then subtract the value of that field from your foundcount, but it sounds like this may be what you are already doing.

                 Do you need this count to be over all records in your table or just the records currently in your found set?

                 ExecuteSQL for users of FileMaker 12 or newer could also return a count of non archived records, but this, unless you got really creative with how users perform finds, would be a count based on all records in  your table instead of all records in your found set.

            • 3. Re: Record count with condition
              lijnbach

                   O.K. Thanks Phil,

                   I don't have a problem how to create calculation fields, but I was hoping there was a direct function or expression for this one. (I am working with the Dutch version and I can't always find the appropriate translations).

                   You helped me with this one, so now I know for sure I have to create calculated fields. Just a little more work.

                   Thanks for your effort and time,

                   Hans Lijnbach

              • 4. Re: Record count with condition
                lijnbach

                     Hai Phil,

                     Now I have to create calculated fields, It is a good idea to add an extra summary field to show the number found directly.

                     What I am doing, I show the numbers of records found in "perform find" to the user. That's no problem.

                     But I also show them the total number of records. But in my case the total number of records, is not the real number of records, but the number of records that are "not in archive"

                     Thans for your time and effort,

                     Hans Lijnbach

                • 5. Re: Record count with condition
                  philmodjunk

                       If you want the total number of records in the table that do not have the text "archived" in a field named ArchiveStatus, ExecuteSQL can do this much more simply:

                       ExecuteSQL (
                       "SELECT Count ( * ) FROM \"YourTable\" WHERE \"YourTable\".ArchiveStatus <> 'archived' "
                       ; "" ; "" )

                  • 6. Re: Record count with condition
                    lijnbach

                         Hai Phil,

                         You are right, I want all the records in the table that have (Field) "Archive = No". (For the user the real number of total records).

                         The Execute SQL seams easier and less work. But how to use the execute SQL? I know the Execute SQL is a script step - I used it before - but how keep the field with the result of the script step constantly updated. Every time a record is added, the field has to be updated. Create the result in a record field?

                         Thanks in advance,

                         Hans

                          

                          

                    • 7. Re: Record count with condition
                      philmodjunk

                           This is not the Execute SQL script step, it's the ExecuteSQL function.

                           What I've posted would be put in side the Specify Calculation dialog for a unstored calculation field.

                      • 8. Re: Record count with condition
                        lijnbach

                             This is clear Phil,

                             I misunderstood that, the SQL function works fine, and easier then een calculation and summary field, and less work.

                             Thanks again for helping me out.

                             Hans Lijnbach.

                        • 9. Re: Record count with condition
                          lijnbach

                               Should use the SQL function more, works great Phil.

                               Hans