Dynamics Ax 2012

Stopped (unrecoverable) workflow dynamics ax 2012 R3

January 31, 2017 by alirazazaidi

Yesterday I face a problem; some Ledger journal records are stuck into Workflow. In Error message, described about issues in customization. In the after last deployment code full cil was not generated. So first step is full Cil generation.

For resubmit these pending records, I used the code snippet delivered on Microsoft Forum by Dick wenning. It help me to resolve ledgers records.

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

WorkflowTrackingStatusTable WorkflowTrackingStatusTable;

while select WorkflowTrackingStatusTable where WorkflowTrackingStatusTable.TrackingStatus == WorkflowTrackingStatus::Faulted &&

WorkflowTrackingStatusTable.WorkflowType == WorkflowTrackingStatusWorkflowType::Workflow

{

Workflow::resumeWorkflow(WorkflowTrackingStatusTable.CorrelationId);

}

I updated the code snippet based on reci id instead of all faulted records.

WorkflowTrackingStatusTable WorkflowTrackingStatusTable;

select * from WorkflowTrackingStatusTable where WorkflowTrackingStatusTable.RecId ==5637166344;

if (WorkflowTrackingStatusTable!=null)

{

Workflow::resumeWorkflow(WorkflowTrackingStatusTable.CorrelationId);

}

You can get recid from workflow history screen.

But one record remains unrecoverable. For this I explored tables as follow.

If we explore workflow tables, you find that main player tables are

SysWorkflowTable

WorkflowTrackingStatusTable

WorkflowTrackingTable

In workflowTrackingStatusTable ContexttableId feild contains the table id of ledgerjournaltable and contextrecid contains the records recid of ledgerjoural row on workflow is applied.

SysWorkflowTable correlation id is key relation between Sysworkflow.

In current scenario, when all records are recovered, but one record is not recoverable. The follow these steps, but you can use them but your own risk.

Get recid from workflowTrackstatusTable from workflow history. Or Use contextrecid of LedgerJournalTable.
Sql Query on workflowtrackingSTatusTable and get correlationId.
Query on SysworkflowTable workflowcorrelationid and get the reference sysworkflow table and get reference id
Query on Select WorkflowTrackingTable workflowTrackingTable.WORKFLOWTRACKINGSTATUSTABLE Which is based on recid of workflowTrackStatusTable.
Delete the records based on reference and reset the ledgerJournaltable status to draft.

For your reference I used following Select query.
select from WorkflowTrackingStatusTable where CONTEXTRECID =5637239972

select * from WorkflowTrackingTable where WorkflowTrackingTable.WORKFLOWTRACKINGSTATUSTABLE =5637166344

select * from SysWorkflowTable where workflowcorrelationid =’D4AF23DF-9CC1-40F9-B886-DAB1CD6B35DE’

update LEDGERJOURNALTABLE set WORKFLOWAPPROVALSTATUS=1 where lEDGERJOURNALTABLE.JOURNALNUM =’xyze’

The formatter threw an exception while trying to deserialize the message Dynamics Ax 2012 R3 SSRS

December 6, 2016 by alirazazaidi

On running report, following error message appear in info box.

The formatter threw an exception while trying to deserialize the message: There was an error while trying to deserialize parameter http://tempuri.org/:queryBuilderArgs. The InnerException message was ‘Element ‘http://tempuri.org/:queryBuilderArgs’ contains data from a type that maps to the name ‘http://schemas.datacontract.org/2004/07/XppClasses:SrsReportProviderQueryBuilderArgs’. The deserializer has no knowledge of any type that maps to this name. Consider using a DataContractResolver if you are using DataContractSerializer or add the type corresponding to ‘SrsReportProviderQueryBuilderArgs’ to the list of known types – for example, by using the KnownTypeAttribute attribute or by adding it to the list of known types passed to the serializer.’. Please see InnerException for more details.

Solution:

Stop AOS service.
Stop Sql Server Service.
Start SQL server Service.
Restart SQL Server reporting services.

Time field on SSRS shows time format Report Dynamics Ax 2012

October 28, 2016 by alirazazaidi

Today is another small tip,

I was developing a custom report. Queried table stored the date and time in separate fields ie. Transdate and transtime. On mapping the transtime to report, its showed time format. Instead of time value.

For it solution, I added the string field on report temp table and then convert the time with time2str function and map to the field.

Complete statement is below.

TableTmp.StransTime = time2Str(_Trans.transTime, TimeSeparator::Colon, TimeFormat::AMPM);

As result, report will display the trans time instead of its format.

Display methods in Report SSRS Dynamics Ax 2012 .

June 13, 2016 by alirazazaidi

Hi, All, today I have to modify the report. Interestingly this report was built on AOT Query, instead of Data provider class. Current Scenario I was working on Worker Report. The requirement was to show’s primary position and department. HCMWorker table has two display method returns these values.

We can add these display methods into reports by following way.

For example I am modify the out of the box HCMWorkersHiredInPeriod Report.

Now Problem appears, we need different values then provided by out of the box display methods. For example we need Position Title instead of Primary Position Id. for this Add new display method that will return the Position Title instead of Position Id, compile table synchronize table, restore the AOT Query and refresh the report data Set. If there is no error in your working environment you will find required methods in report Data set.

Cannot create a record in General journal entry (GeneralJournalEntry). Date: .The record already exists. Dynamics Ax 2012

May 19, 2016 by alirazazaidi

Hi, All today I got error while creating Leger entry

Cannot create a record in General journal entry (GeneralJournalEntry). Date: .The record already exists.

I found some issue with free sequence number in NumberSequenceList . I manually delete the free sequence number with following statement.

