6 Replies Latest reply on Sep 8, 2009 5:03 PM by philmodjunk

    keeping specific totals independent of a find / sort

    ijontichy

      Title

      keeping specific totals independent of a find / sort

      Post

      This community was kind enough to lead me to the Get(totalrecordcount) function, but I'm sad to say I have to ask my next question of a similar nature.

       

      I have some calculations that tally various types of records in a database.    For example, one tally counts records added this week vs before this week, etc.   

       

      I need a way of keeping those counts constant independent of a find that reduces the number of records.

       

      So in a perfect world, there would be a Get (totalrecordcount of records listed as ACTIVE)  or Get(totalrecordcount of records listed as INACTIVE)  or listed as blue, pink--name your qualifier.   The main thing is these would ALWAYS count the total number of records with those attributes, independent of how the records have been sorted so they could always be tallied.

       

      It's possible this is too complicated because that date is an important part of what I'm tracking.    So not only would it be a totalrecordcount of ACTIVE records (for example), but I'd like to know the totalrecordcount of ACTIVE records prior to this week.

       

      Does that make sense?

       

      e.

        • 1. Re: keeping specific totals independent of a find / sort
          davidanders
            

          If you did a Find for Active and a second find (Omit) for the last seven days 

          this would give you the answer you wish?

          The find could be done at startup, or would it have to be uptodate as of right now? 

          • 2. Re: keeping specific totals independent of a find / sort
            philmodjunk
              

            If you can establish a relationship that connects the group of records you want to count, you can also use the count function to count the number of related records. This avoids the need for special scripting, but instead requires setting up some keys and relationships.

             

            Example: If you have a field that auto enters the creation date called "DateCreated" then the date calculation

             

            "Weekof" ; DateCreated - DayofWeek(DateCreated) + 1

             

            Will return the Sunday date of that week for all records created on that week.

             

            Now set up an unstored calculation: "ThisWeek"; Get ( currentdate ) - dayofweek ( get ( currentdate ) ) + 1 to produce a matching date for the current week.

             

            Now create the relationship Yourtable :: ThisWeek > Yourtable 2 :: Weekof

             

            Third calc field: Count ( Yourtable 2 :: DateCreated ) will return the total number of records created prior to this week.

             

            This technique borrows a calc shared by Comment in another thread.

             

            You can do variations of the above to count various other groups of records.

            • 3. Re: keeping specific totals independent of a find / sort
              ijontichy
                

              I get this, at least I think I do.   By using a relationship via a copy of the table the number of records won't change if I do a find that reduces the number of records in primary table?   Right?

               

               

              • 4. Re: keeping specific totals independent of a find / sort
                philmodjunk
                  

                It's not a "copy of a table" though it may look that way.

                 

                When you build relationships in Filemaker each "box" in the graph is really a "Table Occurrence" (TO). Each TO may point to any table you've defined in the file or any file to which you've established an external reference.

                 

                In my example the "copy" is really just a second TO pointing to the same table as your original table. This type of "self join" can be created on the graph simply by dragging from one field in the TO box to outside the borders and then back to a field inside the same box. Filemaker will pop up a dialog for an "Instance" of the current table where you can give this TO a name.

                • 5. Re: keeping specific totals independent of a find / sort
                  ijontichy
                    

                  I'm clear on that even if I did not use the proper terminology.    BUT...will it keep the count constant even if I've done a find that reduces the number of records to a subset of their totals?

                   

                   

                  • 6. Re: keeping specific totals independent of a find / sort
                    philmodjunk
                       As long as you have at least one record in your found set (so the relationship is valid), the totals will not be affected by the current number in the found set.