Can't expand Items with Orders/DocumentLines

[mkraus92]

Hello Community,

i am trying to get all Items that havent been ordered. I thought about expanding Items with Orders/DocumentLines and filtering the Items with Lines.

I tried using $expand Items with Orders/DocumentLines but am getting an Error.

Expanding Items with ItemGroups does work.

/b1s/v1/Items?$expand=ItemGroups&$select=ItemCode,ItemName,ItemGroups/GroupName,Properties1,ItemWarehouseInfoCollection

/b1s/v1/Items?$expand=Orders/DocumentLines&$select=ItemCode,ItemName,Orders/DocumentLines/DocEntry,Properties1,ItemWarehouseInfoCollection

We believe this is because of a wrong Implementation of DocumentLines as it should be a seperate Entity like ItemGroups?

Thanks in advance for your help!

[former_member197733]

Are you trying to get the Items that have orders or the ones that don't?

If the first, based on your other question, I could come up with this:

// Items that have been ordered (QuantityOrderedByCustomers != 0)and their respective order lines
$crossjoin(Orders,Orders/DocumentLines,Items)?$expand=Orders($select=DocNum),Orders/DocumentLines($select=ItemCode),Items($select=ItemCode,ItemName,QuantityOrderedByCustomers)&$filter=Orders/DocEntry eq Orders/DocumentLines/DocEntry and Orders/DocumentLines/ItemCode eq Items/ItemCode and Items/QuantityOrderedByCustomers ne 0

But if your case is the second:

/b1s/v1/Items?$filter=QuantityOrderedByCustomers eq 0

[mkraus92]

I am trying to get the Items a specific Businesspartner has not ordered yet.

For better clarification:

I have 7 Items, each Item can only be ordered once per Customer.

Customer has not ordered anything yet, so i display all 7 Items. He orderes Item 1 and 2.

Next time the Customer wants to see the Items he can order i only show him Items 3-7.

[former_member197733]

Oh I see, you didn't mentioned the BP before. I don't think this association is possible.

This kind of logic is better to be applied in the DB level rather than in the middleware (SL).

If your queries are getting much complex, follow Trinidad's answer here and create a view.