How to pull overridden shipping address to custom GI
Hello!
I am creating a custom GI that aims to pull the Shipment Details by a given date. I want to show what inventory ID shipped out on what shipment along with the qty shipped and the shipping address. However, I am facing some trouble when I try to pull the shipping address onto the GI.
I was able to join the SOOrder and SOShipmentAddress tables by joining the SOOrder.shipAddressID and SOShipmentAddress.AddressID fields (See below).
However, it seems to pull the shipping address from the customer rather than the shipping address that is on the Sales Order or Shipment.
We typically have to override the shipping address when entering a Sales Order, as we are in a business where a customer has many open job sites / warehouses to ship to.
Does anyone know how we can get around the override and have the user-entered Ship-To address to come up in the GI?
Thank you!
Page 1 / 1
Use SOAddress instead of SOShipmentAddress
Hi @jdunmire Attached the GI for your reference.
Added by moderator: This reply has been created with the help of AI
To correctly display the overridden shipping address in your custom Generic Inquiry (GI), ensure that the data pulled reflects the actual shipping address as captured in the overridden Sales Order or Shipment. The default join may prioritize the customer's default shipping address rather than the specific one overridden at the document level.
Key Tables and Relationships
SOShipment: Contains shipment-level details like shipment numbers and associated sales orders.
SOShipmentAddress: Contains the actual shipping address tied to the shipment, including overridden addresses.
SOOrder: Contains the sales order details and references to the shipping address.
SOOrderAddress: Contains the overridden shipping address at the sales order level.
Steps to Pull the Overridden Shipping Address
Join the Correct Address Table
In your GI, ensure you join the SOShipmentAddress table to the SOShipment table using the ShipAddressID field. This ensures that the address comes from the shipment record, which includes overrides if applicable.
If you also need to show the overridden address at the Sales Order level, join SOOrderAddress with SOOrder using ShipAddressID.
Example Join:
SOOrder.ShipAddressID → SOOrderAddress.AddressID
Ensure Proper Filters and Selection
Use a filter to restrict your GI to the desired date range or shipment numbers, ensuring you're pulling accurate data for the shipping address used at the time of the shipment.
InventoryID (from shipment or sales order details)
ShippedQty
Key Considerations
Address Source Priority:
The SOShipmentAddress table reflects the actual shipping address for the shipment. It takes precedence over the address in the sales order when generating a shipment.
Avoid Customer Address:
The customer’s default shipping address is stored in the Address table, but it is not tied to overridden addresses on specific sales orders or shipments. Ensure you’re using the ShipAddressID field to avoid default customer addresses.
Test the Results:
Validate your GI by creating test shipments with overridden addresses and confirming that the addresses displayed match the actual shipment details.
@saifalisabri Could you confirm what version of Acumatica contains a DAC called SOOrderAddress?
I suspect you used an LLM to generate your answer, and while it's very thorough, I don't think it's correct.
I was able to use this GI to help with understanding the reasoning as to why the Overridden Ship-To Address wasn’t pulling to the GI. I did find something weird though...when you write a formula to join all the information together into one column it seems that it doesn’t recognize the overridden address on the Sales Order.
For instance, I used both of these codes to test it out:
Both of these formulas seem to ignore the overridden address on the Sales Order regardless if I join the SOOrder and SOAddress tables by using the relation shipAddressID → AddressID. However, when I separate them into different columns they seem to recognize the overridden address.
Does anyone know how to fix this? See below pic for more details. The customer for the highlighted shipment 001703 does not have a general “Ship-To” address on their customer profile as their jobs are a site by site basis and Ship-To addresses are always changing. (The “Shipping Address” column uses the formulas shown above, whereas the individual columns pull the overridden Ship-To address that is on the SO)
Another odd thing that I found was that it seems to work sometimes for past shipments and other times it won’t pull the overridden address into the formula. Could it be that there needs to be a shipping address within the customer profile first, in order for it to recognize the overridden Ship-To address on the SO? Maybe it has an issue with recognizing an overridden address that comes first comes from blanks for customers within the system that don’t have a generic shipping address within the customer profile. See below, in shipment 001673 shows an overridden address from a customer that has a Ship-To address on the profile and the formula works properly by joining all the address info.
Thank you!
I think the real problem is much simpler than that. You’ll notice that the address that does work has a value for each field (ie: Line 1, Line 2, City, State, Country, Postal Code). You’ll also notice that the one that doesn’t work doesn’t have a Line 2 (which is usually common).
When you concatenate strings, if any strings are null and aren’t null-handled, it will break the formula.
I think the real problem is much simpler than that. You’ll notice that the address that does work has a value for each field (ie: Line 1, Line 2, City, State, Country, Postal Code). You’ll also notice that the one that doesn’t work doesn’t have a Line 2 (which is usually common).
When you concatenate strings, if any strings are null and aren’t null-handled, it will break the formula.
