2 Replies Latest reply on Jun 23, 2011 11:35 AM by Jeff Gherardi

    Searching Multiple Lists

    Stephen Lapic

      If I have three different types of parts - A, B, & C -  and on their unique file cards they each draw from a list of Vendors - Vendors for A, Vendors for B, & Vendors for C.  Each list could have between 3 and 10 or so vendors.  Some of those vendors are unique to one part but some can be suppliers for two or all three parts.  I'm creating a search card just for Sales and Pruchasing so they can search by PO number or drawing number but they also want to view what part(s) an individual vendor supplies regardless of PO or anything else.  I can't use any of the three vendor lists since they don't cover them all.  Do I need to create another list combining them all?  The problem with creating a combined list is that if there is a change to one of the original three then I would have to duplicate that work in the fourth - more importantly rember to do it in the forth list. (By the way, there are a lot more than just part parts and lists this was just an example)

       

      Any thoughts?

        • Re: Searching Multiple Lists
          Pedro Branco

          Hi,

           

          If your lists are clean you can create a new one as a SQL list and show Results from the Vendor Variable.

           

          Create a New SQL List

           

          Paste this sql code

           

          Select distinct Vv.ValueCache
          From VariableValue Vv
          Where Vv.VariableID =  (    select variableid
                      from variable
                      where Variablename like 'Vendor Name') And
                Vv.ValueCache != ''
          order by Vv.ValueCache ASC

           

           

          Change the var to you needs.

           

          Set user, pass and connection to the EPDM DB

           

          The new list will list results already inside you DB

           

          Regards,

           

          Pedro Branco

          • Re: Searching Multiple Lists
            Jeff Gherardi

            Another option would be to place a Read-Only editbox on your datacard that uses an Input Formula to combine the selections from Vendor A list, Vendor B list, and Vendor C list into a single field / variable, say 'CombinedVendor'.

             

            Then, on your search card, search the 'CombinedVendor' variable, and if a given vendor was selected in A, B, or C, it would return results when searched.  Also, if you add added values to any of those lists in the future, you would not need to make any other changes and the system would still work as desired.

             

            Please let me know if you have questions or need additional help setting something like this up.