Get PurchaseOrder from Shipment id

Vote:
 

Is it possible to through the API to retrieve a PurchaseOrder when you only have the Shipment id?

#61983
Oct 09, 2012 12:18
Vote:
 

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(

#62009
Edited, Oct 10, 2012 9:05
Vote:
 

That is just perfect Mark. 

I prefer the API. I actually didn't realize that you could do table join in the OrderSearchParameters.  

#62010
Oct 10, 2012 9:22
Vote:
 

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

#62139
Oct 15, 2012 15:38
Vote:
 

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();

}

    

#62160
Oct 16, 2012 9:43
* You are NOT allowed to include any hyperlinks in the post because your account hasn't associated to your company. User profile should be updated.