I have an external view (vw_PatientProducts) that, by way of the query 'Patient Products' inside my application provides me a list of ProductID's which are eligible to add to an OrderCustomer in the form of OrderDetail objects. I would like to do something like what is in the Process Rule below. OrderCustomer is the only object passed to the Process.
FIND Person WHERE Person=OrderCustomer.Person
PICK ONE OR MORE FROM 'Patient Products'
FIND Product WHERE Product.ID=vw_PatientProducts.ProductID
CREATE OrderDetail FOR EACH Product WITH OrderDetail.Product=Product
INSERT OrderDetail IN OrderCustomer.OrderDetails
This functions, but the "FIND Product" line only places one Product into context, though I can verify that I have more vw_PatientProducts in context.
If the following were allowed, or some other looping structure I think I would have what I need:
FIND Product FOR EACH vw_PatientProducts WHERE Product.ID=vw_PatientProducts.ProductID, but this is illegal of course.
Alternatively, if you have a way to implement this query inside AwareIM,
SELECT DISTINCT Person.ID PersonID, RecommendationDetail.Product_RID ProductID, Product.SKU, Product.Description
FROM basdb.l3wbchem01_Person Person
JOIN basdb.l3wbchem01_Recommendation Recommendation ON (Recommendation.Person_RID=Person.ID)
JOIN basdb.l3wbchem01_RecommendationDetail RecommendationDetail ON (RecommendationDetail.Recommendation_RID=Recommendation.ID)
JOIN basdb.l3wbchem01_Product Product ON (Product.ID=RecommendationDetail.Product_RID) WHERE Person.ID=?
ORDER BY Product.Description;
I would be happy to adapt to that approach.
Thanks for any ideas.
