AnsweredAssumed Answered

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

Question asked by Frinholp on Jan 28, 2011
Latest reply on Jan 30, 2011 by Frinholp


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


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