This is something that I did some days ago and I thought it was creative enough to be worth a blog post.

The task was to do a onetime mass update to the item records. There were more than 85.000 items in the company and they all had two production bin fields that needed to be updated according to a table with some rules provided in Excel. For items starting with ’10’ the two fields should be ‘RAW’ and ‘RAW’, for items starting with ‘11’ the two fields should be ‘TREATED’ and ‘CUT’, etc. like the below table, nothing strange.

Now, there are many ways of doing this;

If it was not that many categories and it was in a 2009 or earlier version I would just run the item table in the object designer, set a filter and use the Replace All function for each of the categories. But since it was quite a few categories and in a 2013 R2 version, then using Replace All will not work.

The other option would be to create a new table, copy/paste the value from Excel into the table and write a function to update the items based on the records in the new table. That would be an ok approach and quite quick. The downside is that you need to create a table just for a onetime change.

The third approach is to write a function that loops through all the item records (or use a report) and within the loop create an IF statement for each of the categories provided in the spreadsheet. The downside of this is all the IF statements you ned to write (and the worst thing I know is to write the same code over and over again).

This is where Excel comes into play. With some creativity in Excel you can get all those IF statements created for you and all you have to do is to copy/paste it back into the C/AL code and run the function. 🙂

Here is how it can be done;

Add the code for the IF statement with the modifications to the records in a cell in the Excel spreadsheet and where the item number part and bin codes are put something that can be replaced by Excel. In the case below I used ‘%1’, ‘%2’ and ‘%3’ (why not use some NAV syntax :-)).

Then in another cell in the Excel spreadsheet, create a formula and use the SUBSTITUTE function three times to replace the ‘%1’, ‘%2’ and ‘%3’ with the item number parts and bin codes. This then creates the proper C/AL code for the IF statement, just drag it down and you have all the IF statements you need.

This can then be copy/pasted into Dynamics NAV and placed within a loop like below (or place it a report).

Agree, it looks a bit weird to have the IF statement, the fields and the MODIFY statement all on the same row, but since this is a onetime update to the records it is less of a problem. Since we now, starting in version 2013, can have 240 characters on a single line of C/AL code this approach could be very useful.

Then it is just to run the function and all the records gets updated.

If, for some reasons, the bins are going to be changed again, I just change them in Excel and copy/paste the code into Dynamics NAV again and run the function.

Quite nice and creative I think. I will most likely use this approach again if I get the same request; it is quick and eliminates having to manually write multiple lines with the same code.