Category Archives: Ax 2012

SystemSequences Update


If you ever get a error where the RecId being inserted into the table already exists then the SystemSequences table is to be blamed for this

In some of my previous blog posts, i update the system sequence table’s NextVal record, which determines the RecId to be given. However, this is tricky.

  1. Update in SQL
    • Stop the AOS
    • Update the NextVal recod in the SystemSequences Table
    • Start the AOS
  2. Write a job
static void SetNextRecId(Args _args)
{
    SystemSequences seq;
    ttsBegin;
    select firstonly forupdate crosscompany seq
        where seq.tabId == 123456; // use the table id here or tablenum()
    seq.skipTTSCheck(true);
    seq.skipDatabaseLog(true);
    seq.selectForUpdate(true);
    seq.nextVal = 5637123456 + 1; // enter the last recId for the table
    seq.update();
    ttsCommit;
}

You may need to run this job and restart the AX client only if the just the above doesn’t work

static void sab_recIdSequenceFix(Args _args)
{
    SystemSequence systemSequence = new systemSequence();
    Tableid tableId = 123456; // use the table id or tablenum() here

    systemSequence.suspendRecIds(tableId);
    systemSequence.suspendTransIds(tableId);
    systemSequence.flushValues(tableId);
    systemSequence.removeRecIdSuspension(tableId);
    systemSequence.removeTransIdSuspension(tableId);
}
Advertisements

AX 2012 License generator UI


So i had created a UI for the license generator.

To create a license in 2012 this was the command:

axutil genlicense /file:c:\licenses\MyLicense.txt /certificatepath:c:\certificates\MyCertificate.pfx /licenseCode:MyModule /customer:MyCustomer /serialnumber:1231232123 /password:MySecretPassword123 /expirationdate:12/30/2018 /usercount:5

However, having the password and certificate path information available wasnt exactly a good idea at that time (and probably is still relevant. Why would you want to distribute those two together no?)

So came the the License generator, with the password hard coded and everything else seemingly invisible to the person punching the licenses without having someone with knowledge of the axutil and more importantly

What is this black box ? and why cant I just click somewhere

Moving forward a few years later and opening up the code out, (yes the password has been taken out of the code). Its now stored as well, although not very secure, but enough to keep prying eyes out for a bit. (this means rooms for improvement)

The code is published at GitHub under AxLicenseGenerator (See the readme in that link)

Or you can Download Ax License Generator 1.1

There is lots more information there, or message me if you need more information.

Happy Licensing

LicGenerator_V1.1
License Generator v 1.1

[AX2012] Moving Project to a diferent Model


 

Moving a project to a different model causes Ax to move all the objects inside it to that model as well.

You could instead create a new project in the Model you want it and then drag all the objects into that. You will lose all the metadata of the project including timestamps.

Another way is to move just the object related to the Project to the right model.

For that use the following steps

    1. Copy the Name of the Project
    2. Find the ModelId you want to move it into. You can use Get-AxModel command for this. (this should be within the same Layer)
    3. Open SQL and find the Element handle of the Project. Use the following command to do that
      -- Find the Element Handle
      -- ElementType 37 = SharedProject
      select top 10 * from ModelElement
      where Name = 'PUT_YOUR_PROJECT_NAME_HERE'
      and ElementType = 37
    4. Once you get just one record from the above code, then run the following command to make sure that the element is the correct one (It should have the modelId which is the current model it is in)
      DECLARE @ELEMENT_HANDLE int
      -- Set the Element Handle
      set @ELEMENT_Handle = (select ElementHandle from ModelElement where name = 'PUT_YOUR_PROJECT_NAME_HERE')
      
      select @ELEMENT_HANDLE
      -- Find the element data and check the Model id
      select * from ModelElementData where ElementHandle = @ELEMENT_HANDLE
      
    5. If you get just one record then we should be good to move the element to the right model
      DECLARE @ELEMENT_HANDLE int
      -- Set the Element Handle
      set @ELEMENT_Handle = (select ElementHandle from ModelElement where name = 'PUT_YOUR_PROJECT_NAME_HERE')
      
      select @ELEMENT_HANDLE
      -- Find the element data and check the Model id
      select * from ModelElementData where ElementHandle = @ELEMENT_HANDLE
      
      -- Move the Model
      -- Change 20 to the ModelId you want the element to be in
      Update ModelElementData set ModelId = 20 where ElementHandle = @ELEMENT_HANDLE
      

 

Power BI for Desktop & Ax 2012


Embed from Getty Images
Power BI for desktop is a very powerful tool and can be used to mash up data from different sources. It is definitely one up to the Excel plugins and so far seems to be more lightweight and processes things faster
I will go through my findings with PowerBI and Ax to come up with some examples

Install PowerBI: Download PowerBI for Desktop and there its a usual click  install process. There are other versions available for every other device, which i will look at later. There is a detailed article on installing powerBI at c-sharpcorner.

Open up the the application and we shall create a report that looks like this

powerBI_AxSales_OverviewAX Sales over time / Region

So once we open up PowerBI, select “Get Data” and select SQL server database

powerBI_AxSales1
Select the data source

Enter the Database Server, if possible put the database name as well. Ax Databases being so large seems to slow down power BI.

Select the database server and database
Select the database server and database

We will now select the Tables/Views from the list. You can use the search bar and select the checkbox. Select the following Tables/Views:

  • CustInvoiceJour
  • CustGroup
  • LogisticsPostalAddressView

After selecting the checkboxes, click the “EDIT” button in the pop up window. This will load the data preview and open the “Query Editor”

Edit Table Columns

Edit the following tables to trim the data being retrieved:

  1. LogisticsPostalAddressView
    1. Select the columns: City,CountryReqionID,RecId. Then right click and select “Remove other columns”
  2. CustGroup
    1. Select the columns: CustGroup,Name,DataAreaId. Then right click and select “Remove other columns”
    2. Add a computed field for identification. Click “Add Column” from the ribbon and then “Add custom column”. Set the new column name to CustGroupId and the formula as =[CUSTGROUP]&[DATAAREAID]
      Create CustGroupId field for identification
      Create CustGroupId field for identification

      Click Ok and this field will be added

  3. CustInvoiceJour
    1. Select fields: Custgroup, OrderAccount, InvoiceAccount, InvoiceDate, InvoiceAmountMST, SalesBalanceMST, SumTaxMST, InvoicePostalAddress, CreatedBy, DataAreaId, Partition, RecId. Right click and click “Remove Other columns”
    2. Create a new custom column “CustGroupId” similar to the one done for CustGroup Table
    3. Create a new custom column “InvoiceYear”. the

Once the above is done, Select “Close and load” button, located in the “home” ribbon bar

Relationships

So now that the tables have been defined, we need to set the relationships between them. Relationships in Power Bi, just like powerview in Excel, can only be linked via one field. Hence, why we added a computed column in the tables CustGroup and CustInvoiceJour

Click on “Manage relationships”

Delete any relationships in there (Currently there is one based off the custgroupId, but we shall delete it to make sure we add it all correctly)

  1. Relate CustInvoiceJour > CustGroup
    1. Add a new relation linking CustInvoiceJour to CustGroup using the CustGroupId on both Tables.

      Relation between CustInvoiceJour and Custgroup
      Relation between CustInvoiceJour and Custgroup
  2. Relate CustInvoiceJour > LogisticsPostalAddressView
    1. Add a new Relation and related CustInvoiceJour to LogisticsPostalAddressView using the InvoicepostalAddress to the RecId. See image for details

      Relation between CustInvoiceJour and LogisticsPostalAddressView
      Relation between CustInvoiceJour and LogisticsPostalAddressView

You should now have 2 relations setup. Close the Manage relations window and we will create our report

Creating the report

Bar Chart

in the field list at the right side of the screen, select the fields Name from the Custgroup table and then the InvoiceAmountMST field from the CustInvoiceJour Table. This will create a Table. Select the Bar chart option on the right side and it will change the view

Map

Click on a blank area in the report. From the field list select InvoiceAmountMST from the CustInvoiceJour Table and then CountryRegionId from the LogisticsPostalAddress. Then select the Map view

Sales by region over time

This will show how sales figures have been flowing by the countries
Click on a blank area in the report canvas. From the field list select InvoiceAmountMST and InvoiceYear from the CustInvoiceJournal. Make sure the InvoiceYear is on the X-Axis. Then select CountryRegionId from LogisticsPostalAddressView. Change the graph type to Line

Hoping to create more reports and make them available

AX 2012 Install Model – A Checklist


Embed from Getty Images

This is a checklist I follow to install Models Provided for a installed site

  1. Stop the AOS
  2. Backup the Database
  3. Install models (follow guidelines, you may be told to use the overwrite option)
  4. Merge Code (optional)
    1. Start AOS
    2. Merge Code
    3. Stop AOS
  5. Compile X++
    1. Option 1 – AxBuild.exe (RU7 and Onwards)
      1. Make sure AOS is turned off
    2. Option 2
      1. Start AOS
      2. Open Ax Client
      3. Compile
      4. Stop AOS
  6. Compile CIL
    1. Start AOS
    2. Open Ax Client
    3. Run Full CIL Compile
  7. Restart AOS
  8. Synchronize database
  9. Restart AOS

If you do restore a Live database over the TEST database, make sure to transfer the parameters as well as reset the SYSSQMSettings.GlobalGUID

AX2012 – Compare model. When you put in hotfixes, updates and want to do less – Part 1


via Tamás Mészáros

So you got updated Model(s) or hotfix(es) and need to make sure your ISV or VAR or CUS / USR layer code has been merged.

Lets see what your options are:

  1. Create a project for each model and then compare then to the upper layers for changes.
    — You spend a lot of time giving yourself carpal syndrome
  2. Auto code upgrade
    — It only manages hotfixes. The code that needs to be manually reviewed can be overwhelming? (someone please correct me on that)
  3. You write some script to find out how to do this
  4. Magic beans
    — You don’t get them

Ok, so I wrote some SQL scripts to do this, however it was getting too complex for me and I knew I could do it faster with in memory processing with C#. Although I can argue with myself that SQL can do a better job with this. However, I went to the path of C#

Tables used:

  1. SysElementType – This stores what a node in the AOT is classified as. E.g. TableInstance, ClassInstanceMethod, BaseEnum. This is purely to translate an integer to a human readable value.
  2. SysModelElement – This is where the definition of each object is. So every node in the AOT is one record here. This record also links it to the Parent Element. So a record which is the table field, will have its parent element to the Table itself. A parent element can have another parent element too, e.g. a form element inside a group
  3. SysModelElementData – This is where the code for the SysModelElement is stored. And it it stored for each Model (not layer). Moment I override the code in the USR layer for the SalesFormLetter classdeclaration in Ax, it will create a record in this table with the new code. So use it as a caution, if you ever query this table, be careful because you could be transferring a “lot” of data across the wire

Combining SysModelElementData, i can find what all other models use the same element. Then link it up to sysModelElement and find more information about the AOT object that has changed.

Armed with this knowledge I went and created  C# console application

The project currently compares models. So if you want to know what has affected the VAR and USR layer models with the addition of a particular SYP layer model, then you send the list of SYP model id’s and then the list of VAR and USR layer models as a csv string. This does a fast compare and outputs what objects you need to look at.

The shortcomings / bugs of this so far:

  1. Security, menu items dont get compared because of the way this is stored
  2. Currently you need to compare models. I would like to say i need to know what objects are affected by these SYP models and then get a list of objects on all the higher layers
  3. Need to create a library instead of a console project

This project is posted on GitHub: https://github.com/shashisadasivan/AxCodeCompare.git

AX2012 Terminating a compile worker


Moral of the story: NEVER KILL A COMPILER WORKER WINDOW EVER

There was a time I “accidentally” closed a compiler widow of a 10 worker process in the 2nd run.
Even though the main window notified that a worker terminated without any reason code, it still continued the process and reported that the compile was successful. Including an error log with a clean compile log (except for warnings, who looks at warnings!!! 😉 )
The next part which is compile the CIL went a bit weird.

