Delivery/Invoice address problem

(0) Share

Report

Posted on by Jørgen Bakke Eriksen

Hi.

We have a Sales order view containing order and order lines. We have a problem with zipcode beeing populated in both zip code (Postnummer) and City (Poststed). You can see the problem in the picture below. The delivery address (Leveringsadresse) is also wrong.

I have been able to find this corrupted date in the CUSTINVOICEJOUR table and have tried to update the rows for this invoiceaccount/customer with correct data using an SQL-statement in MS SQL Server Manager Studio:

begin

transaction

update

CUSTINVOICEJOUR set DELIVERYCITY = INVOICECITY where INVOICEACCOUNT = 'xxxxx' and DELIVERYCITY =

'3702'

commit

transaction

The problem is that the view in Dynamics is not updated.

So my question is whether I am updating the correct table.

Thanks!

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (0)

Mohamed Amine HAMDAOUI 540 on at

Like (0)

Report

Hello,

First, it is not recommanded to use changes directly in the SQL-Server DB because It can cause lost of integrity. Instead, You can use jobs in dynamics Ax.

The table you modified is Invoice not Sales Order, there is the correspondence :

Sales Order == SALESTABLE

Sales Lines == SALESLINE

Invoice header == CUSTINVOICEJOUR

Invoice Line == CUSTINVOICETRANS

Packing slip header == CUSTPACKINGSLIPJOUR

Packing slip lines == CUSTPACKINGSLIPTRANS

Hope it will help.

Mohamed Amine

Was this reply helpful? Yes No
Jørgen Eriksen 105 on at

Like (0)

Report

Thanks for the reply!

The Salestable was the correct table. But do you (or anyone else) know where the address data is comming from? Is there a source table or tables that the deliveryadress and deliverycity to the salestable/salesline? This is maybe done programmatically during sales order creation.

In the salestable table deliveryzipcode and deliverycity contain the same value: a zipcode. I'm trying to figure out what the source of this error is.

We are importing and creating new sales orders from an Excel sheet which doesn't contain address info. So there has to be incorrect data in some table(s) or the import routine.

Was this reply helpful? Yes No
Amir Nazim 5,994 on at

Like (0)

Report

I think the AX uses the address information from GAB (Global address book ) . it allows the sharing of contacts/customers/vendors across all the companies of the organization. GAB can be accessed from the “Basic” navigation pane of the company. i dont know much about the GAB tables but you can start from DirPartyTable (master table for Global Address Book).

other option is if you have cross references updated then you can search for the reference of DeleveryAddress field of SalesTable to find the place from where it is poping up.

Thanks

Amir

Was this reply helpful? Yes No
Mohamed Amine HAMDAOUI 540 on at

Like (0)

Report
Hi,

It depend on the way you configure your AX. These informations may come from :

CUSTTABLE : verify the fields : Address, ZipCode, County, CountryRegionId, State, Street, City

ADDRESS : find the record wich have the incorrect city and correct It.

It may be necessary to correct the city in the table ADDRESSZIPCODE : search the Zip code 3702 and correct the city if incorrect.

Hope it helps

Mohamed Amine

Was this reply helpful? Yes No
Jørgen Eriksen 105 on at

Like (0)

Report
Both CUSTTABLE and ADDRESS contains correct data, that is, I am not able to find entries in those tables where zipcode-values are put into city column.

DeliveryZipcode = DeliveryCity on the tables

SalesLine

SalesTable

CustInvoiceJour

I am tempted to just update DeliveryCity in those tables since I am suspecting that the import routine is the one causing erronous data in the tables. It could be that the importroutine re-uses a variable for populating deliveryname, deliveryzipcode, deliverycity and so on. And if no value for deliverycity is found for some reason (ambiguous lookup from zipcode f.ex) then the deliveyzipcode could be put into the deliverycity f.ex.

I think it's hard to "navigate" the data model but got some help from the AOT tool. I was able to find a relationship between the DirPartyTable and Address table on the recid column. What I discovered was that correct sales orders refer to the CUSTTABLE in the addressreftableid while erronous sales orders refered to the salestable.

