Seperating Shipping address from Invoice Table

[Former Member]

Ship Address gets stored in one field address in OINV or ORDR Table

any way to link that to master Address table to get address separated by address1,address2,city,state and zip

or any other thoughts

[former_member583013 · Accepted solution]

Absolutely,

The example below will give you the link between the Sales Order Shipping Address and and BP Master Address table

SELECT DISTINCT T1.Address AS 'Address Name', T1.Street AS 'Street', T1.Block AS 'Block', 
T1.City AS 'City', T1.State AS 'State', T1.ZipCode AS 'Zip Code', T1.CardCode AS 'BP Code' 
FROM  [dbo].[ORDR] T0 INNER JOIN [dbo].[CRD1] T1  ON T0.CardCode = T1.CardCode
WHERE T0.ShipToCode = T1.Address  AND  T1.AdresType = 'S'   

[Former Member · Accepted solution]

This query will pull the address components from crd1 as they exists at the time the query is run, not when the order/invoice was posted. Just a warning.

Since there is nothing to stop a user from editing an existing address, this query could returned a parsed address that is different in every respect except address name and address type from the address that was actually used for the order or invoice on the logistics tab.

If what you really need is the parsed out address from the logistics tab, you have some work ahead of you. I've got a rudimentary parser that I have written, and it works fairly well for US addresses, but for international addresses, due to differences in logistics tab address formats, it breaks down.

[Former Member · Accepted solution]

can you Please post here

[Former Member]

Do you want these addresses beside your sales report?

Thanks,

Gordon