The CIL compiler would start and AX stared at me for about a minute (as if its doing something, even the processors reported a good 98% usage for the AOS process) and then the AX screen came back to life.

The weirdness continues

  • It didn’t report anything.
  • There was no infolog to tell me that the services were generated, or the CIL compiler had failed.
  • There was nothing in the event log.
  • I even did a AOS restart with the deletion of the xppil folder, and yet I got the same result.
  • There was no CIL compiler log generated either.

So turns out (whatever happens inside the engine) that you should NEVER KILL A COMPILER WORKER WINDOW EVER !

via arkansas-investigations.com

[AX2012] Compare objects between models


SQL Makes everything better

When ever adding a new hot-fix or a new model from another source we need to compare the code touch points between that and existing code.

Ax makes that easy using the compare layers tool, however that only compares layers

So the other way is to go code upgrade and use those tools which may or may not work depending on your case (for example you put a 3rd part model and want to compare what var or cus or usr layer code you need to change)

This brings to writing x++ code or so in yummy SQL

Requirements:

  1. Know what models have changed

  2. Know what models will be affected / need to be compared

Know what models have changed

You know what hotfixes have been put in and there may be more than one. so note the numbers of the hotfixes or models that were added. Use the Get-AxModel command to get the models Ids for them

Know what models will be affected / need to be compared

You will probably need to know all the Models Id’s from the higher layers. So if you have a hotfix that you added then take a note of all model id’s that are a part of isv, isp, var, vap, cus, cup, usr, usp …. you know what i mean

The Fun begins with SQL

select et.ElementTypeName, et.TreeNodeName, med1.ParentHandle, med1.ElementHandle, me.* from ModelElementData med1
join ModelElement me
on ((me.ElementHandle = med1.ParentHandle and med1.ParentHandle <> 0)
or (med1.ParentHandle = 0 and me.ElementHandle = med1.ElementHandle))
join ElementTypes et
on et.ElementType = me.ElementType

where med1.ModelId in (<model ids changed in csv>)
and med1.ElementHandle in (select medisv.ElementHandle from ModelElementData medisv
where medisv.ModelId in (<model ids affected in csv>))
order by me.ElementType

That gives us a result set with main element type and the path to it.

Hopefully this gives an easier way to figure out what has changed and reduces your time. If this post does help you let me know, I am planning to invest in a query to further give more details about each element.

[AX2012] The AXRDCE extension caught an unexpected exception for report AutoReport


One of the errors received when running Print from a Form in AX 2012 (File > Print > Print, Or Ctrl + P) is the following:

The AXRDCE extension caught an unexpected exception for report AutoReport.

With the following message in the infolog

