Ali Raza Zaidi

A practitioner’s musings on Dynamics 365 Finance and Operations

Dynamics Ax 2012

Relation between VendPackingSlipJour and PurchParmTable Dynamics Ax 2012 R3

by

Couple of days ago, I was helping a young developer. Who was doing some customization. He added some customization in PurchParmtable and want information in VendPackingSlipJour Table.

It was very strange that there is no direct relationship between vendPackingSlipJour and  PurchParmTable.

 

After Exploring I found that There is ParmId field in VendPackingSlipVersion table.

And we can get VendPackingSlipVersion by static method with PurchId and PackingSlipId, DeliveryDate.

 

This ParmId has relation with PurchParmTable.

I wrote down following code snippet to get Value from PurchParmTable, with respect to VendPackingSlipVersion.

 

 

 

display real _netWeight()
{
PurchParmTable _purchParmTbl;
parmid _id;
_id=VendPackingSlipVersion::findFromInterCompanyPackingSlip((this.PurchId),
(this.PackingSlipId),(this.DeliveryDate)).ParmId;
select *from _purchParmTbl where _purchParmTbl.ParmId==_id;
return _purchParmTbl.NetWeght;
}

 

Hopes this help

ODBC connection and Null value Handling Dynamics Ax 2012 R3.

by

Today I have very small tip to share. I noted down, some odbc connection posts in my blog. All these post shares the code snippets , that used to open odbc connection and did query on SQL server directly. Couple of days ago, I need similar code snippet to read data from SQL server and Insert into another connect. But that code did not had reference to handling null values. So I note down that code snippet too, for future reference.

 

These all odbc connection , command return results in “ResultSet” class.

 

This ResultSet  class has method  “wasnull”. According to documentation, its behavior is strange. First we have to call the get method and then we call “WasNull” to verify that value appears is null or not.

https://msdn.microsoft.com/en-us/library/resultset.wasnull.aspx

 

So whole code snippet will be like.

public void run()
 

{

Connection Con = new Connection();

ResultSet R;

str sql;

Statement Stmt;

SqlStatementExecutePermission permission;

Real _RealValue;

sql = "SELECT * FROM InventTrans ";

Stmt = Con.createStatement();

permission = new SqlStatementExecutePermission(sql);

permission.assert();

R =Stmt.executeQuery(sql);

 

while ( R.next() )

{

_RealValue =R.getReal(1);

if (  R.wasNull(1) ==boolean::false)

{

 

info (" Not null");

}

Else

{

Info (" Null ");

}

 

 

 

}

CodeAccessPermission::revertAssert();

 

}

Select on InventSum vs InventOnHand Class Dynamics Ax 2012 R3.

by

Today I face the very strange behavior of X++ code.

We develop customization. We generate transfer and issuance through X++.

Logic was very simple,If stock is available then transfer  / Issuance or Adjustment journal create otherwise generate error message, We took this decision  based of query on Inventsum. It works fine in most of cases but In one case when stock is zero it fails. We create Movement Journal to generate stock in that Item variant.  The select query always return Zero stock in posted quantity. Even I get the recid in debug mode and placed in table explorer, Quantity is available in table explore.

This case occurs when got Zero stock in Inventsum table and we try to increase stock by movement journal, other wise select on InventSum works fine.

 

The query will be very similar

 

Select * from InventSum where Inventsum.ItemId == line.ItemId && inventSum.invetdimId == line.fromInventiDimId;

 

This will not work. Even Db synch, Inc Cil, Cache clear done, even Restart the Test server AOS.

 

I have to switch code from select query to Class InventOnhand. Very similar to following.

 

InventOnHand inventOnhand

InventDim       InventDim;

InventDimParm inventDimParm;

 

 

inventDimParm.initFormInventDim(_Line.FromInventDimId);

inventOnHand = InventOnhand::newParameters(_line.Itemid,_line.FromInventDimId,inventDimParm);

 

If (inventOnHand.availPhysical() > _line.RequestedQty)

{

Info( ” Quantity Avalible “);

}

Else

{

Info (” Not available “);

}

 

Its works perfectly fine. I think  InventonHand class is better the do direct query on Inventsum table.

 

Skip certain values Report group sum SSRS Dynamics Ax 2012 R3. Dynamics 365 for operations

by

There is again small tips. Let me share you again scenario. Suppose you have to show customer Sale With customer group level. And group footer want to sum of Sales of that group. Now requirement is that customer with certain sub classification will skip in group sum. For example those customer who are on hold or blocked will not shown in report group sum, but shown in report detail

I was developing RDP based reports. So I add a int field in table. So I can mark specific record need to skip at group level sum.

I update the following SSRS expression to skip certain records at group level.

=Sum(iif(Fields!Flag.Value= 0, Cdbl(Fields!Value.Value), 0.0),”Group Name”)

Data manipulation tip 2 Dynamics ax 2012 and D365 of Operations Table initialization method.

by

If we consider the following code snippet.
CustSalesQty _temp1,_temp2;
_temp1.IntQty=100;

_temp1.CustAccount =”Cust00001″;

_temp1.insert();

_temp1.IntQty=200;

_temp1.insert();

 

_temp1.IntQty=300;

_temp1.CustAccount =”Cust00003″;

_temp1.insert();

while select * from _temp1

{

_temp2.data(_temp1);

_temp2.insert();

 

 

 

}

 

while select * from  _temp2

{

info( ” _temp2 with ” + _temp2.CustAccount + ” ” + int2Str(_temp2.IntQty));

 

}

 

 

}

You can check that when we insert the temp1.inQTY=200, We did not set Customer account. At result record new record will insert with customer Cust00001 . You can see output of above code

 

 

Point is simple, We need to initialize the table before populate for next values.  I found this scenario in custom Inventory reports. When we populate data in different fields based on issue status.

You can set initialized values after ate in side query execution loop. But that piece of code belongs to specific location.

Microsoft provide the out of the box base initValue method. You can add this method by following way.

Best practices says you have to initialize value in this method. And call this method with table reference. This way initialization is table dependent not class dependent.

 

So we can over write the code as follow

public class CustSalesQty extends common

{

/// <summary>

///

/// </summary>

public void initValue()

{

super();

this.IntQty =0;

this.CustAccount =””;

}

 

}

Now above code snippet will works as

class Job123

{

/// <summary>

/// Runs the class with the specified arguments.

/// </summary>

/// <param name = “_args”>The specified arguments.</param>

public static void main(Args _args)

{

CustSalesQty _temp1,_temp2;

_temp1.initValue();

_temp1.IntQty=100;

_temp1.CustAccount =”Cust00001″;

_temp1.insert();

_temp1.initValue();

_temp1.IntQty=200;

//  _temp1.CustAccount =”Cust00002″;

_temp1.insert();

_temp1.initValue();

_temp1.IntQty=300;

_temp1.CustAccount =”Cust00003″;

_temp1.insert();

while select * from _temp1

{

_temp2.initValue();

_temp2.data(_temp1);

_temp2.insert();

 

 

 

}

 

while select * from  _temp2

{

info( ” _temp2 with ” + _temp2.CustAccount + ” ” + int2Str(_temp2.IntQty));

 

}

 

 

}

 

}

Stopped (unrecoverable) workflow dynamics ax 2012 R3

by

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’