Displaying an image on a form grid in Dynamics AX

Sometimes it is useful to display an image in  a data grid on a from to highlight certain records e.g. records with validation errors as in the example below:

To achieve this requires 2 main steps:

1. Adding a display method on the table/form datasource that returns an ImageRes

 //BP Deviation Documented  
 display ImageRes errorExist()  
 {  
   #resAppl;  
   return this.ErrorLogged ? #ImageError : #ImageBlank2;  
 }  

2. Adding a new field control on the form by right-clicking on the Grid control within the form design and selecting New Control > Window

 

Set the properties of the new control as follows:

Filtered lookups on SysQueryForm

I was asked to filter the lookup on the JournalNum field in the Select Query form. Nornally the lookup on this field would display all JournalNums, but the client only wanted to see Vendor Payment journals.

To achieve this I modified the SysQueryForm, specifically the lookup method on the RangeValue field of the Range dataset.

What my code does is use a custom lookup filtered by JournalType.

 public void lookup(FormControl _formControl, str _filterStr)  
 {  
   SysTableLookup   sysTableLookup;  
   SysDictField    sysDictField;  
   QueryBuildRange   rangeJournalType;  
   Query        query;  
   TmpSysQuery     tmpSysQuery;  
   ;  
   sysDictField = new SysDictField(Range.Table_Id, Range.Field_Id);  
   if (sysDictField.typeId() == extendedTypeNum(ledgerJournalId))  
   {  
     //Search range records for LedgerJournalTable.JournalType so that we can use this to filter the lookup for JournalNum  
     for (tmpSysQuery = Range_DS.getFirst(); tmpSysQuery; tmpSysQuery = Range_DS.getNext())  
     {  
       if (tmpSysQuery.Table_Id == tableNum(LedgerJournalTable) &&  
         tmpSysQuery.Field_Id == fieldId2Ext(fieldNum(LedgerJournalTable, JournalType), 1))  
       {  
         break;  
       }  
     }  
     // If a range value exists for JournalType then perform a filtered lookup of JournalNum  
     if (tmpSysQuery.RangeValue)  
     {  
       sysTableLookup = SysTableLookup::newParameters(tableNum(LedgerJournalTable), _formControl);  
       sysTableLookup.addLookupfield(fieldNum(LedgerJournalTable, JournalNum));  
       sysTableLookup.addLookupfield(fieldNum(LedgerJournalTable, JournalName));  
       sysTableLookup.addLookupfield(fieldNum(LedgerJournalTable, Name));  
       sysTableLookup.addLookupfield(fieldNum(LedgerJournalTable, JournalType));  
       query = new Query();  
       SysQuery::findOrCreateDataSource(query, tableNum(LedgerJournalTable)).addRange(fieldnum(LedgerJournalTable, JournalType)).value(tmpSysQuery.RangeValue);  
       sysTableLookup.parmQuery(query);  
       sysTableLookup.performFormLookup();  
     }  
     else  
     {  
       SysLookup::lookupRange(_formControl, range, sysQueryForm.query());  
     }  
   }  
   else  
   {  
     SysLookup::lookupRange(_formControl, range, sysQueryForm.query());  
   }  
 }  

Deleting duplicate records via SQL

 WITH DUP_BARCODE (ITEMBARCODE, DUPCOUNT) AS  
 (SELECT ITEMBARCODE, COUNT(RECID) AS DUPCOUNT  
  FROM INVENTITEMBARCODE   
  GROUP BY ITEMBARCODE  
  HAVING COUNT(RECID) > 1)  
    
  DELETE FROM INVENTITEMBARCODE  
  FROM INVENTITEMBARCODE AS I1  
  JOIN DUP_BARCODE ON I1.ITEMBARCODE = DUP_BARCODE.ITEMBARCODE  
  WHERE I1.RECID != (SELECT TOP 1 RECID FROM INVENTITEMBARCODE AS I2 WHERE I2.ITEMBARCODE = I1.ITEMBARCODE ORDER BY I1.CREATEDDATETIME ASC)  

Get AOS server name for current session

The following code obtains the AOS server name for the current session:

 static void getServerNameForCurrentSession(Args _args)  
 {  
   sysClientSessions cliSessions;  
   sysServerSessions svrSessions;  
   ;  
   select svrSessions  
     exists join cliSessions  
       where cliSessions.SessionId == sessionID()  
         && cliSessions.ServerID == svrSessions.ServerId;  
   info(substr(svrSessions.AOSId, 1, strfind(svrSessions.AOSId, '@', 1, strlen(svrSessions.AOSId))-1));  
 }  

SELECTING latest INVENTRANS record by ItemId and InventDimId

On the project I'm currently working on we had a request to produce a list of the latest INVENTTRANS record per Item and InventDimId. After a little bit of work I came up with the following SQL query.

 SELECT DISTINCT INV1.ITEMID, INV1.INVENTDIMID, INV2.DATEFINANCIAL, INV2.QTY, INV2.COSTAMOUNTPOSTED FROM INVENTTRANS AS INV1  
 JOIN INVENTTRANS INV2 ON INV2.ITEMID = INV1.ITEMID and INV2.INVENTDIMID = INV1.INVENTDIMID  
 WHERE INV2.RECID =  
 (SELECT TOP 1 RECID FROM INVENTTRANS WHERE ITEMID = INV2.ITEMID and INVENTDIMID = INV2.INVENTDIMID ORDER BY DATEFINANCIAL DESC, CREATEDDATETIME DESC)  

Master scheduling run for All Items not picking up level 0 items

Master scheduling had been working fine for a number of days following Go-live, but then something happening and alot of items were not being picked up in the master scheduling run. The symptoms reported by the user was that the process was only running for 10 minutes, where initially it was taking 2 hours to run. Also, no errors were being reported. I suspected a data issue and after a several hours of investigation I finally found the issue. Somehow an InventTable record with a blank ItemId had been created in the database. In the Master Scheduling code that builds the item list for each level there's an If statement that checks the ItemId and if it's blank exits the loop and because this blank item was the first item processed for the level, no items were being selected for this level for the master scheduling run.

Following on from the discovery of this issue and the workaround of deleting the blank item from InventTable, the blank item kept reappearing in the database and it was decided to modify the Master Planning code to discard items with a blank ItemId.

This was done by making a simple change to the createListsFromQueryRun method of the ReqTransCache_Periodic class which builds the item lists used by master planning.

   while (runQuery.next())  
   {  
     inventTable = runQuery.get(tablenum(InventTable));  
    
     // Discard any items with a blank ItemId  
     if (inventTable.ItemId == "")  
     {  
       continue;  
     }  
    
     if (!levelItemMap.exists(inventTable.bomLevel))  
     {  
       itemSet = new Set(Types::String);  
       levelItemMap.insert(inventTable.bomLevel, itemSet);  
     }  
     else  
       itemSet = levelItemMap.lookup(inventTable.bomLevel);  
    
     itemSet.add(inventTable.ItemId);  
    
   }  

Locking records in SQL Server 2008

 USE DynamicsAx3_DEV  
 GO  
    
 SET TRANSACTION ISOLATION LEVEL  
 REPEATABLE READ  
 GO  
    
 BEGIN TRANSACTION  
 select * from RCMCHANGES (UPDLOCK) where STOREID = '0969' or STOREID = '0976'  
    
 ROLLBACK TRANSACTION  
 GO