Error: The value _________ is not found in the map. Dynamics AX 2012 AIF setup

Dynamics AX 2012 - Setting up the AIF

On the Inbound Ports form, under Document Data Policies, when clicking View Schema I received the following error:

The value "/SalesOrder/SalesTable/Dimension" is not found in the map.

This same error might be generated for other fields on other tables, so this issue might apply to any AIF Schema that throws an error of the format:

The value _________ is not found in the map.

This may be related to AIF entries that were upgraded from AX version 2009. It could also be caused by services that were implemented in one environment and then the database was transferred to another environment.

The fix was to do ALL of the following:

1. In the developer environment (Ctrl-D) go to Tools ==> AIF ==> Update Document Services. Choose your document service and regenerate Data Object Classes and update AxBC Classes.
2. Open the Projects window (Ctrl-Shift-P), find the Axd project for your document. Compile it (F7) and generate incremental CIL (Ctrl-Shift-F7)
3. In the AOT, open the Service Groups tab. Open the service group that applies
   to your document. (In my case, the SalesSalesOrderService goes in the AccountsReceivableServices group.) Make sure your service is listed in an appropriate group. If it's not there, drag it there from the Services tab.
4. On the Service Group, compile it (F7), generate incremental CIL (Ctrl-Shift-F7), and deploy it (right-click, Deploy)

 Hope this helps!

Entering Sales Orders by Barcode, GTIN, or any other reference

Dynamics AX insists that the ItemID be used on the Sales Line portion of the Sales Order Form. Many companies, though, have vendors that place orders by barcode number, industry identifier, or even vendor-specific ID. Here is a bit of code that lets the user enter any other number in the Sales Order Form (Form SalesTable) and have Dynamics look up the corresponding ItemID.

This example just does a lookup on the InventItemBarcode table, but it could easily be adapted to (also/instead) check any other tables, (InventItemGTIN, ExtCodeTable, CustVendExternalItem) in order to find items by their GTIN, industry code, or customer/vendor specific item number.

I implemented the code on the SalesTable form, but you could easily adapt it to just about any other form that requires an Item ID.

Hope this helps!

public boolean validate()
{

/*
Overridden validate method. If number entered does not match an ItemID
this will look up the entry in the barcode table and return
the first ItemID that is associated with that barcode.
*/

boolean ret;
ItemId ItemId;

InventTable InventTable;
InventItemBarcode InventItemBarcode;

;


ret = super();

ItemId = this.text();

If (ItemID != "" && !InventTable::exist(ItemId))
{
InventTable = InventTable::find(InventItemBarcode::findBarcode(this.text(), False, False).itemId);
if (InventTable.ItemId != '')
{
info(StrFmt("Barcode lookup. Using Item ID: %1.",inventtable.ItemId)); //Optional
SalesLine.ItemId = InventTable.ItemId; // sets the field correctly
this.text(InventTable.ItemId); // suppresses error 'item not found in relating table'
}

}

return ret;
}

Hard-coded reformatting of Netherlands postal codes

My company discovered recently that Dynamics AX 4.0 is coded to reformat the Netherlands postal codes when setting the address field. Because this code is implemented on the AddressMap object, it affects anything that uses addresses (customers, vendors, sales orders, warehouses, etc.)

The postal codes for the Netherlands consist of 4 number and 2 letters, separated by a space. Many other countries also have a space in their postal codes, like Canada and Great Brittan.

Postal code City Country
2252 GN Voorschoten NL
V5K 1C6 Vancouver CA
E11 3RU London GB

For most postal codes in Dynamics AX, the way the it is entered in the database is the way it appears in the formatted address field on forms and reports. If you want a space in your postal code, then make sure it is entered that way in the AddressZipCode table. What I discovered, though, is that there is a hard-coded exception for the Netherlands.

When the FormatAddress or SetAddress methods of the AddressMap object are called, they check to see if the address is associated with ISO country code NL. If so, they insert a space with this instruction:

addressZipCodeId = substr (addressZipCodeId, 1,4) + ' ' 
+ substr(addressZipCodeId , 5,2); 

Now this is fine if your Netherlands postal codes were entered without a space to begin with. The code above forces them to (what I can only guess is) the accepted format. This is a big problem, though, if your database already stores the accepted format for these postal codes; in which case you will end up with a mangled version with 2 spaces and missing the last character!

A quick fix is to add a line that checks to see if the Netherlands postal code is only 6 characters long and add the space only if it needs one.

if(strlen(addressZipCodeId) == 6)
    addressZipCodeId = substr (addressZipCodeId, 1,4) + ' ' 
        + substr(addressZipCodeId , 5,2);

New features of Microsoft Dynamics AX 2009 (5.0)

Convergence 2008 is coming quickly, and careful readers of the session catalog can gleam some info on the upcoming release of Dynamics AX 2009 (5.0).

The new AOS will sport some new features: a server based batch framework, multiple time zone capability, 64-bit versions, and advanced clustering support.

AX '09 will also bring a new Expense Management solution for automating the management of travel and entertainment (T&E) expenses. Integrated with Financials and Project Accounting, it is delivered through the web-based Enterprise Portal.

Dynamics AX 2009 (formerly 5.0 née 4.5)

As Convergence 2008 approaches, details are staring to leak out about the next version of Dynamics. It looks like the long-anticipated 5.0 release is going to be chronologically renamed "2009". The good news is that the release date is still 2008 … the bad news is it seems to have slipped from "first half of 2008" to "summer 2008." This of course is a way of saying "3rd quarter" that doesn't sound as bad.

There are a few high-level features that make for great ad copy: better integration with office, role-tailored menus and business dashboards, and support for tfeL-ot-thgiR languages. IT departments, though, are still waiting to see the detailed features list – all the little things that don't sound impressive in a sales pitch, but that can make a business run better.

For those who have not yet made the jump to version 4, you'll be glad to hear this: A Microsoft project manager on the Dynamics AX team has promised that clients will be able to upgrade directly from 3.0 to 5.0. It was posted in Microsoft's public newsgroup for AX programming. I'm sure this does not qualify as an official announcement, but it is a good indication that there will be a path for customers on the earlier release.

I'll keep posting news as I come across it. Heard something interesting about 5.0 / 2009? Post it as a comment!

Get external data by ODBC from Dynamics AX 4.0 and X++

A number of good blog entries have discussed how to pull information from other databases into Dynamics AX using X++.

Connecting to a Database via ODBC – Brandon George
http://dynamics-ax.blogspot.com/2005/11/connecting-to-database-via-odbc.html

Convert Axapta date to SQL datetime when querying SQL server directly – Fred Shen
http://fredshen.spaces.live.com/blog/cns!B32E9346DBBAE4E3!171.entry

Accessing an external database using X++ -- Arijit Basu
http://daxguy.blogspot.com/2006/11/updating-external-database-using-x.html

Access a SQL table that is not an Axapta table
http://dynamicsuser.net/forums/t/17136.aspx

But all of these were written for Axapta 3.0. I did not find a lot of documentation on how to do this in Dynamics 4.0. New security requirements in 4.0 mandate explicitly asserting permissions for calling outside data sources. MS has some source code samples, but they’re more for demonstrating the permissions classes than providing a functional example making calls to another database.
http://msdn2.microsoft.com/en-us/library/aa639808.aspx

For my project I needed to fetch records from a package shipping system to get the weight, tracking number, and delivery date of each box shipped. The shipping system uses a SQL database, but this code uses ODBC, so it may work for other data sources as well.

There was an additional requirement: I wanted this to run on the server, and use the AOS account for database authentication. This way, the code could be launched from any client without requiring every client to have the right SQL driver and ODBC configuration.

There was also a possible issue: The AOS is running on the 64-bit version of Windows Server 2003. The AOS is a 32-bit application, which can cause problems when trying to call a 64-bit ODBC driver.

Here is the finished code. It fetches the shipping records and copies them into a custom Dynamics table called MyShippingTrans. I've provided additional comments below.

============================

server static void GetShippingTrans(SalesTable salesTable)
{
LoginProperty loginProperty;
OdbcConnection connection;
Statement statement;
ResultSet results;
Str sqlStatement;
str dataSourceName = "MyODBCDataSource";
str databaseName = "MyDataBaseName";
MyShippingTrans trans;
SqlStatementExecutePermission sqlPermission;
;
loginProperty = new LoginProperty();
loginProperty.setDSN(dataSourceName);
LoginProperty.setDatabase(databaseName);

try
{

connection = new OdbcConnection(loginProperty);
statement = connection.createStatement();
sqlStatement = StrFmt(
"SELECT %1, %2, %3 "+
"FROM Shipment_detail WHERE " +
"Shipment_detail.packageid = '%4'"
, "tracking_number" //1
, "pkg_weight" //2
, "duedate" //3
, SalesTable.SalesId //4
);
sqlPermission = new SQLStatementExecutePermission(sqlStatement);
sqlPermission.assert();
results = statement.executeQuery(sQLStatement);
while (results.next())
{
setprefix(strfmt("Writing %1 to TLCShippingTrans"
,SalesTable.SalesId));
trans.clear();
trans.TrackingNumber = results.getString(1);
trans.Weight = results.getReal(2);
trans.DueDate = results.getDate(3);
trans.SalesId = salesTable.SalesId;
trans.insert();

}
CodeAccessPermission::revertAssert();
}
catch (Exception::Error)
{
error(strFmt("Error accessing shipping database "
+ "or writing to TLCShippingTrans"
+ " on sales order %1", salesTable.SalesId));
}

}

==========================

Here's the same code again, with some comments.

When declaring the method, make sure it runs on the server. Otherwise every client and batch server that runs it will need to have the SQL client and ODBC connection set up. Also, every user would need permission on the external database.

server static void GetShippingTrans(SalesTable salesTable)
{

When declaring the method, make sure it runs on the server. If it does not run on the server, then every client machine and batch server that runs it will need to have the SQL client and ODBC connection set up. Also, every user would need permission on the external database.

LoginProperty loginProperty;
OdbcConnection connection;
Statement statement;
ResultSet results;
Str sqlStatement;
str dataSourceName = "MyODBCDataSource";
str databaseName = "MyDataBaseName";

These are the variables that we'll use to create our connection to the SQL database via ODBC. For simplicity in this example, the data source and database names are hard-coded. In our production system, these are stored in a parameter table so that administrators can easily change them when necessary.

MyShippingTrans trans;

This is the Dynamics table where we'll store the retrieved data

SqlStatementExecutePermission sqlPermission;

Here's the permission object that was introduced in version 4.0 as part of the trustworthy computing initiative. It is now necessary to grant explicit permission to make a call to an external database.
;
loginProperty = new LoginProperty();
loginProperty.setDSN(dataSourceName);
LoginProperty.setDatabase(databaseName);

These lines initialize the login property with the data source name and database name established earlier. Some bloggers have reported that due to requirements of the trustworthy computing initiative, one can no longer make calls from Dynamics AX to MS SQL server using SQL authentication, even if the database server is configured too allow it. It must be done using windows authentication, which happens automatically if the .setUsername() and .setPassword() methods are not invoked. This may not hold true for access to other ODBC data sources, in which case these lines may be helpful:

// loginProperty.setUsername("UserID");
// loginProperty.setPassword("Password");


If there are problems accessing the ODBC data, I want to catch the error:

try
{

connection = new OdbcConnection(loginProperty);
statement = connection.createStatement();
sqlStatement = StrFmt(
"SELECT %1, %2, %3 "+
"FROM Shipment_detail WHERE " +
"Shipment_detail.packageid = '%4'"
, "tracking_number" //1
, "pkg_weight" //2
, "duedate" //3
, SalesTable.SalesId //4
);
This prepares our connection and defines the SQL statement that we'll send to the external data source. You can see that there are three fields we want to retrieve, and we'll select the records that match the SalesId that was passed to the method.

sqlPermission = new SQLStatementExecutePermission(sqlStatement);
sqlPermission.assert();
results = statement.executeQuery(sQLStatement);

Now we use the new permission classes. We create a permission specific to the SQL select statement that we want to use, and explicitly assert the permission. Then we fetch the ResultSet by passing the same SQL statement to the executeQuery() method.

while (results.next())
{
setprefix(strfmt("Writing %1 to TLCShippingTrans"
,SalesTable.SalesId));

trans.clear();
trans.TrackingNumber = results.getString(1);
trans.Weight = results.getReal(2);
trans.DueDate = results.getDate(3);
trans.SalesId = salesTable.SalesId;
trans.insert();
}

A while loop takes us through all the records, and assigns their values to the local table's fields. Note that the results.getType(x) must be in numerical order, or a database error results (more on this later).

CodeAccessPermission::revertAssert();

As you can guess from the name, this step removes the SQL permission granted earlier. This step is optional.


}
catch (Exception::Error)
{
error(strFmt("Error accessing shipping database or "
+ "writing to MyShippingTrans"
+ " on sales order %1", salesTable.SalesId));
}
}

Finally, we catch any errors that occurred and note the sales order on which it happened.

If you found this code helpful, take a minute and leave me a message about your current project.


======================


I mentioned above that the fields in the result set must be accessed in numerical order. If you try to access them out of order, or you reference them more than once, you'll get a database error. For example, you cannot do this:

// not in order
trans.DueDate = results.getDate(3);
trans.TrackingNumber = results.getString(1);
trans.Weight = results.getReal(2);

You also cannot do this:

if (results.getString(1))
{
// read index 1 a second time
trans.trackingNumber = results.getString(1);
{

Making these mistakes will cause one or more of these errors:

Cannot execute the required database operation. The operation failed.
[Microsoft][ODBC SQL Server Driver]Invalid Descriptor Index
Cannot read a record in ().
The SQL database has issued an error.

======================

A note on using ODBC when running AOS on a 64-bit version of Windows:

The Dynamics AX 4.0 AOS is a 32-bit (x86) application. A 32-bit application is not permitted to call 64-bit (x64) code on Windows Server 2003. This means that you'll need to set up a 32-bit ODBC definition on your 64-bit server if you want Dynamics to use it.

The 64-bit ODBC connections are "invisible" to 32 bit apps, so if the Dynamics AOS tries to call a 64-bit ODBC connection, you'll get an error that the DSN is not found:

ODBC operation failed.
Unable to logon to the database.
[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
Object 'OdbcConnection' could not be created

Fortunately, it is easy to set up the 32-bit ODBC connection. The default ODBC console on 64 bit Windows Server is, of course, 64-bit itself. MS did provide a 32-bit console, though. It can be found in
%SystemRoot%\SysWOW64\odbcad32.exe

Use this to set up and configure your ODBC connections, and the Dynamics AX AOS will see them.

I've only done this for the AOS on a Windows Server, but I would guess that the same considerations apply if you're trying to run the Dynamics AX client on an x64 desktop and your ODBC calls are initiated from the client.

Perhaps Dynamics AX 5.0 will have a 64-bit AOS as an option!

==============================

Some developers claim that using CCADO is a better way than ODBC to access other databases from Dynamics AX and X++. Here are some links in case you want to do more research:

Using CCADO to interact with other databases. (translated from Spanish)
http://translate.google.com/translate?hl=en&sl=es&u=http://www.trucosax.com/phpnuke/modules.php%3Fname%3DNews%26file%3Darticle%26sid%3D73&sa=X&oi=translate&resnum=4&ct=result&prev=/search%3Fq%3DCCADO%26hl%3Den%26safe%3Doff%26rls%3Dcom.microsoft:en-us%26pwst%3D1

Data Migration in Axapta Programming
http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?dg=microsoft.public.axapta.programming&tid=962cb419-bdc4-45c4-8faa-5c4876c18c5f&p=1

Leave me a note if you think the CCADO classes are better than ODBC.

Color-code your Dynamics environments

Since implementing Axapta / Dynamics AX in 2003, we've found it invaluable to provide an environment for testing and training as well as our live system (or production environment) to end users. This lets people try different processes or train new employees without fear of screwing up the "real" data.

Occasionally, though, we've run into a problem where someone thought they were using the live system, but were actually in the test environment. It's an easy mistake to make. There is no obvious visual cue to alert a user that they are working on a test system.

There is a way change the color of the Dynamics forms to help indicate what environment is in use. It involved overriding the SysSetupFormRun.run() method, which will be called every time a form is opened. On the class SysSetupFormRun, create a new method with this code:


public void run()
{
SysSQLSystemInfo systemInfo = SysSQLSystemInfo::construct();
;
super();


// Set the color scheme of this instance of the SysFormRun to RGB
this.design().colorScheme(FormColorScheme::RGB);


// If the database name is not the live version, change the color of the form

if (systemInfo.getloginDatabase() != 'MyDBName')
this.design().backgroundColor(0x112255);
}



That's all there is to it. If your live and test systems use the same database name, but the AOS is running on different servers you can modify this code to to match on systemInfo.getLoginServer() != 'MyServerName'. You can change the color by setting the hex value. It uses the RGB values in reverse order: 0xBBGGRR.


Variations of this code could switch form colors to indicate the current user account, application layer, or company. Less practical uses could match color to the time of day or season of the year. If you find this code useful, leave me a message noting what you did with it.


=========================


Special thanks to Brandon George and his Dynamics AX blog for help with form colors.

For more information on changing form colors and sample code for version 3.0, see:

http://dynamics-ax.blogspot.com/2006/04/changing-form-color-based-on-current.html



=======================




Sample screens showing how the code above changes the test environment forms compared to the same form in the live production environment.