4 Replies Latest reply on Dec 20, 2010 10:38 AM by Frinholp

    Omitting Archived Records From Total Count

    Frinholp

      Title

      Omitting Archived Records From Total Count

      Post

      Hi all

      In my solution I send all records to archive rather than delete. This is acheived by setting a record's status using an 'Archived' field who's value is a boolean.

      I currently show the record number, total of found set, and records in total for all my tables using merge fields on my layouts. I use three auto-calculated fields in each table which calculate the aforementioned values.

      Get ( RecordNumber )   

      Get ( FoundCount )

      Get ( TotalRecordCount )

      How would I go about subtracting the number of archived records from the total?

      Thanks in advance

      Lee

        • 1. Re: Omitting Archived Records From Total Count
          philmodjunk

          Define a relationship that matches from any record in your table to only those where the Archived field's value is True. Then

          Get ( TotalRecordCount ) - Count ( ArchivedTO::Archived ) will serve.

          YourTable::cArchivedKey = ArchivedTO::Archived

          Where cArchivedKey is a calculation field that always evaluates to True and ArchivedTO is a new table occurrence of YourTable.

          If you find that this slows down your layout refresh rate unacceptably, you may want to script your "archive" process to include a step that increments a counter field to use in place of this Count function.

          • 2. Re: Omitting Archived Records From Total Count
            Frinholp

            Cheers Phil

            I can't seem to get the above working.

            This is what I have done so far:

            Added a Calculation Field to Clients table called cArchivedKey which =TRUE

            Created a TO of Clients called ClientArchive

            Joined Clients TO to ClientArchive TO using an 'equals' join, joining CArchivedKey field in Client TO to Archived field in ClientArchive TO.

            Added a Calculation Field to Clients table called cRecordCount which =Get ( TotalRecordCount ) - Count ( ClientArchive::Archived )

            Set Archived field to 1 / TRUE

            Outputted contents of cRecordCount which still shows overall total records.

            Is there something you can see that I'm missing?

            Thanks

            Lee

             

            • 3. Re: Omitting Archived Records From Total Count
              philmodjunk

              It worked for me: http://www.4shared.com/file/KNI0AA4F/ArchiveCount.html 

              Compare that file to yours and see if you can spot any differences. One thing to check is to make sure that any code that sets archived to True is not setting it to "True". (Quoting the word treats it as text instead of a constant that stands for the number 1.)

              • 4. Re: Omitting Archived Records From Total Count
                Frinholp

                Sorry Phil

                I apologise for jumping to conclusions. My mistake, I named two field similar and placed wrong one on layout.

                Thanks for the solution

                Lee