Visual Builder

Get Involved. Join the Conversation.

Topic

    Vinod Kumar Kv
    How to use conditional compound filter for below criteria:...
    Topic posted November 9, 2018 by Vinod Kumar KvBronze Medal: 1,250+ Points, tagged Business Objects, Database 
    645 Views, 25 Comments
    Title:
    How to use conditional compound filter for below criteria: Service Data Provider (on table)
    Summary:
    How to use conditional compound filter for below criteria: Source: Service Data Provider (on table)
    Content:

    How to use conditional compound filter for below criteria:

    Source: Service Data Provider (on table)

    Scenario:

    By default show query all records for object with sid==$page.variables.pk

    OR

    There is filter by name text box on page: if user wants to search records based on name then use criteria
    sid==$page.variables.pk AND firstName LIKE $page.variables.nameFilter

    Basically we want to have primary key filter always. If there is value in filter text box then apply AND operator with PRIMARY KEY value

    CRITERIA: (sid==$page.variables.pk) OR (sid==$page.variables.pk AND firstName LIKE $page.variables.nameFilter)
    At any point of time only one should work.

    As of now we have below mentioned filter criteria but this code is listing all records always with .
    {
     "criteria": [
      {
       "attribute": "{{ \"sId\" }}",
       "op": "{{ \"$eq\" }}",
       "value": "{{ $page.variables.pk }}"
      },
      {
         "op": "{{ \"$and\" }}",
         "criteria": [
          {
           "attribute": "{{ \"firstName\" }}",
           "op": "{{ \"$co\" }}",
           "value": "{{  \"$page.variables.nameFilter\" }}"
          },
          {
           "attribute": "{{ \"sId\" }}",
           "op": "{{ \"$eq\" }}",
           "value": "{{ $page.variables.pk }}"
          }
         ]
      }
     ],
     "op": "{{ \"$or\" }}"
    }

     

    Comment

    • Christopher Maggiulli

      I would also like to know this

    • Shay Shmeltzer

      If we say that 

      Cond1 is  

      {
         "attribute": "{{ \"sId\" }}",
         "op": "{{ \"$eq\" }}",
         "value": "{{ $page.variables.pk }}"
        }

      And Cond2 is 

            {
             "attribute": "{{ \"firstName\" }}",
             "op": "{{ \"$co\" }}",
             "value": "{{  \"$page.variables.nameFilter\" }}"
            }

      Then if we understand correctly you can always have Cond1 as the assigned value for the SDP.

      and then in an event where there is a value in the nameFilter you can use assign variable to set the filterCriterion to an "and" condition that has both conditions.

      Right?

    • Vinod Kumar Kv

      Hello,

      Can you please provide us one example implementation code.

      Thanks,

       

    • Christopher Maggiulli

      I'll give an example of what I mean (which may be different from OP)

      So I have a business object called JobTitles.  JobTitles has three columns: label, id, and country.

      On page I have a radio button set with the following options: United States (US), Canada (CA), Both (BO).  In the JobTitles BO each row has either a US or a CA.  I have an SDP for the BO that populates a select element and filters based off the country.  It works to when US or CA is selected, but how do I display the entire table (regardless of the country code) when Both is selected?

      I know I could technically change the US code to USBO and the CA code to CABO, then I could have a filter criterion that

      {

         "attribute":"country",

          "op":"$co"

          "value":"{{ $page.variables.countryCode }}"

      }

       

      In this case that will work, but in more advanced cases that is not possible.

    • Shay Shmeltzer

      I created a short video showing how to do and/or conditions between more than one condition.

      The basic idea is that you create variables that hold your criteria (attribute, op,value).

      Then you assign those variables to your filterCriterion.criteria array, and you set the operator between them.

      You'll need to make sure that the filterCriterion is being reset properly between calls (reset should be empty).

      Video - https://youtu.be/6P2MNrmA2JA

      Code used in the action chain:

            "description": "",
            "variables": {
              "detail": {
                "required": true,
                "type": "any",
                "input": "fromCaller"
              }
            },
            "root": "assignVariables1",
            "actions": {
              "assignVariables1": {
                "module": "vb/action/builtin/assignVariablesAction",
                "parameters": {
                  "$page.variables.travelRequestsListSDP": {
                    "source": {
                      "filterCriterion": {
                        "criteria": [
                          "{{ $page.variables.costCond }}",
                          "{{ $page.variables.toCond }}"
                        ],
                        "op": "$or"
                      }
                    },
                    "reset": "none",
                    "mapping": {
                      "$target.filterCriterion": {
                        "source": "$source.filterCriterion",
                        "reset": "empty"
                      }
                    }
                  }
                }
              }
            }

    • Vinod Kumar Kv

      Thanks a lot for providing sample implementation.

    • Vinod Kumar Kv

      Hello Shay,

      After implementing compound filter criteria shown in below mentioned video my sort criteria is not working?

      Video - https://youtu.be/6P2MNrmA2JA

      Thanks

      • Shay Shmeltzer

        Sorting still works in my sample project that is based on business objects.

        Did you use the default sorting the table offers or are you doing anything via code/action flows?

    • Vinod Kumar Kv

      Hello,

      I am using default sorting the table offers.

      When I set the sorting criteria it shows me sorting filter mapped but when i click on sorting filter to view sort criteria JSON it shows blank.

      Thanks

      • Shay Shmeltzer

        I'm not sure I understand what you mean when you say:

        When I set the sorting criteria it shows me sorting filter mapped but when i click on sorting filter to view sort criteria JSON it shows blank.

        Can you attach screenshots that show the behavior?

    • Vinod Kumar Kv

      Hello Shay,

      Please find attached the screenshots in sequence.

      Thanks

    • Vinod Kumar Kv

      Criteria mapped but JSON not visible

      Thanks

    • Vinod Kumar Kv

      Hello Shay,

      I got the problem I need to set ascending or descending not asc or desc. After setting sortcriteria derection to ascending sorting started working but still if i click on sort criteria the sort criteria JSON is not visible.

      Thanks,

      • Christopher Maggiulli
        Thank you I ran into this problem yesterday and didn't think to use descending instead of desc. Does anyone know if there are any efforts to standardize and/or offer multiple ways to achieve something? Another issue I ran into yesterday was the orderBy parameter in PCS - it required that I have the capital B but I would assume capitalization in query parameters would not affect behaviour. Same with ascending vs asc I would assume it's beneficial to accept both
    • Vinod Kumar Kv

      Hello,

      Compound filter is not working as expected:

      Scenario: We have two fields on page for search criteria: Name(oj-input-text) and Type ["Online", "Email", "Sms"] (oj-select-one)

      Now filter should work for below conditions:

      1) no filter if nothing is selected as criteria

      2) Append name text for filtering records (only Name as filter)

      3) Append type text for filtering type (only Type as filter)

      4) Append name and type for filtering records (Name and Type both as filter)

      We followed the link for building advance filter criteria: https://blogs.oracle.com/shay/filtering-data-providers-with-compound-conditions-in-visual-builder

      As we run our VBCS application filtering is not working as expected because it keeps one of the criteria as default criteria and other criteria which is not default will not work unless and until you have something in default criteria associated by VBCS (in our case name text).

      Please guide us how to achieve filtering as we have mentioned above.

      Thanks

      • Shay Shmeltzer

        I'm not sure what you mean by "default criteria". 

        In the blog above the filterCriterion is redefined every time that you press the search button.

        It is up to you to define the criterion you want to be implemented - and you can do this in your action flow to only include the criterions that should apply.

        Also note the change done directly in the json file around minute 5:10 to make sure the filter is reset at https://youtu.be/6P2MNrmA2JA?t=311

    • Vinod Kumar Kv

      Hello Shay,

      Thanks for your reply:

      As per requirement ​filter shown on attached screen image should work for below conditions:

      1) no filter if nothing is selected as criteria

      2) Append name text for filtering records (only Name as filter)

      3) Append type text for filtering type (only Type as filter)

      4) Append name and type for filtering records (Name and Type both as filter)

      Please see attached image for screen filter not working and filter criteria JSON.

      Please guide us how to achieve processing of this filter criteria in VBCS

      Thanks

    • Vinod Kumar Kv

      Hello Shay,

      We have further analyzed this issue and found that filter starts working if we make a filter call by supplying both filter valuesName and Type). 

      If we supply value only for Type after page load it will not work. We also have seen network tab in debug console and found that no REST calls are issued on clicking  search.

      Once we put value in text field as well as select value from dropdown filter starts working and we see  REST calls in network tab as well.

      Is this desired behavior or bug?

      Thanks

      • John Graves

        Vinod,

        You can setup events for when the user clicks the button, enters text (and hits return) and/or when the dropdown is changed.  Typically you'd run the same action for all three cases.

        Regarding your filter, I would need to see the details of what typeFilter and nameFilter ends up being.  Ultimately, I would think you'd want something like this

        {
         "criteria": [
          {
           "value": "{{ $page.variables.serveyFilterValue }}",
           "op": "$co",
           "attribute": "serveyName"
          },
          {
           "value": "{{ $page.variables.serveyTypeValue }}",
           "op": "$eq",
           "attribute": "serveyType"
          }
         ],
         "op": "$and"
        }

    • Vinod Kumar Kv

      Thanks John,

      We are using VBCS version: Version: 18.4.1

      We are doing the same as mentioned above. It is working.

      The problem is when the page loads the first time and we only want to filter by serveyType dropdown filter and click the button no filter data is retrieved. Now we keep the dropdown filter as it is and simultaneously enter some filter in the text box then filter criteria seems to be working. This happens only the very first time where both needs to be selected after that each individual criteria works independently.

      Thanks

      • John Graves

        That's very odd.

        So after you enter some data in the Survey Name, then make it blank again, it works?

        Are you using the $co (contains) operator for Survey Name?

        Did you set a default value of "" for the Survey Name variable?

        I can try to mock something up tomorrow if you don't figure it out.

        -John