Archiving data and obtaining 'live' record count using a calculation field
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