The error message was:
 Unable to find appropriate service endpoint information in the configuration object.
 Unable to find appropriate service endpoint information in the configuration object.
    at Microsoft.Dynamics.AX.Framework.Services.Client.Configuration.CustomConfigurationChannelFactory.UpdateServiceEndpoint(ServiceModelSectionGroup group, ChannelEndpointElement selectedEndpoint, ServiceEndpoint serviceEndpoint)
    at Microsoft.Dynamics.AX.Framework.Services.Client.Configuration.CustomConfigurationChannelFactory`1.CreateDescription()
    at System.ServiceModel.ChannelFactory.InitializeEndpoint(String configurationName, EndpointAddress address)
    at Microsoft.Dynamics.AX.Framework.Services.Client.Configuration.CustomConfigurationChannelFactory`1..ctor(Configuration configuration)
    at Microsoft.Dynamics.AX.Framework.Services.Client.Configuration.ChannelFactoryConfigurator.CreateClientChannelFactory[T](Configuration explicitConfiguration)
    at Microsoft.Dynamics.AX.Framework.Services.Client.Configuration.ClientConfigurationInternal.CreateXppChannelFactory[T](String relativeUri)
    at Microsoft.Dynamics.AX.Framework.Services.Client.XppServiceClient`1.CreateChannelFactory()
    at Microsoft.Dynamics.AX.Framework.Services.Client.ServiceClientBase`1.get_ChannelFactory()
    at Microsoft.Dynamics.AX.Framework.Services.Client.ServiceClientHelper.InvokeChannelOperation[TResult,TChannel](IServiceClient`1 client, Func`2 operationInvoker, Func`2 exceptionWrapper)
    at Microsoft.Dynamics.AX.Framework.Reporting.Shared.SRSFrameworkServiceProxy.Microsoft.Dynamics.AX.Framework.Reporting.Shared.IXppSRSFrameworkService.getRelationLinkTargetList(SRSFrameworkServiceGetRelationLinkTargetListRequest request)
    at Microsoft.Dynamics.AX.Framework.Reporting.Shared.SRSFrameworkServiceClient.Microsoft.Dynamics.AX.Framework.Reporting.Shared.ISRSFrameworkService.GetRelationLinkTargetList(String tableFieldsListEncodedStr)
    at Microsoft.Dynamics.AX.Framework.Reporting.Shared.TableFieldMenuItemService.GetRelationLinkTargetList(String fieldList)
    at Microsoft.Dynamics.AX.Framework.Reporting.Shared.TableFieldMenuItemService.Resolve(String encodedString)
    at Microsoft.Dynamics.AX.Framework.Reporting.Shared.AutomaticDrillThroughStep.Transform(XDocument report, ILocalReportContext reportContext, ILocalUserContext userContext)
    at Microsoft.Dynamics.AX.Framework.Reporting.Shared.CustomizationExtensionImplementation.ProcessReportDefinition(Byte[] reportDefinition, ILocalReportContext reportContext, ILocalUserContext userContext, Byte[]& reportDefinitionProcessed)

This Probably points to a corrupt configuration for the AIF services

What one may require to do is to Reconfigure the services by going to the Microsoft Dynamics Ax Configuration Utility and click the “Refresh” button in the “Connection” tab.

This should refresh the links to the WCF services.
Open the client using this updated config file and try it again

Other solutions that may work for you:
AX 2012 – Fatal AXRDCE Exception Error Addressed
Error printing within AX (To the screen and Physical printer)

AX 2012 – Model dependencies and Install Order – Part 2 – The app


Credit Nasa

From the follow up from Part 1, this post is more about how to find what models are dependent on what. I have created a console app which outputs csv files and can be downloaded here.

The inner workings:

So we know that ModelElement and ModelElementData hold the AOT objects for the respective models.

ModelElementData is the granular data. So a table field, a class method, a form control are stored here. They link to their parent using the ParentHandle field, or straight to the Main AOT object using the RootHandle.

The app takes every layer, from the ISV onwards , analyses each model and the model element data in them. It then looks if the element is used by any other layer below it OR if the root element is used in any other layer below or in the same layer.

How to use the App:

  • Set the minimum access layer – If you dont care about the SYS /SYP then set the Layer id to that of the ISV. Ofcourse if you work from VAR and beyond, then you probably dont care about ISV / ISP. So look at the Layer table and set the minimum access layer
    Set this in the Tag “MinApplicationLayer” inside the App.config file
  • Connection String – You dont need to set this up, unless you want to default to a specific Ax Database. This is good if you only care about one application. When the App runs, it will ask for user input for the database server and name. Not entering it will revert to the connection string in the App.config file

This is still a command line application, the output is still on the screen. But that’s the next phase of this app, to output the data to files or on screen. And also to make it power shell friendly

Ok, how the app works now:

Run the Exe in a command prompt: AxModel.Common.Exe (I will work on the name, I promise), put the database server, and the database name, and after  a few verbose output data in a CSV format is spit on the screen.

Copy that to excel and it should be all good from there.

The verbose output also outputs the dependencies if you want to look at it, which may be helpful.

Limitations: If you are referencing objects from one model to another in code, like a method in another object is  called from a class, then that will not be detected. This is mainly because cross reference will need to be looked at and is out of scope at this stage. I am focusing on installation dependencies

The code is available on GitHub and I should be updating it more as I conduct more tests and get more input about this.

Download Binaries