[Mark Yankovich]

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
——————————
???

[Alex Meyer]

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
——————————
——————————————-

[Chaitanya Golla]

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
——————————
——————————————-

[Robert Shannon]

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
——————————
——————————————-

[Corey Vantilborg]

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
——————————
——————————————-

[Mark Prouty]

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
——————————
——————————————-

[Mark Yankovich]

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
——————————
——————————————-