4 Replies Latest reply on Dec 1, 2016 1:24 PM by TonyTrevisonno

    Match field for search results in multi user environment


      Hi folks,


      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?

        • 1. Re: Match field for search results in multi user environment

          One option that comes to mind is to use a Global field (unique for each user) in your Search table that stores a ListOf Summary result IDs for the desired found set. Then your relationship for the portal is based on this Global field. This also prevents a significant number of updates to your data table.

          • 2. Re: Match field for search results in multi user environment

            To expand on that, the following relationship


            SearchLayoutTableOccurrence::globalFieldListingIDs = DataTableOccurrence::IDs


            will match to all records in DataTableOccurrence that has an ID that is listed in the global field. And


            The value each user gets in that global field will be unique to their client session. If user 1 get's a list of 3 records, 123, 456, 789, User 2 won't see those ID's and can get a completely different list of IDs into that field without interfering with User 1's search.


            The key, in both senses of the word, is to get a list of unique ID's separated by returns into that global field. A List Of type summary field sounds like the simplest way to get that list of ID's into that global field. Note that if you are using Perform Script on Server, you will need to pass that list back to the client via Exit Script where the client performed script can use Get (Script Result ) to extract that list and put it into the global field.

            • 3. Re: Match field for search results in multi user environment

              Tony --


              In addition to the solution provided by phillipHPG, you can include a global field in your "display this record" flag calculation.  Any calculation field that depends on one or more global fields will be dynamically recalculated for each user.  I use this technique to, among other things:

              • Generate user-unique value lists for various purposes.

              • Support user-unique dynamic portal sorting and filtering (this is true dynamic filtering, by the way, not simply suppressing the display of otherwise linked child records, as FileMaker provides) without triggering uncommitted record modifications.

              • Track active record status across multiple window instances of the same layout, with a different set of instances for each user.  A simple example of this is highlighting the active record in Table view -- multiple window instances, each with its own highlighting.  This technique, in effect, lets you create variables local to a particular window.


              If you can lay your hands on an old copy of Ray Cologon's FileMaker Pro 9 Bible, you'll find an excellent, in-depth, yet easy-to-understand discussion of global fields -- including dependencies involving global fields -- and their uses (I'm sure this same info can be found in later editions of the FileMaker Bible series as well).


              Hope this helps. -- Bob

              • 4. Re: Match field for search results in multi user environment

                To all of you, I am eternally grateful!!

                Thank you so much.

                I tried a few variations of what you recommended, and I'm still fiddling around with it, but this is much better than what I was doing. Its much faster too.


                But, soon, I will have another issue to deal with. It's similar as it deals with multi-user vs single user, but this time it deals with the table as a whole. Look out for my post next week!!


                Thanks again!!