I think I will have to dig up the import routine and check what happens there since I am not able to find any errors in the stored data other that deliverycity in the 3 tables mentioned above.

Was this reply helpful? Yes No
GeorgeC 1,865 on at

Like (0)

Report

I agree, it looks like something's wrong with the import routine.

In native AX, as far as I know, the delivery address is actually copied to the sales order header (and lines). In addition, there's a link in the SalesTable.AddressRefTableId that you pointed out - although I am not sure of its purpose.

In native AX, it is perfectly normal to have valid sales order records with AddressRefTableId pointing to the SalesTable. You can replicate this by manually creating a sales order, then go to the Setup button, select Alt. address and then select the Sales order tab in the pop-up form and copy the delivery address from another sales order. You will see that the AddressRefTableId is now pointing to the SalesTable.

The important question in your case is: what's the business logic to populate the delivery address when importing a new order from Excel?

You said that there's no address data in the Excel file. So you must be copying the address from some AX table or re-using AX logic to populate the field. The default option in AX is to look for existing record in the Address table for the same customer (so Address.AddrTableId == 77 and use record ID to find the address for the customer) with Type = Delivery. AX can also use Type = Blank to find a delivery address for a sales order.

You said that all your wrong records have addresses linked to the SalesTable. So maybe AX cannot find a valid address for a customer and is copying it from another sales order with th wrong city value?

Another point: the address on the sales order lines can be more important than the one on the sales order header so make sure you get the correct address there as well.

George

Was this reply helpful? Yes No
Jørgen Eriksen 105 on at

Like (0)

Report

Problem probably solved. The hired external resource who wrote the import routine has mixed up the variables. #&%!!!!!

axSalesTable.Call("parmDeliveryZipCode", _deliveryCity)

axSalesTable.Call("parmDeliveryCity", _deliveryZipCode)

I don't have the possibility to compile and test code, but will get it done tomorrow. Stop twisting your heads :-)

Was this reply helpful? Yes No
Jørgen Eriksen 105 on at

Like (0)

Report

Ok, I fixed the import making sure that new sales orders for most customers now is correct. We still have problems with a lot of old adresses beeing incorrect and I am back in "frustration mode".

We still have problem with a lot of the old adresses beeing incorrect which results in incorrect invoicingaddress i the invoice, the red marked address in the picture below. And I not able to change what is shown in this field.

I have updated the following fields in the following tables:

ADDRESS.ADDRESS

ADDRESS.STREET

CUSTTABLE.ADDRESS

CUSTTABLE.STREET

SALESTABLE.DELIVERYADDRESS

SALESTABLE.DELIVERYSTREET

SALESLINE.DELIVERYADDRESS

SALESLINE.DELIVERYSTREET

CUSTINVOICEJOUR. INVOICINGADDRESS

CUSTINVOICEJOUR. INVOICESTREET

CUSTINVOICEJOUR. DELIVERYSTREET

CUSTINVOICEJOUR. DELIVERYADDRESS

Actually I have made changes to all these fields so that no one contains the address that is actually shown in the invoice, in other words: there are no current or historical information containing the address that is beeing used in the invoice. So there has to some other address field that is used in our (corrupted) database.

I have also investigate the AOT > Reports > Sales Invoice report in order to try to edit the fields shown in the invoice. I am not able to manipulate any fields in the red part of the above picture - have been adding fields (New control) to the PageHeader Invoice_org without any effect. Adding the same fields to the PageHeader Invoice group adds the fields to the green part in the picture above.

What I have found is that creating a identical new address for the customer using the Dynamics Axapta user interface and deleting the old one, makes the invoice use the correct invoicingaddress. This actually result in entries in the ADDRESS.table for the Party/Customer in question. There is a link between DirPartyTable and Address on the RecId from DirPartyTable corresponding to the AddrRecId in Address. But this is missing for the problematic customers.

I had a go at trying to create Address rows directly in DB but has understood that this is not recommended and since I don't know the database model good enough to update all tables required to maintain integrity I will have a go using the Excel import/export wizard in order to try to create new identical addresses for the customers in question.

Was this reply helpful? Yes No