static void Job14(Args _args)
{
NumberSequenceList list;

while select forUpdate * from list where
list.NumberSequenceId ==5637145445 && list.Status ==NumStatus::Free
{
ttsBegin;
list.delete();
ttsCommit;
}

}

I got again got help from Andre answer on dynamics Ax community

Reference: https://community.dynamics.com/ax/f/33/t/138926

Workflow status and comments Query Dynamics Ax 2012.

May 11, 2016 by alirazazaidi

I have to explore Out of the box workflow status and comments. After dig down, I found following three tables are important to get all information.

WorkflowTrackingCommentTable

WorkflowTrackingStatusTable

WorkflowTrackingTable

Reference of row on which workflow is applied or run can be found workflowTrackingStatusTable two field ContextRecid and ContextTableId

SSRS for Dynamics Ax 2012, group sum based on condition

April 7, 2016 by alirazazaidi

Small tip today, In one of my custom SSRS report for Dynamics Ax 2012 on group total, requires conditioning,
For example sum did not include the value in the based on certain value of other field.

I used following expression to solve this problem.
=Sum(iif(Fields!FieldName.Value <> “abcdxyz”, Fields!FieldName.Value, CDbl(0)))
I took help from following link:

http://www.sqlservercentral.com/Forums/Topic1174757-150-1.aspx

Git with Dynamics ax 2012

April 5, 2016 by alirazazaidi

Recently I explored GIT as source control tool. As per work with Microsoft Technologies I worked with TFS and Source save. In Dynamics Ax 2012 context I found AX2GIT on codeplex and

https://github.com/Go-ERP/Ax2Git/wiki

https://github.com/Go-ERP/Ax2Git

https://ax2git.codeplex.com/

I download ax2git zip. After extraction I found XPO.

So I decided first to download its perquisites. i.e. Git and tortoise for Git. I used following links

Git from http://msysgit.github.io/

Tortoise Git from http://tortoisegit.org/

After Installing tortoise and Git, windows right click popup menu shows me git and tortoise options as follow

To check git works, I create a new folder and create Git Repository there by windows right click popup menu.

Following popup appeared

After click on ok, I found following pop up to show success message

Now I opened Dynamics ax and import the ax2GIt.xpo

Import it, it overwrite some Ax artifacts. When Xpo successfully imported, right click on any node in AOT => add-ins have Git menu.

Currently Ax2Git support only export.

I opened one of my existing project and export it GIT options. Right click on project node when project open with all its detail. And click on add-ins=>Git=> Export AOT selection.

And click on Export AOT selection

As I am working Dynamics Ax 2012 CU8 demo vm so current layer option will. Other as per documentation, it will create separate branch for each layer.

Export to folder process starts

And I target folder I found folders in all files are exported properly.

Selected all folder and commit the changes.

All files are successfully committed.

For testing it reflect changes, I open Ax form and add following empty comment in one of the form

On again export project from AX. Repository folder it shows some changes are not commit.

On checking the right click to see the diff

Yellow highlighted items line it shows the difference, what previously checked in new exported code

Labels migration from one environment to other Dynamics AX 2012

March 9, 2016 by alirazazaidi

Usually I forget this step during code migration from environment to other. So I note it down for future reference.

The best way to move labels from environment to copy the *.lac file form location

C:\Program Files\Microsoft Dynamics AX\60\Server\MicrosoftDynamicsAX\bin\Application\Appl\Standard

And paste on some common folder in target environment.

And then import it by right click on aot => labels =right click and create from file.

Excel power query with Dynamics Ax 2012 R3

February 19, 2016 by alirazazaidi

End user in Pakistan industry love the Excel. I worked in local Pepsi franchise for two years. Everything they need raw data or reporting data into excel. They called it cycle. Because their office work cycle starts and end on excel. How much complex report you developed for them, if it not converted into excel it is useless (I was working in Oracle 6i reports at that time). So recently I decided to explore excel power query that helps to extract complex data from AX to excel. Later user can easily convert or format according to his / her needs. So here are some my key notes to connectivity with Excel power Query.

Currently for this article I used demo data and Microsoft demo vm AX R3 cu9.

For Excel power Query you have to download add in from following link. It works fine for excel 2013.

https://www.microsoft.com/en-us/download/details.aspx?id=39379&CorrelationId=be43bca0-3131-4c19-940f-f3d4f441efa2

When you open the above link in browser following screen will open

Click on download button. Following screen will open

As per my machine has 64 bit version of excel so I download 64 bit version of it.

After download run it setup.

Click on next

Finally click on finished button.

Now open Excel and you find following new tab will be added on tab strip.

If you did not find this tab in excel. Check the excel option and enable power query add-in. You can possibly found it at this location.

Now its time to connect excel with Ax. For this you have to verify that you AIF services are successfully configured and deployed on AX. If not then generate incremental CIL to verify that no error in your environment and AIF services are successfully working.

Now open Ax client and go in organization administrator module. You can follow menu link as

Organization administrator =>Setup=> Document management=> Document data source

On click on it you found following form.

As I am going to test on out of the box Query so I decided to use “USSalesUSMF2011”. You want to use your own query then select it in above screen and also activate it.

Now switch back to excel and from Power Query tab select “From Other Sources” => “From OData Feed”

From new screen select following Ax Query service with default url

http://localhost:8101/DynamicsAX/services/OdataQueryService

Query address will be vary if you are using excel add in on client and AOS on some other location.

Click on Ok. From next screen click on windows screen. As I am trying to connect on demo machine with default admin user so I select my current credentials. Otherwise you have to create a user in AX and he has to write Access on ODataQueryService and all tables in side Query to fetch data.

Click on next you find navigator from

Select required query. And click on ok.

You find data against query in Excel as following

Now if you expert in excel, you can make magic from here.

« Previous Page