10 Replies Latest reply: Sep 3, 2014 10:44 AM by Alexander Dontchos RSS

    Advanced Search - 2 Fields - OR Logic

    Paul Nockolds

      Hi There

       

      QV11 SR1 64-bit

       

      I have 2 fields::

      Field1     JobTitle               in Listbox

      Field2     JobFunction        Dimension within Straight Table Chart   

       

      My users would like to be able to search and select within both of these at the same time using OR logic, so the following sort of thing...

       

       

      =[JobTitle ] like '*Manager*

      OR

      [JobTitle ] like '*Director*'

      OR

      [JobFunction] like '*Executive*'

      OR

      [JobFunction] like '*Management*'

       

       

      The straight table chart does not itself have an advanced search option.  

       

      So if i right-click the JobTitle listbox and choose Advanced Search with the Search in drop-down set to JobTitle, using the statement above i get some results which at first glance appear correct (3223 record count)

       

      However, if i change the Search in drop-down to JobFunction and try the same thing i get different results (2565 record count)

       

      Does anyone have an idea why that might be?

       

      Is there another way for the user to do this kind of thing e.g. a set analysis expression?

        • Advanced Search - 2 Fields - OR Logic
          Jason Michaelides

          Hello mate,

           

          This should work:

           

          1. Create a variable vSearchString (empty)

          2. Add an Input Box object tied to that variable (this is for your users to use and they should type as many comma-separated values as they want)

          3. Create a second variable vWildMatch:

           

          =Chr(39) & Chr(42) & Replace(vSearchString,',',Chr(42) & Chr(39) & ',' & Chr(39) & Chr(42)) & Chr(42) & Chr(39)

           

          this builds a WildMatch()-ready string with wildcards etc.

          4. Then, to list all the registrants that have either a JobTitle or a JobFunction that matches the search, put the following in a text box:

           

          =Concat(DISTINCT IF(WildMatch(Jobtitle,$(vWildMatch))OR WildMatch(JobFunction,$(vWildMatch)),RegistrantName),',')

           

          or, to count the registrants:

           

          =Sum(DISTINCT IF(WildMatch(Jobtitle,$(vWildMatch))OR WildMatch(JobFunction,$(vWildMatch)),Counter_User))

           

          Hope that helps!

           

          Jason

           

          (Now how did I know you were counting registrants? And how did I know you have a counter flag!!??)

          • Advanced Search - 2 Fields - OR Logic
            Paul Nockolds

            Finally got around to looking at this again

             

            Had to add in =1 in a couple of places to get this to work...

             

            =Concat(DISTINCT IF(WildMatch(Jobtitle,$(vWildMatch)=1)

             

                 OR WildMatch(JobFunction,$(vWildMatch)=1),RegistrantName),',')

             

            The count example doesn't seem to work at all even with =1's added?

             

            Not sure this would help me anyway as ideally i want to actually select the results so the users can output the records to excel or make further selections.

             

            If the OR logic worked in the advanced search box it would be great...it doesn't though, in fact it's rather misleading from what i can see.

             

            I've trawled every other post on this subject and there does not as yet appear to be a nice easy way to achieve this.  A macro has been suggested by several people but looking to avoid those if possible.

             

            I'm thinking i may as well concatenate the job title & job function together in a listbox for the sake of allowing someone to search on the 2 together and be able to select them.

             

            Thanks for trying anyway.

             

             


              • Advanced Search - 2 Fields - OR Logic
                Jason Michaelides

                Email me the app mate and I'll have a look.  The above should work fine and so should the advanced search.

                  • Advanced Search - 2 Fields - OR Logic
                    Jason Michaelides

                    Right...got the email, cheers.  First things first - having re-read your issue above, the reason for the difference in counts using the advanced search is pretty clear.  You should not be performing this search in the JobTitle field as not every user has one.  Instead, use the advanced search on the UserID field as there is a unique value for every record.  So:

                     

                    =WildMatch([Job Function BR],'*manager*','*management*','*director*','*executive*') OR WildMatch([Job Title BR],'*manager*','*management*','*director*','*executive*')

                     

                    in the UserID field will give the correct result of 5,952 (on BR tab). It searches for every UserID associated with a Job Title or Job Function returned by the WildMatch() function.

                     

                    Now...I've had some fun with this today and ended up with something I think you'll like!  I've created 4 new variables:

                     

                    vSearchString:

                    <this is empty>

                     

                    vWildMatch:

                    =Chr(39) & Chr(42) & Replace(Replace(vSearchString,', ',','),',',Chr(42) & Chr(39) & ',' & Chr(39) & Chr(42)) & Chr(42) & Chr(39)

                     

                    This builds a WildMatch expression based on the comma-separated string in vSearchString (which is set by your users with a input-box).  It replaces all comma-and-spaces combos with just a comma in case your users put a space between the strings to search for.

                     

                    vActiveBrandTitle and vActiveBrandFunction

                    '[Job Title ' & $(vActiveBrand) & ']'

                    '[Job Function ' & $(vActiveBrand) & ']'

                     

                    These are so you can copy and paste-link the new search objects onto any brand's page and it'll search the correct fields.

                     

                    vWildMatchSearchResult:

                    ='Sum({<[HBM Brand]= {'& $(vActiveBrand) &'}, UserID={"=WildMatch([Job Title ' & $(vActiveBrand) & '],' & vWildMatch & ') OR WildMatch([Job Function ' & $(vActiveBrand) & '],' & vWildMatch & ')"}>} Counter_FACT)'

                     

                    This builds a set analysis expression to count the number of users within the brand that have either a Job title or Job Function containing any one of the search strings. Neat!

                     

                    The Apply button runs an advanced search on UserID for the results of the set analysis.  You can't directly run advanced searches in button actions but you can cheat and dynamically build the expression:

                     

                    =IF(vSearchString='','','=WildMatch([Job Title ' & $(vActiveBrand) & '],' & vWildMatch & ') OR WildMatch([Job Function ' & $(vActiveBrand) & '],'  & vWildMatch & ')')

                     

                    I've also added a Clear button to delete the search strings and reset UserID.

                     

                    Have fun mate...I'll email the app back to you.

                     

                    Jason

                • Re: Advanced Search - 2 Fields - OR Logic
                  Alexander Dontchos

                  Is the advanced search you created case sensitive?