Dynamics F/SCM Customer Experience (CRM) Dynamics GP Dynamics Business Central (NAV) Dynamics SL Power Platform Fabric Copilot

[Alexis Barrios]

How can I get a list of Customer Ship to Locations including addresses?

——————————
Alexis Barrios
Fyffes Group Ltd
Pompano Beach FL
——————————

[Barclay Hershey]

Hi Alexis,
Here is some code I whipped up when I had to extract all the customers and currently valid addresses.  Once you run this, you can filter on IsRoleDelivery = 1 to get only the addresses that are Ship-To.

Select CT.Partition, CT.DataAreaId, CT.AccountNum
, CT.Party, CT.CompanyChainId
, DPT.Name As DPTName
, DPL1.Location
, DPL1.IsPrimary
, DPL1.IsPrivate
, DPL1.IsLocationOwner
, DPL1.IsPrimaryTaxRegistration
, DPL1.IsRoleBusiness
, DPL1.IsRoleDelivery
, DPL1.IsRoleHome
, DPL1.IsRoleInvoice
, DPL1.AttentionToAddressLine
, LLLPA.Description
, LLLPA.IsPostalAddress
, LLLPA.ParentLocation
, LLLPA.DUNSNumberRecId
, LLLPA.ValidFrom
, LLLPA.CountryRegionId
, LLLPA.State
, LLLPA.County
, LLLPA.City
, LLLPA.ZipCode
, LLLPA.Street

From CustTable As CT
Left Join DirPartyTable As DPT
On CT.Party = DPT.RecId
Left Join (
Select DPL.Partition, DPL.Party, DPL.Location
, DPL.IsPrimary, DPL.IsPrivate, DPL.IsLocationOwner, DPL.IsPrimaryTaxRegistration
, DPL.IsRoleBusiness, DPL.IsRoleDelivery, DPL.IsRoleHome, DPL.IsRoleInvoice
, DPL.AttentionToAddressLine
From DirPartyLocation As DPL

) As DPL1
On DPT.Partition = DPL1.Partition
And DPT.RecId = DPL1.Party
Inner Join (
/* LogisticsLocation has the Location Description
LPA has the postal address */
Select LL1.Partition, LL1.LocationId, LL1.RecId
, LL1.Description, LL1.IsPostalAddress, LL1.ParentLocation, LL1.DUNSNumberRecId
, LPA1.ValidFrom, LPA1.CountryRegionId, LPA1.State, LPA1.County, LPA1.City, LPA1.ZipCode, LPA1.Street
From LogisticsLocation As LL1
Inner Join (
Select LPA.Partition, LPA.Location, LPA.ValidFrom
, CountryRegionId, State, County, City, ZipCode, Street
From LogisticsPostalAddress As LPA
Where LPA.ValidFrom <= GetDate()
And ( LPA.ValidTo = 0 Or LPA.ValidTo >= GetDate() )
) As LPA1
On LL1.Partition = LPA1.Partition
And LL1.RecId = LPA1.Location
) As LLLPA
On DPL1.Partition = LLLPA.Partition
And DPL1.Location = LLLPA.RecId
Left Join CountryCode
On LLLPA.CountryRegionId = CountryCode.CountryRegionId
Order By CT.AccountNum Asc, DPL1.IsPrimary Desc, DPL1.Location Asc

——————————
Barclay Hershey
Financial Analyst
Sugar Creek Packing Co
Cincinnati OH
——————————
——————————————-

[Alexis Barrios]

Thanks Mr. Hershey

 

This is very complicated for me.  I was trying to find an out of the box report like the Customer base data report which only shows the main information

 

——————————————-