Crossjoin to exclude items

[mkraus92]

Hello Community,

i am trying to build a Request to get Items not yet ordered by a Businesspartner.

At the Moment i can get all Items Businesspartner '10211' has ordered via the Request:

/b1s/v1/$crossjoin
(Orders,Orders/DocumentLines,BusinessPartners,Items,ItemGroups)
?$expand=Orders($select=DocNum),
Orders/DocumentLines($select=ItemCode),
BusinessPartners($select=CardCode),
Items($select=ItemName),
ItemGroups($select=GroupName)&
$filter=Orders/DocEntry eq Orders/DocumentLines/DocEntry and 
Orders/CardCode eq BusinessPartners/CardCode and 
Orders/DocumentLines/ItemCode eq Items/ItemCode and 
Items/ItemsGroupCode eq  ItemGroups/Number and 
BusinessPartners/CardCode eq '10211'

Now i want to exclude these Items from the List of all Items is have.

Is guess it is not possible to set this Request as a filter so i can invoke:

/b1s/v1/Items&$filter=ItemCode ne (Request1)

Surely there has to be a way to do this in only one Request?

Is it possible to do a Left Join in B1 Service Layer?

Thank you for your Help.

[Trinidad · Accepted solution]

Hi Manuel,

I don't see an easy way to implement this query on top of Service Layer.

But I think a solution would be to create a view in HANA getting the results you are searching for (with some parameters if required) and call that view with the Service Layer Semantic Layer feature allowing you to execute views. Please check the SL user manual for more details on the SL semantic layer feature.

Regards,
Trinidad.

[former_member197733 · Accepted solution]

Just adding a Blog about Semantic Layers consumption via Service Layer