So, I have a table with almost 1 million records (and growing).
I also have a 'search' table and layout for users to query the 1 million record table.
The goal is to display the search results in a portal on the search layout.
The best (fastest and most flexible) solution that I have come up with thus far is a script that does this (among other things):
1. take the users search input
2. go to the 1 million record table
3. perform a standard FileMaker search (the search may be a set of searches and somewhat complex with multiple variables)
4. mark all the search result records with a "1" in the match field
5. the portal on the search layout displays all records with a "1" in the match field.
The script also removes the "1" from records previously marked in previous searches.
This script works really well. It can return around 15,000 records in the portal in around 2 seconds. For FileMaker, I'm happy with that.
So, this works great in a single user environment -- but, what about in a MULTI-user environment?
How do I separate each users results?
Or, is there a better way altogether?
Is the best way to do this by managing multiple unique user keys in the match field?
So, instead of using a generic "1" in the match field, I will use a unique key in the match field for each user.
So, for user 1, I will put "1" in the match field and display records marked with "1" in the portal.
For user 2, I will put "2" in the match field and display records marked with "2" in the portal.
And, so on, per user.
Doing it this way, I will have to "replace" the user key in each match field so I don't disturb the other users keys.
This will probably slow things down considerably with large result sets.
Is there a better way to do this?