Loading 3rd CollectionDatasource based on Selected 1st datasource through 2nd composite entity

I have a test project where I have following Entities:

  1. Order (Master) , OrderLine (Deatil - composite)
  2. Product
  3. Production (Master), ProductionLine (detail - composite)

Product is Associated in OrderLine and Production.

In Production UI, user will select Order from LookupField that will refresh Product LookupField be filtering the products according to the Products in OrderLine Entity related to the selected Order.

My question is related to Production user Interface is:

  1. I tried to filter the product combo box using the following query in the DataSource, but it doesn’t work. Is it the right way in the above explained situation?

select a from demodscontext$OrderLine e join 
demodscontext$Product a where e.order.id = :ds$ordersDs.id
  1. Is a DsContext is suggested in datasource listener in the controller to setSQL as per the selected Order DS? If yes, how can I set order as query parameter?

I have attached the project file, thanks for your help.

demo-dscontext.zip (336.0K)

Try using this query:

select p from demodscontext$Product p  
where p in (select ol.product from demodscontext$OrderLine ol where ol.order.id = :ds$ordersDs)

Sorry, I don’t understand the question.

Thank you.

this did the trick

much appreciated!