Quickly Update Data Through X++ Scripts

Normally I don’t promote to coding scripts within Dynamics AX because it is always perceived as something that a developer has to do. And for the power user, there are so many other ways to tinker with the system that getting your hands dirty with X++ is not necessary. One situation though that it’s sometimes 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.

Hopefully this worked example will demystify scripting a little and make you feel a little like a Whiz Kid. 

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 DIEF.

  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 sub-menu to create a new Project.
  4. This will create a new Project folder for you and if you click on it then you will be able to access the Properties window.
  5. Change the name on the Project so that it is a little more descriptive.
  6. Then right-mouse-click on the project and select the Open option to open up the project itself.
  7. When the Project is displayed, right-mouse-click on the header and select the New menu, and then the Job option.
  8. This will open up an X++ scripting pane for you to write your code within.
  9. 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.

That wasn’t as hard as you probably thought was it.

To view the original post and also download the walkthrough as a PDF with step by step images, click on the following link:

http://dynamicsaxcompanions.com/Tips-And-Tricks/Tips-Tricks-Archive/Quickly-Update-Data-Through-X-Scripts

Advertisements
6 comments
  1. Pat said:

    Letting users write scripts could result in serious data corruption :p You never know what other data could be updated just by calling the .update() method. I would still recommend asking a developper to assist in stuff like this.
    As for the script, it will crash without ttsbegin/ttscommit and the “if (item)” in this scenario is not needed. Also the following would be much faster for a large dataset:

    //start
    ttsbegin;
    item.skipDataMethods(true);
    update_recordset item
    setting BOMCalcGroupId = “DEFAULT”;

    ttscommit;
    //end

    Cheers!

  2. Pat, good point, although everyone should be a developer at heart and should not be afraid of scripting 😉

  3. civinge said:

    A big no to this! Not everybody should be scripting. The risks are huge, this applies to scripts found on the internet as well. A developer can make a judgement if the code does what is stated.

    +1 to Pat for an huge improvment of the suggested code.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: