Unified Operations & Dynamics AX Forum

Expand all | Collapse all

db field updating

  • 1.  db field updating

    SILVER CONTRIBUTOR
    Posted 15 days ago
    Edited by Mark Yankovich 15 days ago
    Hello,

    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!

    #AX2012
    #Beginner
    #Admin​​​

    ------------------------------
    Mark Yankovich
    Allegheny Bradford Corporation
    ------------------------------
    ​​​
    AXUG Summit - Post


  • 2.  RE: db field updating

    TOP CONTRIBUTOR
    Posted 14 days ago
    Mark,

    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
    Fastpath
    Des Moines, IA
    ------------------------------

    AXUG Summit - Post


  • 3.  RE: db field updating

    Posted 14 days ago
    Hi,

    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:

    (IMPORT PRODUCT & PRODUCT MASTER DATA THROUGH DATA IMPORT EXPORT FRAMEWORK - AX2012)
    https://daxusers.wordpress.com/2015/11/05/importing-product-product-master-data-through-data-import-export-framework/

    (AX 2012 | Import Released products using DIXF)
    https://community.dynamics.com/ax/b/shafeealabadiaxtutorials/archive/2015/09/07/ax-2012-import-released-products-using-dixf


    ------------------------------
    Regards,
    Chaitanya Golla
    Lead Architect
    ------------------------------

    AXUG Summit - Post


  • 4.  RE: db field updating

    Posted 14 days ago
    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)
         {
           ttsBegin;
           newProdIdent.clear();
           newProdIdent.Product = inventTable.Product;
           newProdIdent.ProductNumber = inventTable.ItemId;
           newProdIdent.insert();
           ttsCommit;
           C++;
           info("Item: " + inventTable.ItemId);
         }
       }
       info(int2str(C) + " items created in ecoresproductidentifier");
    }

    ------------------------------
    Robert Shannon
    ERP Analyst
    Leatherman Tool Group
    Portland OR
    ------------------------------

    AXUG Summit - Post


  • 5.  RE: db field updating

    TOP CONTRIBUTOR
    Posted 13 days ago
    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.

    Regards,

    ------------------------------
    Corey Vantilborg
    ERP Analyst
    Tigercat International Inc.
    Brantford ON
    ------------------------------

    AXUG Summit - Post


  • 6.  RE: db field updating

    TOP CONTRIBUTOR
    Posted 14 days ago
    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
    ------------------------------

    AXUG Summit - Post


  • 7.  RE: db field updating

    SILVER CONTRIBUTOR
    Posted 11 days ago
    Mark,

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

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

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


    ------------------------------
    Mark Yankovich
    Allegheny Bradford Corporation
    ------------------------------

    AXUG Summit - Post


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