6 Replies Latest reply on Aug 20, 2015 12:59 PM by MorkAfur

    Possible to do a Find records using FM Functions?

    MorkAfur

      Title

      Possible to do a Find records using FM Functions?

      Post

      I'm probably missing something, but is it possible to do a Find using a, for example, Position() (or other FM) function?

      I was trying to do a find based on the second character of a string, but no dice. Nothing found.

      So, do I need to write a script to do "Finds" that use FM's function library?

      I'm trying to avoid the script route since that would be slow compared to an indexed Find.

      Thanks for suggestions,

      - m

        • 1. Re: Possible to do a Find records using FM Functions?
          philmodjunk

          What can sometimes be done is to define a calculation field using the needed functions, such as position and then you perform your find by specifying criteria in the calculation field.

          An alternative approach is to use ExecuteSQL in a query that returns a list of Primary Keys. If you then use this calculation result as a match field against a table of the records queried by the SQL, you can use GO TO Related Records to produce a found set of those records with Primary Keys in the list.

          Please note that none of these methods are particularly fast unless you can use a stored indexed calculation field in the first method. Thus they all may be slow over tables with a lot of records in them.

          • 2. Re: Possible to do a Find records using FM Functions?
            MorkAfur

            Good ideas, thanks!

            - m

            • 3. Re: Possible to do a Find records using FM Functions?
              philmodjunk

              In some cases, you might also be able to get the right results by using search operators.

              *A*

              for example, will find all records where the field contains an "a" somewhere within the text in that field. Other operators can be mixed/matched for different results.

              @a*

              for example, is supposed to find all records where the second character is an 'a'.

              • 4. Re: Possible to do a Find records using FM Functions?
                MorkAfur

                It's too bad that FM doesn't support, given its price, a true RegEx search. Very surprising. There's a standard Perl library that most, if not all, products use, or at least are compatible with.

                ---

                Since the Position() function was just an example, I'm guessing a script approach may be the best way.

                As in... (get a cup of coffee (or two) while this runs....)

                Read a record from the layout

                Do the function on it

                If it meets the function's requirements (like a Position() test, for example)

                Copy that record to another "found" layout or possibly set a field on the current layout so you can later do a find on that field.

                ----

                The SQL idea is good, but for many functions, there wouldn't be a corresponding SQL to the many FM functions to get a list of primary keys for a match field.

                ---

                Thanks Phil!

                - m

                P.S. Holding at 13....Waiting for FM 15...
                 

                • 5. Re: Possible to do a Find records using FM Functions?
                  philmodjunk

                  What you describe doesn't really make sense to me and would not be a method I would use if I could possibly avoid doing so as it would appear to be very, very slow.

                  Care to share some concrete examples of what you had in mind here?

                  • 6. Re: Possible to do a Find records using FM Functions?
                    MorkAfur

                    I'm just assuming a field you want to be able to find something based on some FM function across the entire table.

                    So, in a script, how would you best go through all the data and then have a layout that included your "found set" (equivalent)?  My idea of setting another "flag field" if the function is true (on the first field) is sort of like your calculated field idea. Yet, the script only runs when you need it not when every record gets added to the table as with a calculated field.

                    Maybe add the found PK from the script to a List() and later use that list as a match field for a layout?

                    SQL won't work in this case since (we assume) there isn't a function we need to get a list of PKs to map.

                    Hmmmmm.

                    - m