4 Replies Latest reply on Jan 30, 2011 1:09 PM by Frinholp

    Archiving data and obtaining 'live' record count using a calculation field

    Frinholp

      Title

      Archiving data and obtaining 'live' record count using a calculation field

      Post

      Hi all

      I am experiencing trouble when using a calculation field to calculate the total number of 'live' in my database. Everything works fine until I archive all records in a table so as there are no 'live' records in the system.

      The scenario is as follows:

      I have created a table that has a field called isArcived which holds a boolean value that is true if the record has been archived or false if the record is live. I have created a calculation field called cArchiveKey which is always set to True.

      I have created a TOC of my table called Archive. The Archive TOC has a (self join) relationship with the main table TOC so that

      MainTable::cArchiveKey = Archive::isArchived

      I have defined another calculation field that calculates the number of records that are archived

      cLiveRecords = Get ( TotalRecordCount) - Count (Archive::isArchived)

      All works correctly until I archive all records in the table. When I archive all records cLiveRecords has the value of the total records in the table even though they are all archived i.e Count(Archive::isArchived) = 0 so cLiveRecords = Get(TotalRecordCount).

      What i was expecting is Get ( TotalRecordCount) = Count (Archive::isArchived) returning a value of 0.

      As I say, all is good until ALL records are archived i.e all records have isArchived set to True

      Anyone any ideas?

      Thanks in advance

      Lee

        • 1. Re: Archiving data and obtaining 'live' record count using a calculation field
          ryan

          If i understand your scenario correctly, i think the problem lies within the "Count ( Archive::isArchived )" part of your calculation.

          to achieve the expected "0" result you are looking for, create a calculation field in your Archive TOC using the Get ( TotalRecordCount) function.  for my purposes, i'll call it "cArchiveRecords"

          then have the cLiveRecords calculation = " Get (TotalRecordCount ) - Archive::cArchiveRecords"

          • 2. Re: Archiving data and obtaining 'live' record count using a calculation field
            Frinholp

            Thanks for the reply rDowler

            The results are now worse than before.

            Am I missing something here?

            cArchiveKey is set to True

            isArchived is set to False if 'live' and True if sent to archive.

            Relationship is MainTable::ArchiveKey = ArchiveTOC::isArchived

            I have tried evaluating cArchivedRecords in context of both MainTable and ArchivedTOC. Results of testing are shown for each context below.

            I will show my structure in bold and results from data viewer underneath. I will create 3 records and test all live, 2 live and 1 archived, 1 live and 2 archived and all archived.

            cArchiveRecords  Calculation from Archive, = Get(TotalRecordCount)

            3 records live -> MainTable::cArchiveRecords -> value = 2

            3 records live -> Archive::cArchiveRecords -> value = no output

            2 records live -> MainTable::cArchiveRecords -> value = 2

            2 records live -> Archive::cArchiveRecords -> value = 2

            1 records live -> MainTable::cArchiveRecords -> value = 2

            1 records live -> Archive::cArchiveRecords -> value = 2

            0 records live -> MainTable::cArchiveRecords -> value = no output

            0 records live -> Archive::cArchiveRecords -> value = no output

            cArchiveRecords  Calculation from MainTable, = Get(TotalRecordCount)

            3 records live -> MainTable::cArchiveRecords -> value = 3

            3 records live -> Archive::cArchiveRecords -> value = no output

            2 records live -> MainTable::cArchiveRecords -> value = 3

            2 records live -> Archive::cArchiveRecords -> value = 3

            1 records live -> MainTable::cArchiveRecords -> value = 3

            1 records live -> Archive::cArchiveRecords -> value = 3

            0 records live -> MainTable::cArchiveRecords -> value = no output

            0 records live -> Archive::cArchiveRecords -> value = no output

            Thanks again

            Lee

            • 3. Re: Archiving data and obtaining 'live' record count using a calculation field
              Frinholp

              I have created a table that has a field called isArcived which holds a  boolean value that is true if the record has been archived or false if  the record is live. I have created a calculation field called  cArchiveKey which is always set to True.

              I have created a TOC of my table called Archive. The Archive TOC has a (self join) relationship with the main table TOC so that

              MainTable::cArchiveKey = Archive::isArchived

              I have defined another calculation field that calculates the number of records that are archived

              cLiveRecords = Get ( TotalRecordCount) - Count (Archive::isArchived)

              All works correctly until I archive all records in the table. When I  archive all records cLiveRecords has the value of the total records in  the table even though they are all archived i.e  Count(Archive::isArchived) = 0 so cLiveRecords = Get(TotalRecordCount).

              Using the original scenario above which all calculated fields are evaluated in the context of main table TOC (not the TOC of istself which is the Archive TOC) I performed a watch on Count (Archive::isArchived).

              As previously stated if no records have isArchived set to True the value of Count (Archive::isArchived) is 0. If one record's isArchived is set to true the value of Count (Archive::isArchived) is 1 as expected. If two record's isArchived is set to true the value of Count (Archive::isArchived) is 2 and so forth until all record's isArchived is set to true the value jumps to zero. This is when using a layout that shows 'live' records. This layout is based on the MainTable TOC.

              However if I display a layout that shows the archived records rather than 'live' records, the  value of Count (Archive::isArchived) when all record's isArchived is set to true returns the expected value i.e all records in the table. I find this strange behaviour as this layout is also based on the MainTable TOC.

              Why would there be two different results even though both layouts are based on the same TOC?

              I can't for the life of me figure this one out.

              Any Ideas?

              Again thanks in advance

              • 4. Re: Archiving data and obtaining 'live' record count using a calculation field
                Frinholp

                Problem solved

                I have created a calculation field called cArchiveKey which is always set to True.

                This calculation field was setup to be stored, this was causing the issue. This field needs to be set as unstored or global.

                Lee