Maintaining found sets

[charper]

My database consists of one file that's organized into several different record types (Customers, Orders, Products, etc.) There's one layout (used for viewing and modifying records) for for EACH record type . Scripted finds are performed as the user navigates between layouts to ensure that they see the appropriate TYPE of records only. Example: When user goes to the Customers layout, his found set becomes all of the Customer records in the file. Other finds can done from within each layout to find more specific subsets of each record type. However, these found sets are not retained as user navigates between layouts.

Example: User goes to the Customers layout and finds only those customers who live in California. Then user goes to the Orders layout and finds only those order that were placed on 2/10/01. User then returns to Customers layout. But information about the previous found set of customers is gone (since the database made no effort to retain this info anywhere).

I'd like to be able to restore the previous found set of California customers when returning to the Customers layout in the above example. I'd like this behavior to be the same for all layouts for each record type. What's the best way to do this?

Should I just have a set of Globals for each record type that stores the find criteria that was last used on each layout? Then, everytime the user navigates to a layout, I could just perform a find based on these globals. Is this kosher? Is there a better way?

I'd also like to keep track of the last record that was viewed on each layout and return user to that record.

Note: I'm not interested in solutions that involve breaking my flat file up into a multi-file database structure, but I would like to discuss the pros and cons of flat file designs over "relational" with someone sometime.

[LiveOak]

How about a couple of relational solutions that use only your existing file? The first is to navigate between record types without a find (slow) using a relationship (fast). Create a global field "gType" of type text. Create a self relationship with your current file in which gType (on the left side) is related to Type (on the right side of the relationship). Call this relationship "SelfByType". Your script to change displayed types now looks like:

SetField(gType, "Product")

Go to related records (SelfByType, show only related records)

Go to Layout (Product Display)

You will find this to be VERY fast compared to a find.

To restore a found set of records that is not quite so simple as all products, give each record a unique serial number with the autoenter settings. Create a text global called "gFoundSet". Create a self relationship between gFoundSet and SerialNum (call it "FoundRec". Now the tricky part. You will need a list view layout with ONLY the SerialNum field, no labels, other text, or other fields. Call this layout "Found Capture". You will also need a layout with the field gFoundSet showing. I'll call this utility layout "All Fields", since we usually have one we can use to make sure a paste operation is always done on the layout were a field appears. When you change layouts, call a subscript to capture the found set as follows:

Go to layout (Found Capture)

Copy All Records

Go to layout (All Fields)

Paste (gFoundSet)

This will paste all the serial numbers for the found records into the text field gFoundSet with carriage returns between them. This is a way to create a many to many relationship. To restore the found set of records the script looks like:

Go to related records (FoundRec, show only related records)

This has some limitations. The text pasted to gFoundSet is limited to 64K characters, so a limited number of records can be captured. A variation of this trick is used to mark records in multi-user files. If, in a multi-user file, you just create a field "marked" and put a one in it AND another user is doing the same thing, you will find all records in both users marked sets. The variation is a trick we use to prevent this.

Hope I explained the techniques in an understandable manner.

-bd

[charper]

Yes, this is a superb reply. Very clear.

The 64k limitation is my only aprehension here. I'm just thinking outloud now about how many actual records this maximum will represent. I'm planning on a magnitude of tens of thousands of each record type. So, say if I have 10,000 customer records 6 months from now, that's potentially 10,000 serial numbers that my foundset global would have to store at, what, 4 bytes each? That's 40k which would put me just under the gun.

Success with this company's project could mean trouble with found sets in about 6 months to a year. Hmmm. That's a problem.

What happens at the breaking point? If you try to stick more than 64k into a FMP field, do you get a nice warning or does it just paste as much as it can and continue as though nothing were wrong?

My brute force solution of just capturing the latest search criteria for each record type with a set of globals has the appeal of always working no matter how big my found sets get. But I hate the lack of economy in having to maintain a truck load of globals for each record type. Ugh. I'm stumped.

Further comments or suggestions?

[LiveOak]

The limit won't apply when you are displaying all product records, you'll just match a global containing "product" to a type field containing "product". It's more of a question of marking all customers in California. This count could be much less. -bd

[charper]

Erm....you lost me there. When does the limit not apply when there is a very large number of records in my found set. Doesn't the storage of a found set, in this solution, always entail copying a string of record identifiers into a global text field whose limit is 64k.

Also, what about just having a flag field for each record in the database (a DIFFERENT flag field corresponding to each layout/record type)? Every time a new find is performed, using the Replace command, I set the flag fields to zero and use the Replace command to turn on the flag field for the all of the found records in the given record type. Then, whenever I go to a particular layout, I do a find on this flag field for the appropriate record type.

This seems pretty robust and economical. My only concern is whether or not there will be a performance issue with doing a huge number Replacements across very large found sets (20 thousand records, say). What do you think?

[charper]

Oh crapola. This field tagging solution won't work for a multi-user scenario. Aarrg!

This is a real pickle.

[Vaughan]

What if the flag field added/appended a unique identifier for each user, so the field could have multiple flags in it? Tricky to add/remove identifiers, but would be multiuser.

[charper]

Yes, I've pondered that, but I don't see a way of doing that without having to execute a Set Field command and a Go To Record (next) command for every record in the found set. Definitely a perfomance issue with that solution for large found sets, no?