D365 Finance & Operations and Dynamics AX Forum

Expand all | Collapse all

db field updating

  • 1.  db field updating

    Posted Jun 10, 2019 03:41 PM
    Edited by Mark Yankovich Jun 10, 2019 04:06 PM

    We are implementing AX 2012 R3 on premise for discrete manufacturing.  As implementations go, there are times when we learn that we do not have fields setup correctly.  For instance, the product type for our raw material items was imported as 'None', when it should have been declared as 'BOM'.  There are over 2,000 records that this needs changed in.  In a past life, it would be an update query to select records with 'None' and update the field to 'BOM'.  How can this be done in the AX environment?

    All we know how to do at this stage is to have the consulting group re-import, or call each record up one at a time and change.  Re-importing for as many times that we might find data the needs corrected is impractical.  Updating each record using the forms is far too time consuming.

    This is just one example.  We will have more items without doubt that need this type of attention.

    Our consulting group, as well as many on-line searches provide enough caution about updating through SQL that this approach has not been taken.

    Looking for guidance on what we can do now, and also the proper training to be able to do this in-house.

    Thank you!


    Mark Yankovich
    Allegheny Bradford Corporation

  • 2.  RE: db field updating

    Posted Jun 11, 2019 08:17 AM

    You can perform SQL operations in the X++ language just like you can in direct SQL although the syntax is slightly different. Then you would just execute your X++ either in a batch or just by creating a sample project.

    Here is some documentation around updating tables:
    update Table Method
    Microsoft remove preview
    update Table Method
    The where clause is optional. When used, the where clause specifies a condition for update to test while processing each row of the table. Only those rows that test true against the condition are updated with the new values.
    View this on Microsoft >

    Feel free to reach out if you have any questions.

    Alex Meyer
    Director of Dynamics AX/365 for Finance & Operations Development
    Des Moines, IA

  • 3.  RE: db field updating

    Posted Jun 11, 2019 11:34 AM

    Can you please let us know the import process you are following as this can be done either by DMF process through a small development in the DMF class(DMFProductEntity) or specifying the required value in the productType field in input template.

    Please refer below links:


    (AX 2012 | Import Released products using DIXF)

    Chaitanya Golla
    Lead Architect

  • 4.  RE: db field updating

    Posted Jun 11, 2019 12:34 PM
    You can also create a very nice and easy job to update. Below is a job that I created that went through all items and created an ecoresproductidentifier if it hadn't been created. You should be able to use this to what is needed. I would say, though, that you should run this in a test system before running it on a live system. Also please note, copying code in here appears to get rid of formatting, and this was a very fast / quick job created.

    static void MassCreateEcoResProdIdentity(Args _args)
       InventTable inventTable;
       ItemId itemId;
       EcoResProductRecId product;
       EcoResProductIdentifier prodIdent, newProdIdent;
       Counter C;

       while select itemid, product from inventTable
       outer join prodIdent
       where inventTable.Product == prodIdent.Product
         if(prodIdent.RecId == 0)
           newProdIdent.Product = inventTable.Product;
           newProdIdent.ProductNumber = inventTable.ItemId;
           info("Item: " + inventTable.ItemId);
       info(int2str(C) + " items created in ecoresproductidentifier");

    Robert Shannon
    ERP Analyst
    Leatherman Tool Group
    Portland OR

  • 5.  RE: db field updating

    Posted Jun 12, 2019 01:33 PM
    I think Robert's solution makes sense.  We use Jobs like this extensively to update data.   A couple of additional things to consider when using Jobs to mass update data.

    Use Set-Based operations when possible,  for improved performance: https://docs.microsoft.com/en-us/dynamicsax-2012/developer/update-recordset

    Be aware of the "do" methods on tables.  For example table.update() calls business logic associated with the table,  table.doUpdate()  does not call the business logic.  Using doUpdate, doInsert, doDelete is faster but carries the risk of inconsistent data.

    Finally,  make sure you have tested your Jobs in a non-Production environment, and make sure your back-ups are ready to go.


    Corey Vantilborg
    ERP Analyst
    Tigercat International Inc.
    Brantford ON

  • 6.  RE: db field updating

    Posted Jun 11, 2019 02:55 PM
    Does AX2012R3 still have the table Fill Utility as is in AX2009? Right click on record, choose Record info.
    This opened a viewable dataset with query builder to create a list, then to apply a mass change. ​

    Mark Prouty
    Programmer / Analyst
    ANGI Energy Systems
    Janesville WI

  • 7.  RE: db field updating

    Posted Jun 14, 2019 08:10 AM

    AX2012R3 does not have the Fill Utility per the f1 help.

              Fill utility
              Update a specific field for multiple records at one time.

             This control is not available if Microsoft Dynamics AX 2012 R2 or AX 2012 R3 is installed.

    Mark Yankovich
    Allegheny Bradford Corporation

If you've found this thread useful, dive deeper into User Group community content by role