Get Even More Visitors To Your Blog, Upgrade To A Business Listing >>

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:

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.



This post first appeared on Web Service Testing A Blackjack GUI And API, please read the originial post: here

Share the post

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

×

Subscribe to Web Service Testing A Blackjack Gui And Api

Get updates delivered right to your inbox!

Thank you for your subscription

×