3 Replies Latest reply on Aug 4, 2014 7:43 AM by tudor

    Massive search

    tmilas

      Hi Guys (and Gals),

       

      I have data base containing 120,000 records. One customer asked me to send them a list of all products serial numbers per their provided list of Sales Orders. In other words... if I had 20 Sales Orders I would creadte 20 new searches... right. Problem is their list ha 4500 Sales Orders.... Can I export that Excell Sheet to Find mode to run of search of all these records... or do I need a script for that?

       

      Keep it Lit,

       

      Tom

        • 1. Re: Massive search
          taylorsharpe

          Use ExectueSQL and Distinct such as:

           

          "SELECT DISTINCT \"Serial Numbers\" FROM Table_Name"

          • 2. Re: Massive search
            LyndsayHowarth

            If you don't want to use the SQL...

             

            You can also do it with a simple relationship by sales order iD.

             

            For the relationship:

            on the left you have a global field where you enter the sales order IDs entered as a list

            on the right you have the sales order ID field in a new table instance - sales order items by global field

            the match is =

             

            you can then create a list layout of the sales orders by the relationship and put the global field from the other side of the relationshjp at the top.

             

            Only sales order items which have their order id entered in the field will appear in the list.

             

            There are other ways to do this with a checkbox to mark the records and have the marked IDs added to the field or make the relationship by globabl mark to mark.

             

             

            I hope that helps.

            • 3. Re: Massive search
              tudor

              I have a generic "List Find" script I use in my main in-house solution that is easily portable to others. It takes a bit of upfront scripting but comes in very useful.

               

              I have three global fields:
              •To Find

              •Field To Search

              •Missing

               

              You paste the list you need to search into "To Find". Then I have a small script attached to a button by "Field To Search" that sets that field to:

              Get ( ActiveFieldTableName ) & "::" & Get ( ActiveFieldName )

               

              That way you can enter the field you want to search by, click the button, and you get the full field name in "Field To Search".

               

              From there, the script basically loops through the list and uses "Set Field By Name" to create as many Find requests as necessary. At the end it compares the resulting found set to the original list and reports back in the "Missing" global which ones it couldn't find.

               

              Because it's a bit more brute force than other solutions it may not be as speedy as some of the other methods but it's worked well for me.