Replacement key error in Excel Add ins in AX

Q: In AX2012, while trying to import data into the table ledgerJournalTrans using Excel add-in, we're getting this error, any solution for that?

Ans:

You have to specify the replacement key on the foriegn key tables of LedgerJournalTrans, basically Aif/web service displays the user friend values to the user in excel, i.e. if you want to sent the vendTransId in the LedgerJournalTrans, the user dont know about the Id, but he knows about the user friendly columns i.e. vendAccount and other transaction attributes, so it needs a replacement key that replace the foriegn key (recId) to user freindly columns.

Replacement key (pointing to an index, it can be equal to a clustered index)is a key that have columns identifying the unique record.

 Belows tables should be modified to set the Replacement key property in order to resolve this error

•  VendTrans
•  CustTrans
•  Tax1099BoxDetail
•  ReasonTableRef
•  VendBankAccount
•  CustBankAccount
•  BudgetSource

lookup in AX

In the dynamics ax 2012, there are different ways to fill the combo box/drop down list

How to create a simple lookup

The SysTableLookup class is provided by the standard application to allow programmers to easily create their own lookup forms, in code.

The basic steps to using this class are as follows:

1. Create the sysTableLookup object
2. Create the query to select the lookup data
3. Add  the fields shown on the lookup
4. Performs the lookup

client static void lookup<TableName> (FormStringControl _ctrl) {     SysTableLookup          sysTableLookup       =  SysTableLookup::newParameters(tableNum(<tableName>),_ctrl);     Query                   query                = new Query();     // create the query for the lookup     QueryBuildDataSource    queryBuildDataSource = query.addDataSource(tableNum(<tableName>));     // Add fields that will be shown in the lookup as columns             sysTableLookup.addLookupfield(fieldNum(<tableName>,<FeildName1>));     sysTableLookup.addLookupfield(fieldNum(<tableName>,<FeildName2>));     //Add the query to the lookup form     sysTableLookup.parmQuery(query);     // Perform the lookup     sysTableLookup.performFormLookup(); }

This above method of lookup was heavily used in AX 2009, and it also used in the AX 2012 when there isn’t any data source specified in the form (i.e. Dialog Form) and the StringEdit control used for the lookup

How to create a simple lookup Reference

 

 The SysReferenceTableLookup class is used to construct lookup forms for reference controls.

1. Create the SysReferenceTableLookup object
2. Create the query which will be used to select the lookup data
3. Add the fields which will be shown on the lookup
4. Perform the lookup 

This method is now the standard method used to lookup the data for drop down when there is any modification needed to override the behavior of the functionality provided by the automatic lookup

public static client <tableName> lookup<tableName>(     FormReferenceControl        _formReferenceControl) {     Query                   query;     SysReferenceTableLookup referenceLookup;     if (_formReferenceControl == null)     {         throw error(Error::missingParameter(null));     }     referenceLookup = SysReferenceTableLookup::newParameters(         tableNum(<tableName>),         _formReferenceControl,         true);     // create the query for the lookup form      query.addDataSource(tableNum(<tableName>));     // Add fields that will be shown in the lookup form as columns     referenceLookup.addLookupfield(fieldNum(<tableName>,<FeildName1>));     referenceLookup.addLookupfield(fieldNum(<tableName>,<FeildName2>));     // Add the query to the lookup form     referenceLookup.parmQuery(query);     // Perform the lookup and return the selected record     return referenceLookup.performFormLookup() as <tableName>; }

 

post inventory journal using code

recently i have written a quick code to to post the invent transfer jounral

/// <summary>

/// Populates the buffer of the <c>InventJournalTable</c> table data.
/// </summary>
/// <returns>
/// Buffer of the <c>InventJournalTable</c> table.
/// </returns>
Public InventJournalTable populateInventJournalTable()
{
    InventJournalTable      journalTable;
    InventJournalTableData  journalTableData;

    journalTable.clear();

    journalTable.JournalNameId  = InventParameters::find().QuickTransferJournalNameId;
    journalTableData            = JournalTableData::newTable(journalTable);
    journalTable.JournalId      = journalTableData.nextJournalId();
    journalTable.Reservation    = ItemReservation::Automatic;
    journalTable.JournalType    = InventJournalType::Transfer;
    journalTableData.initFromJournalName(journalTableData.JournalStatic().findJournalName(journalTable.journalNameId));
    journalTable.Description    = InventDescription.valueStr();
    journalTable.insert();

    return journalTable;
}

/// <summary>

/// Populates the buffer of the <c>InventJournalTrans</c> table data.
/// </summary>
/// <param name="_InventJournalId">
/// <c>JournalId</c> of the <c>InventJournalTable</c>
/// </param>
/// <returns>
/// Buffer of the <c>InventJournalTrans</c> table.
/// </returns>
public InventJournalTrans populateInventJournalTrans(InventJournalId _InventJournalId)
{
    InventJournalTrans inventJournalTrans;
    InventDim          toInventDim;

    inventJournalTrans.JournalId      = _InventJournalId;

    inventJournalTrans.JournalType    = InventJournalType::Transfer;
    inventJournalTrans.TransDate      = systemdateget();
    inventJournalTrans.ItemId         = inventSum.ItemId;
    inventJournalTrans.Qty            = InventQty.realValue();

    // Dimensions from which the transfer performs
    inventJournalTrans.InventDimId    = inventDimLocal.inventDimId;
    inventJournalTrans.initFromInventTable(InventTable::find(inventSum.ItemId), False, False);

    // Dimensions To which the transfer performs
    toInventDim.inventSiteId         = InventSite.valueStr();
    toInventDim.InventLocationId     = InventWareHouse.valueStr();
    inventJournalTrans.ToInventDimId = InventDim::findOrCreate(toInventDim).inventDimId;
    inventJournalTrans.insert();

    return inventJournalTrans;
}

/// <summary>
/// Creates and posts the Inventory Transfer Journal.
/// </summary>
/// <remarks>
/// If there is any exception then the Inventory Journal data is deleted.
/// </remarks>
public void createAndPostJournal()
{
    InventJournalTable      inventJournalTable;
    InventJournalTrans      inventjournalTrans;
    JournalCheckPost        journalCheckPost;

    ttsbegin;

    // populates the inventJournalTable table
    inventJournalTable = element.populateInventJournalTable();

   // populates the inventJournalTrans table
    inventjournalTrans = element.populateInventJournalTrans(inventJournalTable.JournalId);

    ttsCommit;

    if (BOX::yesNo('Do you want to post the Journal ? ', DialogButton::Yes) == DialogButton::Yes)
    {
        // Call the static method to create the journal check post class
        journalCheckPost = InventJournalCheckPost::newPostJournal(inventJournalTable);

        if(journalCheckPost.validate())
        {
            try
            {
                journalCheckPost.run();
            }
            catch
            {
                // Deletes the InventJournalTable table, the InventJournalTrans will auto delete because of the Delete actions.
                InventJournalTable.delete();
            }
        }
     }
}

get date in dynamics ax

I have read an article about the getting of current date time in dynamics AX and the answer is DateTimeUtil::getSystemDateTime. for further reading.

install Adventure Works SQL DW

How to install Adventure Works SQL DW and Analysis Services 2005/2008 sample database and project  

Below is the link for that contains the Adventure Works DB file and the process to install the DB, that is the sample database to learn the MDX queries

Tutorial
How to Install SSAS database

code samples ax 2009

I have found a very good link for code samples of different things in dynamics AX 2009

The list of items includes

·        Code samples for Microsoft Dynamics AX

·        Reports - Generate a List of Reports in Microsoft Dynamics AX

·        EP - Data Binding

·        AIF - Calling the Customer Service in Microsoft Dynamics AX 2009

·        AIF - Calling the Vendor Service in Microsoft Dynamics AX 2009

·        Enterprise Portal Quick Start: Creating Multi-Section Forms in Microsoft Dynamics AX 2009

·        Enterprise Portal Quick Start: Persisting Data Using View State and Data Sets in Microsoft Dynamics AX 2009

·        Enterprise Portal Quick Start: Customizing Lookups in Microsoft Dynamics AX 2009

·        Enterprise Portal Quick Start: Connecting a Details Part to a Grid

·        Enterprise Portal Quick Start: Adding a Toolbar to a Grid in Microsoft Dynamics AX 2009

·        Enterprise Portal Quick Start: Handling Exceptions in Microsoft Dynamics AX 2009

·        Enterprise Portal Quick Start: Using Record Context in Microsoft Dynamics AX 2009

·        Enterprise Portal Quick Start: Creating a Wizard (Tunnel) Form in Microsoft Dynamics AX 2009

·        Enterprise Portal Quick Start: Connecting a Fact Box to a Grid in Microsoft Dynamics AX 2009

·        Enterprise Portal Quick Start: Creating Links Based on Menu Items in Microsoft Dynamics AX 2009

·        Enterprise Portal Quick Start: Creating and Using Proxy Classes in Microsoft Dynamics AX 2009

·        Enterprise Portal Quick Start: Creating a Basic Form in Microsoft Dynamics AX 2009

·        Enterprise Portal Quick Start: Creating a Basic Grid in Microsoft Dynamics AX 2009

·        Enterprise Portal Quick Start: Creating an Advanced Grid in Microsoft Dynamics AX 2009

·        Code for video How Do I: Integrate an Application Using the .NET Business Connector?

·        Code for video How Do I: Create a Custom AIF Pipeline Component?

EP Development in AX 2012

Today, I have watched a very good presentation (http://vimeo.com/26902748) on Enterprise portal Development in AX 2012, given by a friend Zeeshan Jafry, the presentation covered the followed points, and it is a good presentation to have a quick start

• Basic Installation and configuration of EP
• Add

       o A new module in EP
       o   List page in that module
       o Quick Launch in that module
       o New web part
       o New EP Page
       o New user control (Web Form, Modal Dialog Form)
                  o http://msdn.microsoft.com/en-us/library/aa855178.aspx

Enterprise Portal is one of the most attractive features of Dynamics AX 2012. Enterprise Portal for Microsoft Dynamics AX provides a Web-based application framework that allows for users to interact with data in Microsoft Dynamics AX through a Web browser