DataUpdate Through X++ Scripts (Advanced)

Sometimes it's just better to roll up your sleeves and write a little bit of X++ code is when it comes to updating masses of data. I’m not talking about updating 100 or so records because you can use Excel for that, but if you need to update 1,000’s or 10’s of thousands of records then Excel can be a little slow, and a script is so much faster.

How To Do It…

In this example, I want to update the Calculation Group on all of my released products – because I forgot to load it through DIXF.

1. To create the script, just open up the AOT by pressing CTRL+D.
2. And then press CTRL+SHIFT+P to open up the Projects explorer.
3. Right-mouse-click on the Projects folder and select the Project option from the New submenu to create a new Project.
4. Then right-mouse-click on the project and select the Open option to open up the project itself.
5. When the Project is displayed, right-mouse-click on the header and select the New menu, and then the Job option.
6. This will open up an X++ scripting pane for you to write your code within.
7. Now is the dirty part. Create a variable that points to the table you want to update – in this case it’s the InventTable by adding this line of code: InventTable item;

1. Then create a loop that will step through every record by typing this:
   while select forUpdate item
   {
   }

1. Then add your code to see if there is a record in the table to update:
   if (item)
   {
   }

1. Finally, add your code to update the record:
   ttsBegin;
   item.BOMCalcGroupId = “DEFAULT”;
   item.update();
   ttscommit;

1. The full code will look like this.
   
   
   static void Job11(Args _args)
   {

      InventTable item;

while select forUpdate item

{

if (item)

{

ttsBegin;

item.BOMCalcGroupId = “DEFAULT”;

item.update();

ttscommit;

}

}

}

1. To run the job, just click on the green play button in the menu bar.

Now all of the records will be updated for you.