2 Replies Latest reply on Oct 6, 2011 10:45 AM by philmodjunk

    FM Find function similar to SQL IN ?



      FM Find function similar to SQL IN ?


      I have a list of a hundred ID's that I need to do a regular search on.

      The ID's are for customers.


      So I need to find customer 1523, 1458, 1354, 1358, 1353 etc etc - the list is about 100 long.

      We need to find these on a weekely basis and include a date range.

      With SQL you could do something like Select * from customers where ID IN "1523, 1458, 1354, 1358, 1353"

      The beauty of this is that I could bui8ld the statement from a list of ID's in a minute or so using find/replace in notepad++


      I need to implement something similar in a script, but I have 20 of these scripts to build and manually pasting in each individual ID is going to take all day if not more.

      So does anyone have any ideas how I can build this search script?


      Can I do it with constrain found set?

        • 1. Re: FM Find function similar to SQL IN ?

          Here are some concepts you could play with:

          Have a table of two fields: ID and GroupName.  The ID will be the ID, and the Group Name will let you group the customers' ID together - the same CustomerID can be in many groups.

          Link that table to the table you want to search in by ID=ID.  Have a global field, gGroupName, in the customer table (assuming that's where the user works).  Make it a text field, with a drop-down list of all the GroupNames.

          You can script as much of this as you want.  Attach it with a script trigger to the global field:

          Go To Customer Layout
          Set Variable ( $GroupName ; gGroupName )
          Freeze Window
          Go to Layout (the one with the new table of IDs)
          Enter Find Mode
          Set Field ( GroupName ; $GroupName )
          Perform Find
          Go To Related Record (Use that relationship by ID ; Customer layout ; Show all related records)

          You can amend the list as you add more customers.

          You can create the list in Excel and import it to that table.

          • 2. Re: FM Find function similar to SQL IN ?

            Here's a simple script that will find all records with one value in the list of values. You can paste, values into the global field this uses, type them in  or even format the field with a check box group and this will work as long as each value is separated by a return. (And this can be modified to work with commas or other separating characters if you want.)

            #List of IDs are first entered in global text field called: Globals::gIDList
            Enter Find Mode []

               Set Variable [ $I ; value: $I + 1 ]
               Set Field [YourTable::ID ; Getvalue ( Globals::gIDList ; $I ) ]
               Exit Loop if [$I > valuecount ( Globals::gIDList ) ]
               New Record/Request
            End Loop
            Set Error Capture [on]
            Perform Find[]