Visual Builder

Get Involved. Join the Conversation.

Topic

    Bhanu Sethi
    Search for data on a VBCS page based on a parameter value
    Topic posted April 29, 2019 by Bhanu SethiGreen Ribbon: 100+ Points, tagged Database, REST, UI, Web 
    410 Views, 21 Comments
    Title:
    Search for data on a VBCS page based on a parameter value
    Content:

    Hi,

    We have devloped a page in VBCS visual application wherein we are displaying data for the jobs submitted in the past.

    The requirement here is that if the user enters a request id in a text area the table below should show the details of the corresponding record.

    How can we achieve this in VBCS?

    Thanks in advance.

     

    Comment

     

    • John Ceccarelli

      Hook up the text field to a page variable (i.e. searchString), then when someone clicks Search button launch an action chain that calls the GET that retrieves a single record passing in searchString as the parameter. Then do the usual binding the return payload to a local page variable and use that variable to provide data for the form.

      Sounds similar to this:

      https://blogs.oracle.com/vbcs/single-page-master-detail-in-vbcs

      • Bhanu Sethi

        Hi John,

        Thanks for your input.

        Here, I have mapped the value that the user enters in a text feild to a page variable.

        Next I have set up an action chain, here the issue is when I try to map the value of this search string or the text that user enters to the GET as a parameter to fetch data, I am not able to do so.

        There are no values coming up on the target to map to.

        Please refer to the attached snapshot and advise.

        • John Ceccarelli

          Looks like something wrong with the service connection. Are you calling the REST API that gets a single record or multiple records? What kind of service are you calling? Business Object, Fusion Apps REST, or something else?

          • Bhanu Sethi

            I am calling a REST API here which fetches multiple records at a time.

            I have actually exposed my DBCS as REST service and am trying to get data from one of the tables in the schema there using the REST endpoint.

            If the page loads then the table that I have dropped there shows data(all records) but when I am trying to embed this functionality to enter the data value in a text area and the on the click of a Search button it should display only that particular record in the table, this isn't happening.

            Please let me know if you would need any more inputs to help here,

            Regards,

            Bhanu Sethi

    • Shay Shmeltzer

      Another example :

      https://blogs.oracle.com/shay/filtering-a-table%2c-list-or-other-collections-in-oracle-visual-builder-cloud-service

      and for more complex criteria:

      https://blogs.oracle.com/shay/filtering-data-providers-with-compound-conditions-in-visual-builder

      • Bhanu Sethi

        Hi Shay,

        I have tried to do so referring to the first link. But on clicking the search button nothing happens, data isn't getting filtered and displayed in the table accordingly.

        Below is the JET code that got generated when I added the filter criterion,

        {
         "filterCriterion": {
          "criteria": [
           {
            "value": "{{ $page.variables.SearchRequestId }}",
            "op": "\"$eq\"",
            "attribute": "\"Id\""
           }
          ]
         }
        }

        Can you please advise?

        • Shay Shmeltzer

          Check the browser console and network tabs to see what happens when you click the search button.

          See: https://blogs.oracle.com/vbcs/debugging-and-troubleshooting-visual-builder-logic

          We need to know if your logic is being activated and what is the resulting REST call that it produces.

          Also - is your data coming from a business object or from an external REST service?

          • Bhanu Sethi

            The data here is coming from a REST service. I have exposed DBCS as REST service and trying to display data from one of the tables here.

            When the VBCS page loads, and a blind search happens all records are displayed in a table dropped on the page, but the functionality of searching for a single record on click of search button and displaying only that particular record in the table then isn't working.

            Attached is a snapshot of what the page looks like for easier understanding

            • John Ceccarelli
              Did you enable querying on the REST service and provide the transforms 
              functions for telling the client how to formulate the query URL?
              
              
              
              • Bhanu Sethi

                Hi,

                Can you please share the steps on how to enable quering on the REST service?

                Also, if you can elaborate more on the transform function here.

                Thanks in advance.

                • Shay Shmeltzer

                  Does your REST service support query/filter?

                  What's the syntax used to tell the REST endpoint to filter? Is it by passing some parameter?

                  This really depends on the service you are using and the support it provides.

                  • Bhanu Sethi

                    I checked that the REST endpoint supports query. I am able to establish the service connection also using it but when I try to map the same as a data source to the table now, it says doesn't contain any suitable object. Please advise.

                    Attaching a snapshot for reference.

                    • Shay Shmeltzer

                      What is the syntax of the URL you are using to do the query on your ORDS?

                      Are you passing some parameter on the URL?

                       

                      • Bhanu Sethi

                        Yes the URL has a parameter, it comes up in the path parameter category as well while setting up the service connection.

                        Also, when I try to hit the URL by passing parameter value within the URL, it returns data as well for that particular record.

                        But I am unable to map to the table as a data source.

                        Below is the syntax depicting the URL in place.

                        http://IP/ords/schemaname/resourcename/{paramtername}

    • Shay Shmeltzer

      In order for the REST endpoint to show up as a possible source for a table, it needs to have the action hint marked as "retrieve many" in its definition.

      https://docs.oracle.com/en/cloud/paas/app-builder-cloud/visual-builder-developer/edit-service-connection.html#GUID-FCBB850D-F1D4-4BB3-B746-878799AB77A0

      service-connections-edit-endpoints.png

      Have a look at this blog/video for some tips on working with the type of service you are accessing:

      https://blogs.oracle.com/shay/working-with-rest-post-and-other-operations-in-visual-builder

       

      • Bhanu Sethi

        Now I have selected the action as Retrieve Many in the service connection.

        But when I choose it as a data source for the table, the proper endpoint structure doesn't load and I am unable to bind the data to the table from this connection.

        Please have a look at the snapshot and advise.

         

    • John Ceccarelli

      OK so if this an ORDS endpoint here is an older example of the transforms javascript to enable filtering, etc. You enter this in the Transforms tab of your service connection.

      //
      // Example code, not for use in production environments.
      //
      define(['urijs/URI'], function (URI) {
          'use strict';
       
          var Request = function () {
          };
       
          /**
           * Filter transform function
           * @param configuration
           * @param options the JSON payload that defines the filterCriteria
           * @returns {object} configuration object the url looks like ?filter=foo eq 'bar'.
           */
          Request.prototype.filter = function(configuration, options) {
              var filterCriterion = options;
              if (filterCriterion === undefined) {
                return configuration;
              }
              var criteria = filterCriterion.criteria;
              if (criteria === undefined) {
                return configuration;
              }
       
              function jetFilterOpToScim(fop) {
                  switch (fop) {
                      case '$eq':
                          return '$eq';
                      case '$co':
                          return '$like';
                      default:
      • John Ceccarelli

        hmmm blog truncated my post... Let me see if we can post a blog on this.

      • Bhanu Sethi

        I have added this in the transforms tab of service connection, still I am not able to filter the data in the table.

        • David Konecny

          Im attaching complete version of the transform script. Could you try it once again please? I wrote this transform for my own ORDS application a while ago and at that time it worked perfectly. Right now I do not have ORDS setup handy to verify it though.

          Thanks,
          -David