CODEWITHSUNDEEP
axaptax++dynamics-ax-2012
Tito
Tito

Reputation: 804

Get all addresses on the system Dynamics AX 2012

QUESTION:

I would like to know how can I get all the addresses on the system, including (customers, vendors, banks and tax authorities) on a query, and put it on a view with columns (name, address). So it would be parties and non-parties entities.

WHAT I'VE TRIED TO DO:

I use the table concepts to figure out the problem for parties:

Part of the LOCATION DATA MODEL

LogisticsPostalAddress - table contains addresses in the system that could be associated with various entities. Is where the main information about the addresses are, it seems that the rest are extra descriptions of these addresses.

The CustTable and VendTable have a FK to DirPartyTable. The BankAccountTable and the TaxAuthorityAddress have the relation directly with the LogisticsLocation that have relation with the LogisticsPostalAddress, at the relation Location_FK.

The question now is how to build the query that gets the addresses from Customers, BankAccounts, Vendors and Tax Authorities and put it on a view with (name,address) pair?

EDITED

I have found a way to get all addresses using a method, but not using a query, and is not very efficient, but it was enough for what I wanted:

1) Create a table: AllAddresses for example: With fields: Name, Address

2) Create a job:

static void GettingAllAddresses(Args _args)
{
    CustTable custTable;
    VendTable vendTable;
    TaxAuthorityAddress taxAuthorityAddress;

    LogisticsPostalAddress logisticsPostalAddress;
    AllAddresses allAddresses;

    ;

    while select * from custTable
    {
        allAddresses.Address = custTable.address();
        allAddresses.Name = custTable.name();
        allAddresses.CustAccount = custTable.AccountNum;
        allAddresses.doInsert();
    }



    //Getting all vendors
    while select * from vendTable
    {

        allAddresses.Address = vendTable.address();
        allAddresses.Name = vendTable.name();
        allAddresses.doInsert();
    }

    //Getting all tax authorities addresses
    while select * from taxAuthorityAddress
    {


        allAddresses.Address = TaxAuthorityAddress::address(taxAuthorityAddress.TaxAuthority);
        allAddresses.Name = taxAuthorityAddress.Name;
        allAddresses.doInsert();
    }

    //Getting all bank addresses
    while select * from bankAccountTable
    {
        logisticsPostalAddress = LogisticsLocationEntity::location2PostalAddress(bankAccountTable.Location);
        allAddresses.Address = logisticsPostalAddress.Address;
        allAddresses.Name = bankAccountTable.Name;
        allAddresses.doInsert();
    }
}

Upvotes: 1

Views: 15157

Answers (2)

ThD
ThD

Reputation: 1

As one can tell from your database diagram the relationship for the Global Address Book is very complex (and in many places really non-intuitive). One thing it appears you did not address is whether or not the LogisticsPostalAddress you get is active or not (as determined by the ValidFrom and ValidTo dates). AX has code that preserves the current state of an address that is changing in LogisticsPostalAddress by simply setting the ValidTo to 2 seconds before the change is committed, and creates a new LogisticsPostalAddress record with ValidTo set to the commit date so it will be active. If you have not had a lot of changes made to addresses then you probably won't have much of a problem, but if there were a lot you may get a lot of un-wanted address records. There is an article here http://hyperdaptive.com/2016/04/320/ that has some details in it that could be helpful - it also has some X++ and SQL code that could be very helpful if you happen to still be working on this. Good luck.

Upvotes: 0

Jay Hofacker
Jay Hofacker

Reputation: 3469

Look at how the view LogisticsEntityPostalAddressView is built, it may already provide the information you need. If the Description field from the LogisticsLocation table isn't good enough and you really need the Name field from, for example, the table BankAccountTable, you will need to build a similar view.

You can see that view uses the view LogisticsEntityLocationView, which has a union query which pulls data from several tables. You could build a similar view to pull the Name field from BankAccountTable, DirPartyTable, and TaxAuthorityAddress.

Upvotes: 1

Related Questions