BULK UPDATE OF SALES UNIT and SALES District for customer

(1) Share

Report

Posted on by Ayushaman

275

Hi Everyone!

I am seeking assistance to perform a bulk update of the sales district and sales unit for customers within the CustGroup 'XYZ'(Suppose) using SQL. The update should be executed under the following conditions: the CustGroup must be 'XYZ', the customer delivery address should be primary, and based on the delivery address state, the customer's sales unit and district should be updated accordingly.

I have attempted to fetch the necessary data but am encountering issues with the joins and possibly the table selection. The query I have constructed is as follows:

[SELECT CT.AccountNum, CT.CUSTGROUP, CT.SALESDISTRICTID, CT.ISALESUNITID, DPL.ISPRIMARY
FROM CustTable CT
JOIN DIRPARTYTABLE DPT ON CT.PARTY = DPT.RECID
JOIN DIRPARTYLOCATION DPL ON DPT.RECID = DPL.PARTY
JOIN LOGISTICSADDRESSCOUNTRYREGION LACR ON CT.PARTYCOUNTRY = LACR.COUNTRYREGIONID
INNER JOIN LOGISTICSPOSTALADDRESS LPA ON LACR.COUNTRYREGIONID = LPA.COUNTRYREGIONID
INNER JOIN LogisticsAddressDistrict LAD ON LPA.DISTRICTNAME = LAD.NAME AND LPA.DISTRICT = LAD.RECID
WHERE CT.CustGroup = 'WTD'
AND DPL.ISPRIMARY = 1

]

The update will be carried out in SQL. The relationships I have established were derived from my AX2012 system, and I am attempting to perform the bulk update accordingly. Thank you in advance for your help, and please feel free to request any additional details you may require.

All responses (8)

Answers (1)

Sort by

André Arnaud de Cal... 307,786 Super User 2026 Season 2 on at

Like (0)

Report
Copy link

Link copied!

Hi Ayushaman,

Can you tell us what issues you are encountering, do you get an error, too many, or not all relevant records in the result set? What exact version of AX 2012 are you using?
Ayushaman 275 on at

Like (0)

Report
Copy link

Link copied!

Hi Andre,

When i am joining LOGISTICSADDRESSCOUNTRYREGION ,LOGISTICSPOSTALADDRESS ,LogisticsAddressDistrict I am not getting any data zero records joining till DIRPARTYLOCATION is working fine. I am getting desired record but for updating I need primary delivery address state of customers to be fetched based on that I will update SalesUnit and Sales District .The version I am using is AX2012 R3.the query I am running in SQL server but the joining relation I retrieved it from AX itself. Basically we need to bulk update from backend based on the condition I earlier told you .

Thanks,

Ayushaman
Verified answer

André Arnaud de Cal... 307,786 Super User 2026 Season 2 on at

Like (1)

Report
Copy link

Link copied!

Hi Ayushaman,

I think you will need to change your query. A country is not used to find an address in the AX application.

The DirPartyLocation.Location field has a relation to the table LogisticsLocation on the RecId field. This table has a field to indicate if a location is a postal address. If so, you can use the Location field or RecId from the LogisticsLocation table to find a record in the table LogisticsPostalAddress.
Ayushaman 275 on at

Like (0)

Report
Copy link

Link copied!

Thanks a bunch Andre!! I got the result as expected.
André Arnaud de Cal... 307,786 Super User 2026 Season 2 on at

Like (0)

Report
Copy link

Link copied!

Hi Ayushaman,

If your question has been answered, would you mind marking the reply or replies that helped you? The status of the question will then be set to Answered which is helpful for other persons if they have the same question in future.
Suggested answer

Ayushaman 275 on at

Like (0)

Report
Copy link

Link copied!

Joining
CustTable CT WITH (NOLOCK)
JOIN DIRPARTYTABLE DPT WITH (NOLOCK) ON CT.PARTY = DPT.RECID
JOIN DIRPARTYLOCATION DPL WITH (NOLOCK) ON DPT.RECID = DPL.PARTY
JOIN LOGISTICSLOCATION LL WITH (NOLOCK) ON LL.RECID = DPL.LOCATION
JOIN LOGISTICSPOSTALADDRESS LPA WITH (NOLOCK) ON LPA.LOCATION = LL.RECID

Thanks

Ayushaman
Suggested answer

Ayushaman 275 on at

Like (0)

Report
Copy link

Link copied!

Hi Andre,

Apologies for the delayed response I already tried to mark your reply as answered but it's not allowing me when I click on does this answer you question checkbox. If you know any other way around kindly let me know and I will mark it right away .

Thanks

Ayushaman
André Arnaud de Cal... 307,786 Super User 2026 Season 2 on at

Like (0)

Report
Copy link

Link copied!

Thanks for the update, Ayushaman. I was able to mark the reply. For the next question please try again marking a reply. There was an issue in the past, but I don't know if there is still a small error related to marking replies for some users.