3 Replies Latest reply on Jun 6, 2017 6:52 PM by beverly

    Finding multiple records using delineated search string


      I work with a few databases (not FileMaker) that have the capability of returning an almost unlimited number of records when certain fields are searched using a character string of identifiers (usually a unique ID number of some sort) separated by commas.


      Does anyone know if FileMaker 15 has such a capability designed into it?


      I've tried using various separators in both Find mode and the Quick Search bar, but no luck.



        • 1. Re: Finding multiple records using delineated search string

          If you are referring to the use of SQL:

          WHERE x IN (1,2,3,4,....)

          and any of the values are returned (an OR search),

          then not directly.


          FileMaker does have a "multi-line key" type of match on a return delimited list of values. This would be an OR search so that any would match the relationship.


          Typically the values may be put into a field with global storage and the relationship would be created between the field and the ID.


          Another method: is a function called ExecuteSQL() and it could would use the WHERE...IN. But it may become unwieldy. The result is probably not what you want as it doesn't actually FIND, but rather returns text (in one field or variable) what you SELECT. You'd still have to get the 'match' on your records.


          Another method: if you already have the ID's in a storage (field or variable), then you maybe able to "loop" the return delimited list and create multiple requests for your scripted Find. The Multi-line Key would be less cumbersome.


          How are you getting the list of ID's for your search?


          • 2. Re: Finding multiple records using delineated search string

            Thanks, Beverly, for answering.


            The alternate approaches you suggest -- as best I, a non-techie, can comprehend them -- may not be practical for my purposes, but I need to ponder them more.


            As for your question about my search string source, any list I would plug into my FileMaker database would pretty much originate from an Excel export of the non-FMP dbase, and then run through a find/replace in a text editor or Word in order to insert delimiters.


            Now, for the reverse process, my FMP databases have summary fields that list the IDs of the found set. And then calculation fields (Unstored) substitute the necessary delimiter -- comma, in this case -- for paragraph characters in the list.  A button copies it all for found records I'm working with so I can then paste it into the non-FMP system's search form.  It works so nicely, and that's why I'd like to be able to do the same kind of searches in my own dbases.  As it is now, I have to do what I call "batch matches," where I copy/paste those IDs output on the Excel sheet into an .xlsx file I have reserved for this, and then do an import that "marks" the matching records.


            I know this probably seems terribly primitive to a professional developer, but it works for me, just not as efficiently as someone like yourself might make it.



            • 3. Re: Finding multiple records using delineated search string

              OK, Andrew! if you have a column in Excel with these IDs, then a simple export of these (save as CSV) of the one column, many rows and you'd have your return-delimited list.


              The Multi-line key match is not standard and I've not seen it anywhere but FileMaker. It just becomes like the

              WHERE x IN ( ... )

              (any match).


              If you need a little more help with this, there are several articles on the technique. And/or come back with specifics on what you have and how you may set this type of relationship.