4 Replies Latest reply on Nov 10, 2014 8:04 PM by user19752

    Pulling Search Criteria from a Field

    melcam

      In my solution each record has a unique long SKU (ie 14AAAMBNOV72014) which contains one of 80 possible short SKUS (in this case AAAMB). I need to create a new field and label each record with its correct short SKU. Since there are over 80 short SKUs and the possibility that new ones will be created, I do not want to program the script to manually search for each one. Is it possible to write a script that will pull search criteria from another table with a field of SKUs? So that script first searches for all records containing "AAAMB" in the product table, labeling all found records with the short SKU, and repeating for each record on the SKU List, going through all the records until it runs out and then stops?

       

      Thanks!

        • 1. Re: Pulling Search Criteria from a Field
          jimfeuerstein

          Hi, melcam.

           

          I'm a bit confused about what you're trying to do here.

           

          From what you say initially, it sounds like your goal is simple: You want to extract the short SKU (for example, AAAMB) from the long SKU (for example, 14AAAMBNOV72014) and then put that short SKU in another field in the same record.

           

          If that's indeed what you want to do, I wonder:

           

          Does that short SKU always appear in the same position within the long SKU? Based on the structure of your example long SKU, I'm guessing that might be the case.

           

          Is the short SKU always the same length?

           

          If so, you could simply grab the short SKU directly using Middle ( longSKU ; 3 ; 5 )

           

          But I'm confused by your questions about searching, and they make me wonder if I've misunderstood what you're trying to achieve. And, of course, if you're SKUs aren't consistently structured, my suggestion is no help anyway.

          • 2. Re: Pulling Search Criteria from a Field
            melcam

            All of the SKUS are different lengths because sometimes they have lot numbers in front of them as well. So if I just pull the SKU from the same spot it won't always be correct. Which is why I would do a search for each SKU, (such as *AAAMB*).

            • 3. Re: Pulling Search Criteria from a Field
              jimfeuerstein

              melcam,

               

              OK, to keep things simple, the answer is yes, you can do what you’re asking, and it's pretty much a question of which piece of the process you need help with.

               

              Here's the broad outline:

               

              First, you collect a list of all the short SKUs into a $variable. There are many ways to do that, but the most efficient in your case is probably the ExecuteSQL function.

               

              Then you can go to the table you want to process and loop through that list of short SKUs in the $variable, doing this for each item on the list:

               

              1. Perform the search

               

              2. If your found set is greater than 0, loop through the found records adding your short SKU to the new field.

               

              Like I said, the question now is which piece of that do you need help with.

               

              However, I still wonder whether there isn’t a way — if we completely understood the structure of both the long SKU and the short SKU — to process each long SKU directly, even if it was just to do some pattern-matching.

               

              - Jim

              • 4. Re: Pulling Search Criteria from a Field
                user19752

                Yes, for example, if lot number contain only numbers, and short SKU never contain numbers,

                Left ( Substitute ( SKU ; [ 0 ; "" ] ; ..1to8 here.. ; [ 9 ; "" ] ) ; 5 )