Archiving using a self-join and calulation field

[frinholp]

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 Archived 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::Archived so there is a match when the Archived field is true or set to 1.

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

cTotalRecords = Get ( TotalRecordCount) - Count (Archive::Archived) - This should calculate the total amount of records that are live.

All works correctly until I archive all records in the table. When I archive all records cTotalRecords returns the total amount of records.

I have performed a watch on Count (Archive::Archived). This should return the number of records that have Archived set to true or 1. This count works fine until all records Archived fields are set to true or 1. When I do this, Count (Archive::Archived) returns 0 eg I have 6 records in the system and archive 5 of them, the count returns 5 which is correct. If I archive the 6th record the count returns 0. Strange?

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

I have 2 layouts base on the Main Table TOC. One that uses a form view for live records and one that shows the archived records in a list view. When performing a watch on Count (Archive::Archived), if I switch to the layout that shows the archived records, Count (Archive::Archived) returns the correct count when all records are archived i.e if there 6 records in the system and 6 are archived the count returns 6 how the count should behave. Why would this happen? As I stated both the layouts are based on the same TOC.

I have spent days on this to no avail. I really hope someone can help on this one. It is probably going to be something so minute to change but something massively significant - I hope.

Anyone any ideas?

If it helps I have included a stripped down version of my database and provided 6 records. You archive records by clicking the icon with database symbol with red minus sign. To view the archive you click on the icon that looks like a filing cabinet next to tools on my custom toolbar (underneath the spellcheck icon). To restore a record click on the green tick.

I will also include the example database that someone kindly allowed me to use which shows the archiving system functioning in its most basic form. I have cross referenced my structure to the example file so many times now and there is nothing obvious that is different.

Maybe some users may like to incoporate this system in their own design and hope it proves a useful example. The database was provided by PhilModJunk. Phil from Caulkins Consultants so all rights belong to him and many thanks to Phil for his generosity.

Thanks in advance

Lee

Test file and example.zip

[comment]

The "True" calculation field needs to be unstored (or global).

I have cross referenced my structure to the example file so many times now and there is nothing obvious that is different.

Doesn't the example file exhibit the same flaw?

BTW, your archiving method is unnecessary complex: all you need to do is set an Archived field to the current date or timestamp, then omit the record. The archived status can be calculated as =

GetAsBoolean ( Archived )

[frinholp]

Thanks Comment

I ironic thing is that I give somebody advice on using unstored calculations yesterday on a different forum.

I would never of thought about getting the archive date as a boolean - something I will re-use in the future.

In my circumstance, when I restore a record the archive date will still hold a value. What is the best way to clear the date. Set as null?

Thanks again

Lee

[comment]

when I restore a record the archive date will still hold a value.

That doesn't make sense to me. You restore an archived record by emptying the archived date (i.e. setting it to "").

[frinholp]

I was looking for info on what is the best way to empty a field's content and whether setting it to "" is the correct procedure. I was unsure if = null is a method or if there are any other methods other than "".

"" is fine though.

Cheers

Lee

[comment]

BTW, I would do it this way:

Archived.zip

[frinholp]

Hi Comment

I now have two instances of my database. One using the equality join, and one using the cartesian cross join. Both are functional. Could you please explain the benefits of using the cartesian cross rather than the equality join?

Maybe you could answer another question that has come to mind? I am developing a database that incorperates an invoicing system. I would like to be able to view unpaid invoices within a poratal, and also on layout that exclusively displays unpaid invoices. The invoice would contain a field that flags whether the invoice fee has been settled, and a timestamp of the settlement date.

In such a case, would it be beneficial to use a similar system to the archiving system, or perform a find on layout load?

Your assistance is greatly appreciated,

Lee

[comment]

The main benefit is that the same relationship can be reused for other purposes (e.g. counting other attributes, displaying all records in a portal, etc.). There are also less fields, I think.

Re your other question, I believe it's largely a matter of preference. You could also use a filtered portal (provided the number of related records is not too large).

---

BTW, the correct term is "Cartesian product".

[frinholp]

Hi Comment

I have opted to use a solution similar to the one you proposed in you technique file.

I was wondering if I could pick your brain on a couple of things that are not clear to me at present?

In your technique file the cActive calculation is not stored and indexes are automatically created when needed. Is there any benifit to store the index? Is there any instance you can think of where it would be more efficient to to treat the calculation as unstored?

I have two layouts based on my table. One shows active records in a form view and the other shows archived records in a list view. As you recommended I have two scripts, one for archiving a record and one that restores a record.

When I switch layouts I perform a find cActive value is 1 for the active records layout, and cActive value is 0 for the archive layout. As suggested when Archive Record or Restore Record is executed, I ommit the current record from the found set. When restoring a record I would like to be able to exit the archive, and the restored record to be checked if it meets the criteria of the previous found set and show/not show dependant on the previous set criteria. At present because I am performing a on cActive value is 1, I am returned with a new found set.

It is probably very basic standard technique that I'm looking for. Could you please enlighten me?

Thanks again

Lee

[comment]

In your technique file the cActive calculation is not stored and indexes are automatically created when needed.

I am afraid you are mistaken in the first part: the calculation is stored. It will get indexed when required (for example the first time you perform a find searching it). An unstored calculation cannot be indexed and searching it will be slower.

I got a bit lost in the second part. I think you'd want to perform the original find again, after restoring the archived record. If you're not sure what criteria were used, you could use the Modify Last Find [] command/script step.

[frinholp]

Thanks for you prompt response

In your technique file the cActive calculation is not stored

Is there any instance you can think of where it would be more efficient to to treat the calculation as unstored?

Sorry my bad, I meant is stored

I got a bit lost in the second part. I think you'd want to perform the original find again, after restoring the archived record

This is exactly what I meant

If you're not sure what criteria were used, you could use the Modify Last Find [] command/script step

No, I wouldn't be sure on what criteria was used as the previous found set could be a result of a user defined find. Thanks for making me aware of this script step

Lee

[frinholp]

Hi

It may seem that I'm worrying excessively here but I'm simply trying to broaden my understanding. The field cTotalActive is defined as

 cTotalActive = Get ( TotalRecordCount ) - Count ( All::Archived )

and the cCountArchived defined as 

cCountArchived = Count ( All::Archived )

would there be any difference in performance if cTotalActive is defined as 

 cTotalActive = Get ( TotalRecordCount ) - cCountArchived 

?

[comment]

In performance? I don't know. You could try and test this - but my guess is that you would need a VERY large number of records to see a difference (if there is a difference).