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.
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?
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.)
I apologise for jumping to conclusions. My mistake, I named two field similar and placed wrong one on layout.
Thanks for the solution