Virtual Happy Hour this month, Jun 28, we'll be getting a sneak preview at our soon to launch SaaS CMS!

Try our conversational search powered by Generative AI!

Querying ShipmentEx table and tracing Order back to user account with SQL

Vote:
 

Hello,

I have a table "ShipmentEx" that links to some orders that have ERP and Tax stuff, but I can't see how I can link it to get the full order information.ShipmentEx cannot link to Shipment, OrderGroup, or OrderForm tables because they all require OrderGroupId, which isn't available in the ShipmentEx table. From a row in ShipmentEx, I want to be able to pull the full order and user account details. Can someone provide clarity on this?

Edit: I was able to query orders with customer information, with OrderGroup -> LineItem -> Shipment tables, but linking to this table is essential.

SELECT [ObjectId]
      ,[CreatorId]
      ,[Created]
      ,[ModifierId]
      ,[Modified]
      ,[PrevStatus]
      ,[ERPOrderNo]
      ,[StatusExt]
      ,[TaxDocCode]
      ,[ShipmentTax]
      ,[ShipmentTaxStatus]
      ,[ShippingRateTable]
      ,[ShippingGroup]
      ,[HandlingCharge]
  FROM [elad01mstrfg1t0prod.commerce].[dbo].[ShipmentEx]
#315814
Edited, Jan 18, 2024 17:05
Vote:
 

Not sure if I understand you correctly but

LineItemEx (ObjectId) is essentially LineItem (LineItemId), from there you get OrderGroupId 

#315855
Jan 19, 2024 7:57
* 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.