Custom FormDatasource lookup

The following sample demonstrates how to add custom lookup to field on an AX form

 public void lookup(FormControl _formControl, str _filterStr)  
 {  
   SysTableLookup     sysTableLookup = SysTableLookup::newParameters(tablenum(InventItemBarcode), _formControl);  
   Query              query = new Query();  
   QueryBuildDataSource  qbds;  
   ;  
    
   sysTableLookup.addLookupfield(fieldnum(InventItemBarcode, ItemBarcode), true);  
   sysTableLookup.addLookupfield(fieldnum(InventItemBarcode, Description));  
   sysTableLookup.addLookupfield(fieldnum(InventItemBarcode, LIQ_Sell_ItemId));  
   sysTableLookup.addLookupfield(fieldnum(InventItemBarcode, LIQ_UnitId));  
   sysTableLookup.addLookupfield(fieldnum(InventItemBarcode, LIQ_Primary));  
   sysTableLookup.addLookupfield(fieldnum(InventItemBarcode, LIQ_Active));  
    
   qbds = query.addDataSource(tablenum(InventItemBarcode));  
   qbds.addRange(fieldnum(InventItemBarcode, ItemId)).value(queryValue(RCMJournalItemTrans.ItemId));  
    
   sysTableLookup.parmQuery(query);  
   sysTableLookup.performFormLookup();  
 }  

SQL script for updating AX tables

The following SQL code is an example how to insert records into an AX table including generation of RecId's. One point to note is the SYSTEMSEQUENCES record for a table is only created when the first record is added to an AX table through the AX client.

 ALTER PROCEDURE loadLIQ_StoreItemVendorTable AS  
 DECLARE @NEXTVAL BIGINT,  
       @ROWCOUNT  BIGINT  
         
 SELECT DISTINCT 'sto' AS DATAAREAID, 0 AS RECVERSION, IDENTITY(BIGINT,0,1) AS RECID, RPT_T_VendorStoreItem.StoreID AS RCMSTOREID,  
 RPT_T_VendorStoreItem.ItemID AS ITEMID, EndVendorAccountNumber AS VENDACCOUNT, CHAIN.GROUPINGID AS CHAIN, INVENTITEMBARCODE.LIQ_Sell_ItemID, INVENTTABLE.ItemGroupId AS ITEMGROUPID  
 INTO #TEMP  
 FROM RPT_T_VendorStoreItem, INVENTTABLE, INVENTITEMBARCODE, RCMGROUPING AS STORE  
 INNER JOIN RCMSTORETABLE ON STORE.REFRECID = RCMSTORETABLE.RECID  
 INNER JOIN RCMGROUPING AS CHAIN ON CHAIN.GROUPINGID = STORE.GROUPINGARRAY2_  
 WHERE STORE.ROOTPARENT = (SELECT LIQ_GROUPINGIDCORELOCATIO30002 FROM RCMPARAMETERSVIRTUAL WHERE DATAAREAID ='CLV') and  
      RCMSTORETABLE.STOREID = RPT_T_VendorStoreItem.StoreID and   
      INVENTTABLE.ITEMID = RPT_T_VendorStoreItem.ItemID and  
      INVENTITEMBARCODE.ITEMID = RPT_T_VendorStoreItem.ItemID and  
      INVENTITEMBARCODE.LIQ_UNITID = RPT_T_VendorStoreItem.UnitOfMeasure and  
      INVENTITEMBARCODE.LIQ_ACTIVE = 1 and  
      INVENTITEMBARCODE.LIQ_SELL_ITEMID <> ''  
 ORDER BY RPT_T_VendorStoreItem.StoreID, RPT_T_VendorStoreItem.ItemID  
    
 SELECT @NEXTVAL = NEXTVAL FROM SYSTEMSEQUENCES WITH(UPDLOCK, HOLDLOCK)  
 WHERE ID = -1 and TABID = 30345  
    
 INSERT INTO LIQ_STOREITEMVENDOR  
 (DATAAREAID, RECVERSION, RECID, RCMSTOREID, ITEMID, VENDACCOUNT, CHAIN, LIQ_SELL_ITEMID, ITEMGROUPID)  
 SELECT DATAAREAID, RECVERSION, RECID = RECID + @NEXTVAL, RCMSTOREID, ITEMID, VENDACCOUNT, CHAIN, LIQ_SELL_ITEMID, ITEMGROUPID  
 FROM #TEMP  
    
 SELECT @ROWCOUNT = COUNT(*) FROM #TEMP  
    
 UPDATE SYSTEMSEQUENCES  
 SET NEXTVAL = @NEXTVAL + @ROWCOUNT  
 WHERE ID = -1 and TABID = 30345  
 GO