SOLVED :"Illegal data conversion from original field (Dynamics AX 2009)"

After Restoring the data from Live Environment to the Test Environment When I synchronize the Data Dictionary after that I got the following error message.

Illegal data conversion from original field IPBCLIENTSESSION.CLIENTCOMPUTER to CRTFERTORDERTABLE.FertSupplier: Unable to convert data types to anything but character field type (0 to 4).

In Dynamics AX Environment I check the id of the table i.e CRTFERTORDERTABLE  and id of the field i.e FertSupplier

The id of table  CRTFERTORDERTABLE  is 30379

id of the field FertSupplier is 30002.

Than in Sql server I write the following query:

 SELECT TABLEID,FIELDID,NAME from SQLDICTIONARY
  where TABLEID = 30379 and  (FIELDID = 30002)

After running the query the result that I got is:

in the above image we can see that field CLIENTCOMPUTER contains the id 30002 while in AX Field ID 30002 belongs to FERTSUPPLIER

now I will update the SQLDICTIONARY table and set the CLIENTCOMPUTER ID to new id to remove the conflict.

below is the query to update the table

update SQLDICTIONARY set FIELDID = 300020
  where TABLEID = 30379 and FIELDID = 30002
 
Now after running this I will again synchronize the table in Dynamics AX and after the synchronization the error will not arrive.

Thanks

Muhammad Zahid.

Dynamics AX 2009 Check name of the Database name and Database Server name

In Microsoft Dynamics AX 2009 we can check the name of the database and database server without opening the AX Server Information.

Below is the navigation path to check this.

Administration -> Database -> Database information

Once you click the database information below form will be open that contains that information .

Thanks

Muhammad Zahid.

Check the name of the calling object on the Form in Dynamics AX 2009

Some times on the form we need to check which object called this form and based on the calling object we do some changes on the form.

if the type of the calling object is class than below is the code inside if check through which we can identify the name of the caller class:

 if(SysDictClass::isEqualorSuperclass(classidget(element.args().caller()), classnum(nameoftheclass)))
{
     //write some logic to do some changes on the form if the caller class is the same is you check
}

if the type of the calling object is menuitem than below is the code:

