Permanent Re-Sorting of table after appending

[sgius]

I have an FM 6 database with 150,000 + records which I update on a weekly basis from sales transactions. The ideal way for the database to appear is with the most recent imported records at the top. Since this is a chronological history of sales, this would display this most recent sales first and the oldest at the "bottom". I am able to permanently Re-sort the database by sorting it and then importing it into a clone of itself, but this is a very time consuming process (it takes about 2-3 hours for the import). Each time I update the database with the most recent records, they show up at the "bottom" of the list, instead of at the top, and then the database is no longer permanently sorted by most recent to oldest.

Any suggestions on how to force the new records to be imported to the "top" of the list?

[BobWeaver]

The only way to permanently sort records is the way you did it -- importing into a clone. It's time consuming because Filemaker has to re-index all the records. It's more efficient to have a startup script that sorts the existing records the way you want (eg., by creation date in descending order).

[-Queue-]

This is not completely true. Check the sample files for an alternate method that doesn't require clones. Just beware that auto-entered data should be checked and corrected after duplication. I use a simple self-relationship to set any auto-entered fields with data from the original record, before it's deleted.

[Anatoli]

RE: simple self-relationship

Hmm, with portal sorting 150,000 + records it will take ages...

[BobWeaver]

I assumed that Sgius meant physically rearranging the records. The simplest method I know of, is to do an import into a clone. You could also move the data from one record to another, but I wouldn't even entertain the thought.

Queue, I assume the example you referred to (and Anatoli hinted at) involves a sorted portal technique, which may be the best solution if it's suitably fast. But, I think it would be better, when opening the file, have a startup script find the most recent records and then sort these. I can't see any benefit (other than frustrating the user) to having 150,000 records in the found set when you open the file.

[-Queue-]

I agree with you, Bob. I just figured if sgius was hell-bent on doing it, I might as well point him in a possible direction.

FWIW I don't believe a portal sort would be necessary. A descending sort by creation date and time for the already-existing records should suffice. And while it would indeed take awhile, it could still be faster than importing into a clone, depending on how many fields and indexes exist in the file. I would still recommend an on open sort, but only after finding records for the last week, month, year, or however long is deemed necessary. Is there any conceivable reason for displaying even half that many records on open?

[BobWeaver]

My experience on one project that I inherited was that the original developer never considered how big the files would get and therefore never limited the number of records that would be displayed on startup. In that case, I added a search in the startup script to find the most recent day's records. Depending on the application, you could probably start with no records in the found set, and ask the user to do a query for what is required.

[CobaltSky]

An alternative that would likely make the portal approach more viable would be to have the self-join relationship based on a date range (eg using an unstored calc based on Status(CurrentDate) on the left and the record creation date on the right) so that by default it only displays the most recent week, month or year's records (whichever suits).

This approach could readily be provided with a 'scroll-back' option so that the user can view blocks of records from earlier periods if desired - but at any one time the portal will be presenting a manageable 'slice' of the data - and always sorted so that the most recent records (of the group being viewed) appear at the top.