SAP Business One Service Layer: Enable row level filter on document lines.

Since SAP Business One 9.3 PL01, version for SAP HANA, Service Layer allows you to do row level

filter (e.g. document line filter).

To fully comply with OData, Service Layer exposes a new query service for the row level filter, which

is implemented based on the $crossjoin capabilities by separating

the QueryPath and QueryOption in the query URL.

1 Metadata for query service

Query Service is exposed in the manner of FunctionImport in the following way:

<FunctionImport Name="QueryService_PostQuery" ReturnType="Edm.String" m:HttpMethod="POST">
    <Parameter Name="QueryOption" Type="Edm.String"/>
    <Parameter Name="QueryPath" Type="Edm.String"/>
</FunctionImport>

2 Examples for query service

[Filter on joining document header and document line]

Such a request as below

POST /b1s/v1/QueryService_PostQuery
{
  "QueryPath": "$crossjoin(Orders,Orders/DocumentLines)", 
  "QueryOption": "$expand=Orders($select=DocEntry, DocNum),Orders/DocumentLines($select=ItemCode,LineNum)&$filter=Orders/DocEntry eq Orders/DocumentLines/DocEntry and Orders/DocEntry ge 3 and Orders/DocumentLines/LineNum eq 0" 
}

results in

{
   "odata.metadata" : "$metadata#Collection(Edm.ComplexType)",
   "value" : [
      {
         "Orders" : {
            "DocEntry" : 9,
            "DocNum" : 5
         },
         "Orders/DocumentLines" : {
            "ItemCode" : "i1",
            "LineNum" : 0
         }
      },
      {
         "Orders" : {
            "DocEntry" : 12,
            "DocNum" : 6
         },
         "Orders/DocumentLines" : {
            "ItemCode" : "i1",
            "LineNum" : 0
         }
      },
      ...
      {
         "Orders" : {
            "DocEntry" : 20,
            "DocNum" : 12
         },
         "Orders/DocumentLines" : {
            "ItemCode" : "i1",
            "LineNum" : 0
         }
      },
      {
         "Orders" : {
            "DocEntry" : 44,
            "DocNum" : 22
         },
         "Orders/DocumentLines" : {
            "ItemCode" : "i1",
            "LineNum" : 0
         }
      }
   ]
}

[ Filter on joining document header and document line with parenthesis ]

Such a request as below

POST /b1s/v1/QueryService_PostQuery

{ 
  "QueryPath":"$crossjoin(Orders,Orders/DocumentLines)", 
  "QueryOption":"$expand=Orders($select=DocEntry, DocNum),Orders/DocumentLines($select=ItemCode,LineNum)&$filter=Orders/DocEntry eq Orders/DocumentLines/DocEntry and (Orders/DocumentLines/LineNum eq 0 or Orders/DocumentLines/LineNum eq 1 or Orders/DocumentLines/LineNum eq 2)" 
}

results in

{
   "odata.metadata" : "$metadata#Collection(Edm.ComplexType)",
   "value" : [
      {
         "Orders" : {
            "DocEntry" : 9,
            "DocNum" : 5
         },
         "Orders/DocumentLines" : {
            "ItemCode" : "i1",
            "LineNum" : 0
         }
      },
      {
         "Orders" : {
            "DocEntry" : 3,
            "DocNum" : 1
         },
         "Orders/DocumentLines" : {
            "ItemCode" : "i1",
            "LineNum" : 0
         }
      },
      ...
      {
         "Orders" : {
            "DocEntry" : 28,
            "DocNum" : 17
         },
         "Orders/DocumentLines" : {
            "ItemCode" : "i2",
            "LineNum" : 1
         }
      },
      {
         "Orders" : {
            "DocEntry" : 44,
            "DocNum" : 22
         },
         "Orders/DocumentLines" : {
            "ItemCode" : "i2",
            "LineNum" : 1
         }
      }
   ]
}

[Note]

The response is a raw string with the same structure as JSON and the content-type is text/plain.

Some JSON utility libraries can be used to convert the response to a valid JSON structure to

analyze.