if(this.formRun().args().menuItemName() == (menuItemDisplayStr(HcmUpdateToForecastPosition))
{
  //write some logic to do some changes on the form if the caller menuitem is the same as you           check 
}

if the type of calling object is form than below is the code:

Object  formRunObject;//write this in the class declaration of the form

//write below code in the init method of the form

formRunObject = element.

if (element.args().caller())
{
     formRunObject = element.args().caller();

     if(formRunObject.name() == formstr(ProjInvoiceTable))
     {
          //write some logic to do some changes on the form if the caller menuitem is the same as                //you check
     }
}

This are the three ways in which we can check the names of calling object.

you can write these code in the init method.

if you have any questions on this than feel free to comment on it.

Thanks
Muhammad Zahid

Error Message When Synchronize Microsoft Dynamics AX 2009 Data Dictionary

After restoring the Database from the Production Environment on to the Test Environment I synchronized the data dictionary and than I got the sync error.

I investigate and found out that the issue was in the CRTCards table. It has the unique index for CardNumber Field and the table contains the duplicates records for card number.

I just go to the SQL Server Management Studio and write the below query and run it to check for the duplicate records and delete them.

WITH CTE AS(
 SELECT CARDNUMBER,
 RN = ROW_NUMBER()OVER(PARTITION BY CARDNUMBER ORDER BY CARDNUMBER)
 FROM CRTCARDS
 )
 DELETE FROM CTE WHERE RN > 1

Below are the links for more detail analysis on this:

https://support.microsoft.com/de-ch/kb/937765

http://stackoverflow.com/questions/18390574/how-to-delete-duplicate-rows-in-sql-server

Thanks

Muhammad Zahid.

Getting Financial Dimension Values through X++

I was working on the  SSRS reports there was the scenario of showing the individual dimensions values i.e Business Unit, Cost Center, Department, LineOfBusiness, LineOfRevenue.

I have the generalJournalAccountEntry table in my query which cantains the LedgerAccount that contains the financial dimension values in combine i,e 602100-001-026-014-. This account contains the values of main account i.e 602100, business unit i.e  011, department i.e 026,  costcentre i.e 014.

My scenario  was I have to break this accounts into individual dimensions at run time.

After some research I got the code to get the financial dimension values by ledgerDimension which is already present in GeneralJournalAccountEntry table.

Below is the code snippet to achieve this:

private void addDimensionSegments(MzkLedgerTransactionListStagingTmp _ProcessingStagingTable, GeneralJournalAccountEntry _generalJournalAccountEntry)
{
    // DimensionAttributeValueCombination stores the combinations of dimension values
    // Any tables that uses dimension  combinations for main account and dimensions
    // Has a reference to this table’s recid
    DimensionAttributeValueCombination  dimAttrValueComb;
    //GeneralJournalAccountEntry is one such tables that refrences        DimensionAttributeValueCombination
    //GeneralJournalAccountEntry          gjAccEntry;//TODO commment zahid
    // Class Dimension storage is used to store and manipulate the values of combination
    DimensionStorage        dimensionStorage;
    // Class DimensionStorageSegment will get specfic segments based on hierarchies
    DimensionStorageSegment segment;
    int                     segmentCount, segmentIndex;
    int                     hierarchyCount, hierarchyIndex;
    str                     segmentName, segmentDescription;
    SysDim                  segmentValue;
    ;

    setPrefix("Dimension values fetching");
    //Fetch the Value combination record
    dimAttrValueComb = DimensionAttributeValueCombination::find(_generalJournalAccountEntry.LedgerDimension);
    setPrefix("Breakup for " + dimAttrValueComb.DisplayValue);

    // Get dimension storage
    dimensionStorage = DimensionStorage::findById(_generalJournalAccountEntry.LedgerDimension);
    if (dimensionStorage == null)
    {
        throw error("@SYS83964");
    }

    // Get hierarchy count
    hierarchyCount = dimensionStorage.hierarchyCount();
    //Loop through hierarchies to get individual segments
    for(hierarchyIndex = 1; hierarchyIndex <= hierarchyCount; hierarchyIndex++)
    {
        setPrefix(strFmt("Hierarchy: %1", DimensionHierarchy::find(dimensionStorage.getHierarchyId(hierarchyIndex)).Name));
        //Get segment count for hierarchy
        segmentCount = dimensionStorage.segmentCountForHierarchy(hierarchyIndex);

        //Loop through segments and display required values
        for (segmentIndex = 1; segmentIndex <= segmentCount; segmentIndex++)
        {
            // Get segment
            segment = dimensionStorage.getSegmentForHierarchy(hierarchyIndex, segmentIndex);

            // Get the segment information
            if (segment.parmDimensionAttributeValueId() != 0)
            {
                // Get segment name
                segmentName = DimensionAttribute::find(DimensionAttributeValue::find(segment.parmDimensionAttributeValueId()).DimensionAttribute).Name;
                //Get segment value (id of the dimension)
                segmentValue        = segment.parmDisplayValue();
                //Get segment value name (Description for dimension)
                segmentDescription  = segment.getName();
                ///info(strFmt("%1: %2, %3", segmentName, segmentValue, segmentDescription));
                if(segmentName == 'BusinessUnit')
                {
                    _ProcessingStagingTable.BusinessUnit = segmentValue;
                }
                else if(segmentName == 'LineOfBusiness')
                {
                    _ProcessingStagingTable.LineOfBusiness = segmentValue;
                }
                else if(segmentName == 'LineOfRevenue')
                {
                    _ProcessingStagingTable.LineOfRevenue = segmentValue;
                }
                else if(segmentName == 'Location')
                {
                    _ProcessingStagingTable.Location = segmentValue;
                }
                else if(segmentName == 'Department')
                {
                    _ProcessingStagingTable.Department = segmentValue;
                }
            }
        }
    }
}

Thats all from my side.

More detail to this can be found on the below link:

https://sumitsaxfactor.wordpress.com/2011/12/16/getting-individual-dimension-combination-valuesdimension-storage-class-ax-2012/

Microsoft Dynamics AX 2012 Leading Zero Reading From Excel Sheet

I was importing the financial dimensions values i,e Business Unit , Cost Centre and Department.

I got stuck while importing the value 02 and after reading this value from excel sheet it was considered as 2.

after searching a lot to fix this issue I found the following links that helps me to resolve this issue.

http://dynamicsuser.net/forums/p/72274/384995.aspx

http://dynamicsuser.net/forums/t/53171.aspx

Thanks

Muhammad Zahid.

Deleting Legal Entity and its Transactions

Below is the the link that contains the info on how to delete the Legal Entity and its Transactions.

http://daxture.blogspot.com/2014/09/delete-legal-entities-in-ax-2012.html


Thanks

Muhammad Zahid. 

Microsoft Dynamics AX 2012 DataDictionary Synchronization errors

There was a working environment of Microsoft Dynamics AX 2012 with data.

I just changed its database file without changing the model file.The database file was without data.

After attaching the database i try to synchronized the data dictionary.

After synchronization I got the following error message on around 18 tables:

Below is the screen shot of this error that i was getting

I also compared the ids of the objects on which I was getting the errors in the sqldictionary table and the AX environment but the ids were same.

After getting some help from the below links I deleted the table on which I was getting the sync errors in the SQL Server Management Studio and than sync that table on Micosoft Dynamics.

After doing the above steps on each of the table that's generates the errors my issue was resolved.

Below are the links for more info on this.

http://blogs.msdn.com/b/axsupport/archive/2012/09/19/troubleshooting-aot-synchronize-errors.aspx

https://community.dynamics.com/ax/f/33/t/143042


Thanks

Muhammad Zahid.

Sql Query to Copy Data from one table of Database to Another table of different database

To copy large amount of data from one table of one Database to another table of different Database.We can use the below query.

note:To copy the data one thing keep in mind that the table definition in both the databases should be the same.

INSERT INTO MicrosoftDynamicsAX..userinfo 

SELECT * FROM MicrosoftDynamicsAX_Deleted..USERINFO where USERINFO.ID = 'admin'

The above code will copy the data from "userinfo" table of database "MicrosoftDynamicsAX_Deleted" to the "userinfo" table of database named "MicrosoftDynamicsAX"

Thanks

Muhammad Zahid

Error Renaming the Database name in Microsoft Sql Server

When I was trying the rename the database manually the Microsoft SQL Server was throwing the below error:

The database could not be exclusively locked to perform the operation.

After doing some research on this. I found out that there were some connections with that database already exists.

To resolve this we need to close all the connections first and than rename the database.

Below is the query to do all of this for us.

use master
ALTER DATABASE OldDBName SET SINGLE_USER WITH ROLLBACK IMMEDIATE    
ALTER DATABASE OldDBName MODIFY NAME = [NewDBName]

ALTER DATABASE NewDBName SET MULTI_USER

here OldDBName is the name of your existing database and NewDBName is the name that you want to change.

More information on this can be found on the below link.

http://stackoverflow.com/questions/16685269/error-on-renaming-database-in-sql-server-2008-r2

Thanks

Muhammad Zahid.

Creating Multi Select lookup in SSRS reports in Microsoft Dynamics AX 2012

In this tutorial I share you the steps of creating the multi select lookup.

Below is the screen shot of the multi select lookup of how its looks like.

First of all we need to create the parm method in the contract class for this multi select company lookup.

As you can see this in the below image.

After that we need to bind the control to this parm method of multi select company lookup to the dialog box in the build method of the UI Builder Class.

After  that in the postRun method of the UI Builder Class we are calling the another method that we have created to create the lookup on the companyField Control.

Below is the definition of this companyLookup method.

In the above code the selected field container contains the fields which are selected by default when the lookup is open.You can also show the empty container if not to show any field selected by default.

This was all related to the multi select lookup in SSRS Reports.

If you have any question related to that than feel free to approach me.

Thanks

Muhammad Zahid.

Using Radio Button Control for RDP Based SSRS Reports in Microsoft Dynamics AX 2012 part 2

This is the second post related to the radio button control for RDP Based SSRS Reports.

In this post we will learn on creating the radio buttons using the enum with style property set to combo box.

below is the screen shot of the enum with style property set to combo box.

Below are the steps for creating the radio buttons from the enums of style type combo box.

First of all declare the following variables in the class declaration of the UI Builder Class.

After that write the following code in the build method of the UI builder class.

After that write the following code in the post run method of  the UI builder class after the super() method.

The above code is basically registering the override modified method on the selection change of the radio buttons.

Below is the definition of this reportTypeModifiedMethod

The above method is basically enabling and disabling the custAccount lookup on the selection change of the radio buttons.

Now after doing all of this please refresh the datasource node of the report on visual studio to see the parameters reflected there.

After running the report the parameters looks like this.

On the selection of "EEO prepartion" the custAccount lookup will gets disable as our reportTypeModified method that we have discussed previously is doing this.

One thing we noted here is that there is no need to create the contract method of the enum if the style of the enum is combo box.

This was all related to the creation of radio buttons from the enum having style = combo box.

Feel free to comment if you need further info on this,

Thanks

Muhammad Zahid. 

Using Radio Button Control for RDP Based SSRS Reports in Microsoft Dynamics AX 2012 part 1

Radio Buttons are created on the report parameter through the enums.

There are two scenarios.IF the style of the enum property is radio button than code is different and if the style of the enum property is combo box than code will be different.

First I will give the example of  the enum which have style property set to radio button:

Below is the screen of the enum with style property set to radio button:

First we have to create the parm method for this enum in the contract class.

Below is the screen shot for this.

Than in the build method of the UIBuilder class we need to add the following code to show the radio button control on the dialog:

Now in the postbuild method of the UI builder class I am getting the bind control and registering a method on the selection change of this control.

the type of dialogFieldFilterBy is DialogField.I have declared this in the class declaration.

Now the method that I have registered in the this control will enable disable some other controls based on the selection change of the radio control.

Below is the definition of this method that I have registered.

After  doing all of this just refresh the datasources on the designer node of this report in visual studio so that parameters should appear in the parameters not of report.

Now after running the report the checkbox control appears like this:

Now on the selection change of this radio control the method will be called that we have registered previously i.,e filterbymodified

Now when I select the ProjectQuotation from the radio button ItemGroupId and ItemId will be disabled and Project Field will be enable due to the execution of the code in the registered method.

As you can see this in the below image.

This was all related to the radio button in SSRS reports using the enum with style property set to radio button.

In the next post I will give the example of  creating the radio buttons with enum with style property set to combo box.

Thanks

Muhammad Zahid.

no report data table with name exists in report schema for data provider

I was getting the following error message when I move my already running report from Test Server to the Live Server.

After running the report on live server the error message was:

"No report data table with name MZK_ProductPriceHeaderTmp exists in report schema for data provider MZK_ProductPriceDP."

The Solution to this issue is that restart the reporting services running on this server and than clear cache and the usage data and than try to run the report again.

Thanks

Muhammad Zahid.

Changes in InventTrans Data Model in Microsoft Dynamics AX 2012 Version

After the upgrade from AX 2009 to AX 2012.My client has raised an issue that he was not able to select the delivery note and physical date from Stock Transactions table in selection criteria on the SalesEditLines Form.

After some research I found out that there is same change in InventTransData Model and thats why query associated to the SalesEditLines Form is also modified that's why user was not able to see the two fields as they were in AX 2009.

Below is the Screen Shot taken from AX 2009 of the form SalesEditLines Form:

Above form can be access through the below navigation path:

Account Receivable -> Periodic -> Sales update -> invoice

As you can see in the above image Stock Transactions table was available in selection criteria in AX 2009 but in AX 2012 it is change in the query associated to this form due to change in the data model of InventTrans.

Below is the link that contains the description of change in InventTrans Data model in AX 2012.

http://www.axpulse.com/changes-in-microsoft-dynamics-ax-2012-inventtrans-data-model/

Dynamics AX 2012 SSRS Report Parameter Label Truncate Issue

I was having an issue on SSRS report that label of my parameter was truncating.

The issue was that I was not using any Label Id for the parameter text.

Below are the steps to resolve this issue.

1. Creating a label with label id
2. Prefixing "literalstr()" around the label - e.g. SysOperationLabelAttribute(literalstr("@lbl001"))

Below is the link for more detail on this issue:

https://community.dynamics.com/ax/f/33/t/119603.aspx

Thanks

Muhammad Zahid.

Getting name of the Query Associated to the Form

There are many forms  in which user have the rights to modify the selection criteria on the form.

The forms that shows the selection criteria are basically associated with query.

Below is the navigation path of the form,I have took this as an example.

Go Account Receivable / Periodic / Sales Update / Invoice/

A form will open as you can see this in the below image.

 Now click on the select button.A new form will open this is basically the form where you can specify the selection criteria.

This form is basically based on the query i,e is associated to the form.

You can see the screen shot of this selection criteria form in the below image.

Now to find the query i,e bind to this form just right click it and and than select the personalize tab and than click advance tab and than see the field in the caller field.

The caller field contains the name of the query i,e  associated to this form.

You can see this in the below image.

Below is the query i.e salesupdatepackingslip associated to above form.

Thanks

Muhammad Zahid.

Progress Bar Indicators in Microsoft Dynamics AX 2012

To perform some data processing that takes large amount of time for example uploading data from Excel or exporting data to excel.We can show the progress bars to the users that data processing is under progress and remaining time etc.

Below is the link which shows the examples on how can we use the progress bars in Microsoft Dynamics Ax 2012:

http://msdn.microsoft.com/en-us/library/aa841990.aspx 

Thanks

Muhammad Zahid.