Map Data Changes in Excel Before Making Changes in NAV RapidStart
Hello and welcome back to our series of blog posts on RapidStart in Microsoft Dynamics NAV.
To this point, we have asked you to prepare the Excel file (part 3, steps 3 and 4) for data mapping for making changes to existing records. Now, we are going to show you how to create the map and use it in the Excel file.
During our last blog post we created a list of Payment Terms being used in the Customer table. The finished table looked something like this:
Excel has a number of ways to map changes of data. One way is to look at the list of Payment Terms and then in the column to the right of it, enter in the new Cash Flow Payment Terms. After that you can use a VLOOKUP in the Customer data tab to map new and old terms.
That would end up looking something like this in the mapping tab:
And something like this in the Customer data tab:
The VLOOKUP in the image above looks a bit different than a normal VLOOKUP because the elements of the formula are looking into the table that was downloaded from NAV. Regardless of this, the VLOOKUP functions normally so the results are still the same.
You could also use other Excel formulas, such as IF. It all depends upon the change you want to make, how you want to make that change and what functions you are most comfortable using. As long as the result in Excel is correct, the method does not matter. Please find a helpful link here on the VLOOKUP function in Excel.
Once you have entered in the formulas and have derived what the new values for the Cash Flow Payment Terms should be, then copy the new Cash Flow Payment Terms into your clipboard and paste/special values into the Cash Flow Payment Terms.
In the example above it would be the values in column F and you would paste/special values into column D (without changing the header rows of column D).
Once the correct values are in the Cash Flow Payment Terms, please click on the filter for Cash Flow Payment Terms Code to make sure all of the results look correct (image below). Make sure that there are no error messages, like “#N/A” and check that the Blank Values are correct too.
If there are any “#N/A” errors, these indicate that a matching value was not found so please review the formula and the mapping values. The values in the map have to match exactly for the VLOOKUP to work.
If there are blank values, this means that the formula found a corresponding match but that the match does not have an updated value. Please check the mapping values to make sure every record is mapped appropriately.
At this point, please make sure to save the file.
In this blog post we demonstrated a way to map changes in Excel and save the updated file. All of the instructions above were about how to prepare to change existing records.