Monday, June 20, 2011

How to update multiple records in one go - the Fill utility in Dynamics Ax explained

The Fill utility in Dynamics Ax is in my opinion one of the most undervalued features.  Maybe it's because this utility isn't that well known, so I decided to dedicate a blog post to it.

First of all: What is it?
The Fill utility allows a user to update multiple records in one go, setting a specific field with a certain value.  Without the help of a developer, without a lot of manual labour (ctrl+c - next - ctrl+v - next - ctrl+v ...).
Examples: Change the item group for a set of items.  Change the credit limit for a group of customers.

How does it work?
1. Open the form that contains the records you want to update.
2. Select the field you want to change and right-click it.  Select the Record info form in the popup menu.



3.  From the Record info form, select the button that reads 'Fill utility'.
4.  You'll get a query form, asking you for the records that needs updating.



Be carefull to set the right criteria in this stage!
5.  Next Ax show you the records that will be updated, according to the criteria you set earlier.  So again, review with care.
6.  In the last step you set the new value of the field you wanted to change (where it all started.)  Press OK and the change will be performed.  (Believe me, it's easier as it seems from the explanation above.)

Some notes:
Is the Fill utility not available?  You cannot find it?
This feature is controlled by a configuration key.  So make sure the administrator enables the configuration key, under Administration - Setup - System - Configuration.  The configuration key for the Fill utility is right under the first header, Administration.

There is also security key involved.  So if a users complains he/she doesn't see the Fill utility button in the record info screen, make sure this user is member of a group with the appropriate user rights.

What have you done?!
If you were a bit too quick pressing next/ok, you can always see afterwards what was changed.  Dynamics Ax keeps a log of updates performed by the Fill utility.  You can find this log under Basic - Inquiries - Fill utility Log.


Which fields are avaible for using the Fill utility?
Or maybe, which are not available.  Not available for use by the fill utility are the fields recid and dataareaid.  Also if the field you select is the primary key of the table (like the itemid in InventTable, accountnum in CustTable), you cannot use the Fill utility.
If the field you select is not a field of the underlying table but a datamethod instead, again you cannot use the fill utility.

2 comments:

  1. Hi,

    In form with many columns in the grid, the first coloumn need to be freezed(ie. EmplTable.EmplId).

    Can u tell me how to lock the first column, so the EmplId is still visible when the focus is at the end of the grid?

    ReplyDelete
  2. Great information...Your post the very informative i have learned some information about your blog thank you for Sharing the great information.....Microsoft Dynamics Ax Partners

    ReplyDelete