3 Replies Latest reply on Jul 14, 2011 8:54 AM by philmodjunk

    Match multiple criteria in same field

    GertvanOss

      Title

      Match multiple criteria in same field

      Post

      Hi,

      I've tried various find requests, some scripting but all without the result I'm looking for. It seems so easy.

      Setup:

      3 columns
      Serial# | kfCompanyID | kfCity


      A company can have various locations (London, Paris, Boston, Istanbul)
      Is it, with this setup, possible to get a selection of, for example:

      All companies with BOTH an office in Boston AND Istanbul?


      Can somenone can point me in the right direction?


      Thanks,Gert

        • 1. Re: Match multiple criteria in same field
          philmodjunk

          Boston OR Instanbul can be done, but finding two different records where one stores Instanbul and another stores Boston and only those that have one record of each can't be done with just a Find from this table.

          Two approaches come to mind:

          Method 1:

          Perform the find for Boston OR Instanbul using separate find requests.

          Sort the records by companyID to group records for the same company together.

          Use a script to loop through the records and omit those that do not appear twice.

          Method 2:

          Define a separate table with just one record for each company that links to a related table of location by CompanyID. Define a calculation field in this company table as: List ( Locations::City ). This calculation field will show a return separated list of all cities where a given company is located. Perform a find that enters Boston Instanbul as criteria in this field and it should pull up only those Company records that are located in both cities.

          • 2. Re: Match multiple criteria in same field
            GertvanOss

            Ha,

            Thanks for the quick response. 

            I like method 1 for its simplicity though I'll go for method 2 as then I can narrow the list down to companies that have even more then 2 locations in common. Very good suggestions.

            Thanks again for the advice.

            Gert

            • 3. Re: Match multiple criteria in same field
              philmodjunk

              Another option that eliminates the need for a related Location table is to make your location field a check box field so that you can select multiple cities for one company record. This assumes you don't have other uses for that Location table.