X

Shay Shmeltzer's Oracle Development Tools Tips

Filtering Data Providers with Compound Conditions in Visual Builder

Shay Shmeltzer
Director of Product Management - Oracle

I posted in the past a basic introduction to filtering lists in Visual Builder - showing how to use the filterCriterion to filter the records shown in a ServiceDataProvider. Since I recorded this video, a few things changed, and I also saw several customers asking how can they use more complex conditions that involve more than one filter.

In the video below I show how to define a basic filter with the latest versions (note that in VB 18.4.1 you no longer need to surround value with quotes ""), and then I show how to create a more complex condition that involves two filter criteria and set them to work with either an or or an and operator.

When you are using business components in Visual Builder, the filterCriterion is translated into a "q" parameter that is passed to the GET method (more about this q query parameter here). If you find that you are not getting the records you are expecting, check out the browser's Network tab to see what query parameter was passed in your call to the REST service (intro to this debugging technique here).

As you'll see the filterCriterion contains an array of "criteria" so you can specify several of them. In the video I'm using an approach that Brian Fry showed me that gives you a more declarative way to populate the array dragging and dropping multiple "criteria type" variables into the same array.

Note however that the important thing is what is actually being populated in the json file that defines the action. You should go into this view and verify that you have the right structure there. You can also directly manipulate that source to achieve the filter you need.

As you'll see in the video there are some cases where the design time for this filterCriterion adds an entry into the JSON that might not match what you want (we are tracking this issue). So as mentioned - if things don't work as expected direct manipulation of the JSON might be required. 

Another issue that has been identified with the above approach is when you leave one of the field empty, the filterCriterion is not correctly created. Andrejus has a solution for this on his blog using a JavaScript method to properly construct the conditions. One more tip is - if you don't get the filtering you need working, check out the browser's network tab to see the format of the q parameter appended to the call to your business objects.

Here is a clip from the JSON file of the action:

        "assignVariables2": {
          "module": "vb/action/builtin/assignVariablesAction",
          "parameters": {
            "$page.variables.travelRequestsListSDP": {
              "source": {
                "filterCriterion": {
                  "criteria": [
                    "{{ $page.variables.locationCriteria }}",
                    "{{ $page.variables.priceCriteria }}"
                  ],
                  "op": "$and"
                }
              },
              "reset": "none",
              "mapping": {
                "$target.filterCriterion": {
                  "source": "$source.filterCriterion",
                  "reset": "empty"
                }
              }
            }
          }
        }

Join the discussion

Comments ( 2 )
  • David Gress Friday, December 28, 2018
    Thanks for information. Exactly what I was looking to do.
  • Sai Monday, August 26, 2019
    Thanks Shay.
    This is very useful information to build search criteria.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.