a!queryEntity is returning data on non-existent primary key ID and even on passing primary key value as empty or null

I've created a query entity which retrieves all the table data on the basis of just primary key of the table or entity as a filter. But, the provided a!queryEntity code is returning data on non-existent primary key ID and even on passing primary key value as empty or null as well.

For ex- There are two rows existing in the table with primary key (requestID) values "1" and "2" but if I pass value "3" or something else which is non-existent in table, still the code is returning some random data where my scenario is not to return any data. That's one thing, other is if I pass "requestID" as "NULL" or if it's Empty, the query is returning all rows present in the table, whereas in my code as you can see below I am returning "{}" on blank values of "requestID", which also is my requirement.

What needs to be modified to achieve both the requirements. Attached is the code and screenshots for understanding. Any help appreciated. Thanks 

a!queryEntity(
  entity: cons!OSR_ENTITY_T_REQUEST,
  query: a!query(
    logicalExpression: a!queryLogicalExpression(
      operator: "AND",
      filters: if(
        rule!OSR_RULE_General_APN_isValueBlank(
          ri!requestID
        ),
        {},
        {
          a!queryFilter(
            field: "RequestID",
            operator: "=",
            value: ri!requestID
          )
        }
      )
    ),
    pagingInfo: a!pagingInfo(
      startIndex: 1,
      batchSize: -1,
      sort: a!sortInfo(
        field: "RequestID",
        ascending: true
      )
    )
  ),
  fetchTotalCount: false
)
Returning all rows in table on passing no value or NULL: Returning all rows on NULL or Empty "requestID"                  No data on requestID 3: No data on requestID 3   

Returning some random data on requestID 3 in dashboard: Returning some random data on requestID 3 in dashboard

  Discussion posts and replies are publicly visible

Parents
  • There's two different questions here, so I'll address each of them separately:

    1.Why is it returning all values if your input is null?

    The reason this is happening is that you have an if() statement in the filters section. You're currently saying that if the value is null, then the filters should also be null. Keep in mind though - if your filters are null, that naturally means you want to return all rows! I think instead you should put your if() statement at the very top level around a!queryEntity() instead.

    2. Why is it returning data when you provide an ID that doesn't exist?

    If you look closely at the output, you will see that there are several properties returned. a!queryEntity() always returns additional metadata about queries such as the batch size, start index, sorting, etc. even if there are no rows returned. If you look at the parameter for "data", you will see that there are 0 items returned, which is what I would expect from this query. If you want to get to the data, you might need to use dot notation to find the corresponding property like this: a!queryEntity(...).data

  •  For the 2nd question, when I query data normally, it's correct that it's returning 0 items but when it comes to showing those 0 items or data on dashboard, it's showing some random data for "Service Type" and "Supply Type" for ID 3 which doesn't even exist? Ref: Image 3

  • Do you have any other logic in that interface that might return a text string even if there is no data? For example, suppose you have an if statement in your display field for service type like this:

    a!textField(
      label: "Service Type",
      readOnly: true,
      value: if(
        local!request.serviceType = 1,
        "Hardware Installation",
        "Software Installation"
      )
    )

    In this example, it will display "Software Installation" even though no data is returned because the if() condition returns false.

Reply
  • Do you have any other logic in that interface that might return a text string even if there is no data? For example, suppose you have an if statement in your display field for service type like this:

    a!textField(
      label: "Service Type",
      readOnly: true,
      value: if(
        local!request.serviceType = 1,
        "Hardware Installation",
        "Software Installation"
      )
    )

    In this example, it will display "Software Installation" even though no data is returned because the if() condition returns false.

Children