Skip to main content
Solved

How to pull overridden shipping address to custom GI


Forum|alt.badge.img

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!

 

Best answer by darylbowman

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.

Try this: 

=IsNull([SOAddress.AddressLine1],'')+', '+IsNull([SOAddress.AddressLine2],'')+', '+IsNull([SOAddress.City],'')+', '+IsNull([SOAddress.State],'')+', '+IsNull([SOAddress.CountryID],'')+' '+IsNull([SOAddress.PostalCode],'')

View original
Did this topic help you find an answer to your question?

7 replies

darylbowman
Captain II
Forum|alt.badge.img+13

Use SOAddress instead of SOShipmentAddress


Manikanta Dhulipudi
Captain II
Forum|alt.badge.img+13

Hi ​@jdunmire Attached the GI for your reference.


saifalisabri
Freshman II
Forum|alt.badge.img
  • Freshman II
  • 26 replies
  • November 19, 2024

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

  1. SOShipment: Contains shipment-level details like shipment numbers and associated sales orders.
  2. SOShipmentAddress: Contains the actual shipping address tied to the shipment, including overridden addresses.
  3. SOOrder: Contains the sales order details and references to the shipping address.
  4. SOOrderAddress: Contains the overridden shipping address at the sales order level.

Steps to Pull the Overridden Shipping Address

  1. 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.

    Example Join:

    • SOShipment.ShipAddressIDSOShipmentAddress.AddressID
  2. Include the Correct Address Fields

    • Add the fields from SOShipmentAddress, such as:
      • AddressLine1
      • AddressLine2
      • City
      • State
      • PostalCode
  3. Validate Against the SOOrderAddress (Optional)

    • If you also need to show the overridden address at the Sales Order level, join SOOrderAddress with SOOrder using ShipAddressID.

    Example Join:

    • SOOrder.ShipAddressIDSOOrderAddress.AddressID
  4. 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.

Example Query Configuration

Here’s how your GI table joins might look:

  • Tables:

    • SOShipment
    • SOShipmentAddress
    • SOOrder
    • SOOrderAddress (optional for cross-checking)
  • Joins:

    • SOShipment.ShipAddressID = SOShipmentAddress.AddressID
    • SOOrder.ShipAddressID = SOOrderAddress.AddressID (optional)
  • Columns to Display:

    • SOShipment.ShipmentNbr
    • SOShipment.ShipDate
    • SOShipmentAddress.AddressLine1
    • SOShipmentAddress.City
    • SOShipmentAddress.State
    • SOShipmentAddress.PostalCode
    • 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.

darylbowman
Captain II
Forum|alt.badge.img+13

@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.

See this post.


Forum|alt.badge.img
  • Author
  • Jr Varsity II
  • 16 replies
  • November 19, 2024
Manikanta Dhulipudi wrote:

Hi ​@jdunmire Attached the GI for your reference.

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:

Formula 1:

=CSTR([SOAddress.AddressLine1])+’, ‘+CSTR([SOAddress.AddressLine2])+’, ‘+CSTR([SOAddress.City])+’, ‘+CSTR([SOAddress.State])+’, ‘+CSTR([SOAddress.CountryID])+’ ‘+CSTR([SOAddress.PostalCode])

Formula 2:

=[SOAddress.AddressLine1]+’, ‘[SOAddress.AddressLine2]+’, ‘+[SOAddress.City]+’, ‘+[SOAddress.State]+’, ‘+[SOAddress.CountryID]+’ ‘+[SOAddress.PostalCode]

 

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!

 


darylbowman
Captain II
Forum|alt.badge.img+13
  • 1596 replies
  • Answer
  • November 19, 2024

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.

Try this: 

=IsNull([SOAddress.AddressLine1],'')+', '+IsNull([SOAddress.AddressLine2],'')+', '+IsNull([SOAddress.City],'')+', '+IsNull([SOAddress.State],'')+', '+IsNull([SOAddress.CountryID],'')+' '+IsNull([SOAddress.PostalCode],'')


Forum|alt.badge.img
  • Author
  • Jr Varsity II
  • 16 replies
  • November 19, 2024
darylbowman wrote:

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.

Try this: 

=IsNull([SOAddress.AddressLine1],'')+', '+IsNull([SOAddress.AddressLine2],'')+', '+IsNull([SOAddress.City],'')+', '+IsNull([SOAddress.State],'')+', '+IsNull([SOAddress.CountryID],'')+' '+IsNull([SOAddress.PostalCode],'')

Just tested this out and it works, thank you!


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings