AI OnAI Off
The easiet way would to be to create your own data method to the ordergroupid from the shipment table based on the shipmentid. Then you would pass the ordergroupid to OrderContext.GetPurchaseOrder
private static int GetOrderGroupIdByShipmentId(int shipmentId) { int orderGroupId = 0; using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["EcfSqlConnection"].ConnectionString)) { SqlCommand cmd = new SqlCommand(); cmd.CommandText = "Select OrderGroupId from Shipment where ShipmentId = @ShipmentId"; cmd.CommandType = CommandType.Text; cmd.Parameters.Add(new SqlParameter("@ShipmentId", shipmentId)); conn.Open(); cmd.Connection = conn; orderGroupId = Convert.ToInt32(cmd.ExecuteScalar()); } return orderGroupId; }
If you want to use the api, then you could you use order search
private static PurchaseOrder GetOrderByShipmentId(int shipmentId) { OrderSearchOptions options = new OrderSearchOptions(); options.Classes.Add("PurchaseOrder"); options.RecordsToRetrieve = 1; OrderSearchParameters parameters = new OrderSearchParameters(); parameters.JoinType = "inner join"; parameters.JoinSourceTable = "OrderGroup"; parameters.JoinSourceTableKey = "OrderGroupId"; parameters.JoinTargetQuery = "Shipment"; parameters.JoinTargetTableKey = "OrderGroupId"; parameters.SqlWhereClause = String.Format("Shipment.ShipmentId = {0}", shipmentId.ToString()); return OrderContext.Current.FindPurchaseOrders(parameters, options).FirstOrDefault(); }
; parameters.SqlWhereClause = String.Format(
That is just perfect Mark.
I prefer the API. I actually didn't realize that you could do table join in the OrderSearchParameters.
Ok tried solution number 2 but got the following exception:
The multi-part identifier "Shipment.ShipmentId" could not be bound.
Iterating through meta classes
Metaclass Table: OrderGroup_PurchaseOrder
So i removed the mark as answer
Sorry you can only join on CatalogEntry Search. Try this
private static PurchaseOrder GetOrderByShipmentId(int shipmentId) { OrderSearchOptions options = new OrderSearchOptions(); options.Classes.Add("PurchaseOrder"); options.RecordsToRetrieve = 1; OrderSearchParameters parameters = new OrderSearchParameters(); parameters.SqlWhereClause = String.Format("OrderGroup.OrderGroupId = (Select OrderGroupId from Shipment where ShipmentId = {0})", shipmentId.ToString()); return OrderContext.Current.FindPurchaseOrders(parameters, options).FirstOrDefault(); }
Is it possible to through the API to retrieve a PurchaseOrder when you only have the Shipment id?