Application Developmentpublic

Visual Builder

Get Involved. Join the Conversation.

This is a public Forum  public

Topic

    Stephen Bryant
    Compound Filter Criterion Issue
    Topic posted October 23, 2019 by Stephen BryantBronze Trophy: 5,000+ Points, last edited October 23, 2019, tagged Action Flows, Business Objects, Custom Code, UI 
    12 Views, 7 Comments
    Title:
    Compound Filter Criterion Issue
    Summary:
    The filter criterion works if you provide the first two fields
    Content:

    Hello,

    I have the following 6 fields that are used as filter criteria on an SDP.

    Treasury Symbol, Fund, Doc Num, Status, fromDate, toDate

    Everything works as long as you provide the first two fields (Treasury Symbol, Fund).  It will not work without these two fields.  I am accounting for nulls using a function provided in Andre's blog at: http://andrejusb.blogspot.com/2019/01/search-form-in-oracle-visual-builder.html.  That works fine.

    The network tab in Chrome Tools indicates a bad request.  Here is the error message when providing only the Treasury Symbol (the first field):

    "detail" : "Failed to build ViewCriteria from expression \"((budgetLevelId = '1') and (treasurySymbol = 3) and (fundValue = '') and (docNumber = '') and (docStatus = '') and (transactionDate >= '') and (transactionDate <= ''))\" using view object DynamicResource. Literal value \"\" cannot be compared with attribute \"fundValue\" of type \"java.lang.Long\". ",

     

    Here is the q parameter when the above error ocurred:

    q: ((budgetLevelId = '1') and (treasurySymbol = 3) and (fundValue = '') and (docNumber = '') and (docStatus = '') and (transactionDate >= '') and (transactionDate <= ''))

    As you can see the query parameter looks fine.  One other bit of information is that Treasury Symbol and Fund are both passed as numbers (the id of the value in the table).  budgetLevelId is always provided.  Doc Number and Doc Status are text and the date fields, of course, are dates.  It seems when a null is passed to Treasury Symbol and/or Fund, the problems start.

    I would like to be able to enter one or many fields and bring back results accordingly.

    Any ideas?

    Thanks,

    Steve 

     

     

    Version:
    19.3.1.2

    Comment

     

    • Shay Shmeltzer

      To check that a value is null you need to use {{null}} so something like:

              "filterCriterion": {
                "op": "$eq",
                "attribute": "cost",
                "value": "{{null}}"
              }

       

    • Stephen Bryant

      Shay,

      I am providing values for the six fields based on the UI.  So are you saying to include "{{ null }}" as the default value?

      I tried that but that causes the field to be required.  So does that mean I just require the Treasury Symbol and Fund?  I know that will enforce it but is there a way to say, search just on Treasury Symbol while leaving all the other fields null?  

      Thanks,

      Steve

      • Shay Shmeltzer

        My suggestion is to modify the JS function you are using to put out the correct format for cases where one of the fields is null.

        • Stephen Bryant

          Oh.  Okay I will give it a shot.

           

          Thanks,

          Steve

        • Stephen Bryant

          Shay,

          Please pardon my misunderstanding here.  Here is the function which is inserting nulls as the value per Andre's blog.

           PageModule.prototype.prepareSearchAppropParams = function(q1,q2,q3,q4,q5,q6,q7) {
              
              if(!q1.value) {
                q1.value = '';
              }
              
              if(!q2.value) {
                q2.value = '';
              }

              if(!q3.value) {
                q3.value = '';
              }
              
              if(!q4.value) {
                q4.value = '';
              }
              
              if(!q5.value) {
                q5.value = '';
              }
              
              if(!q6.value) {
                q6.value = '';
              }
              
              if(!q7.value) {
                q7.value = '';
              }

             var res = [q1,q2,q3,q4,q5,q6,q7];
              
              return res;      
              
            };

          I am not quite sure how I would use the {{ null }}.  The function would still require all the parameters.

          Steve

           

          • Shay Shmeltzer

            I guess the first question to ask is what do you want the query to be in cases where you didn't specify a value in a search field?

            Do you want it to be added to the query as ..and (fieldX is null)

            or will you expect it to then not be added to the query.

            Right now it seems that your code result in the first option, but with the wrong syntax - if you want this option you need to generate the right syntax from comparing to null.

            If you want the second behavior you'll need to modify your query construction code to ignore cases where there is no value in the field.

     

    community forum