Do you also delete records, or only create ?
These are records of client interaction, so they are never deleted and the record count will continue to grow.
The users I want to only view the last 1000 also contribute to these records and again do not delete.
I hope that answered your question.
Would everybody who is presented with this 1000 record set see the same set of records? Or is there further differentiation?
In that case I could think of a mirror table. Creating a record creates it in the mirror table, duplicates it in the big table, then deletes the first record of the mirror table, if the total record of the mirror table is greater than 1000. Specific users only work on the mirror table. You can't find or interact with what's not there.
What about this...
- Log in with a limited access account
- Find All records, Sort by Creation Date (or a timestamp field)
- Go to the last record - 1000
- Capture the date / timestamp
- Set a field (or variable) to that captured value
- Use a Priv Set that only allows access to records with a equal to or greater than value
If it does not need to be exactly 1000 records, you could split it into two tables and use a ontimer script to move the surplus records to the other table.
in a multiuser environment and with 90000 records and counting, you could have problems with records being created by other users while you sort. Moreover, in order to have a value surviving a relogin you should have a system table with only one record and one field into which you capture the timestamp, but record locking problems can arise on that record.
Another approach would be a variation on the virtual list concept. In the past I have called this a RAT. (Record Access Table)
Create a table with 1000 records. Have a record ID; and an ID field to hold the ID field from your target 90,000+ table. Let's say the 90,000 table is Orders. Relate RAT::fkOrderID to Orders::OrderID. Use a script to periodically capture the correct ID set into the RAT table. You can just copy one of the existing ORDER layouts and base it on the RAT table.
All solid feedback.
I was looking for something more like this DavidZakary Actually got me thinking, bc it doesn't have to be exactly 1000 records I could just figure out how many records a day are created then use the date field minus (how many days it takes to get to 1000 records) off the top of my head its about 120 records per day, so it would be roughly 9 days worth of records.
I'm going to give this a try.
Always helps to bounce ideas around
Using date makes it easier.
Well my idea is that everybody works only on the 1000 record table.
They are free to do searches, edit records, anything. The big table is an archive, to which certain people have access.
We had this in a 80 user setup, in which people wrote their daily timesheets by creating records into the "small" table.
If I remember well, in the evening, a server script would:
- search the "Archived" field for the value 0; (0 is autoentered at record creation)
- import the found records in the big table;
- replace the 0 with 1;
- find all records, go to first and omit FoundCount - 1000
- Find omitted and delete em all, thus leaving the last 1000 in the table.
During the day everybody was speedy, interacting with the Timesheet table.
The Administration, 4 people, had access to the archive, counting 1.5 million records.
Good afternoon Tim,
I hope your day is going well. I had an idea regarding your question, but I'm not certain it will meet all your needs. Basically, I created a table occurrence of a table with 1200 records and used a cartesian join (cross join) to make them all visible to everyone via the relationship. I added one calculation field to the table that places a number 1 in the field if the record is in the last 1000 records. I then created a layout based on the parent table with a portal, which filters the records based on the value in the additional field I created. I've attached the sample I created. If it works, it should be simple enough to add a field, table occurrence, and layout to your solution. Good luck!
Last1000.fmp12.zip 148.2 K
Make a serial auto enter and use privilege sets to restrict records to the next serial value minus 1,000.