Save a Backup and Start the Process of Making Mass Changes in NAV RapidStart
Welcome back to our series of blog posts on how to use RapidStart to make mass updates or add data into NAV. In the previous blog post (part 2), we covered how to create a configuration package.
In this week’s post we will cover how to export the tables selected in the configuration package to Excel and one way to map changes in the file. If you’re following along with the intro, this will finish steps 2, 3 and 4. Subsequent blog posts will cover how to import, validate and apply changes to existing records and how to create new records using RapidStart.
Step 2 Continued
From the configuration package we want to export to Excel. First go to the Actions Ribbon and select “Export to Excel.”
You will get this message box:
Click on “Yes.”
Next you will be asked to choose where to save the file. Give the file a name and save it as an Excel File.
NAV will take a little time to download the tables and create a spreadsheet with a tab for each table. Once the file has downloaded, we will work in Excel for a little while.
Before we do anything with this Excel file, we must make a copy of the file so we have a backup in case we need to revert settings to the original. In Excel, do a “Save As…” and save a copy with a name that differentiates it as the backup.
Name the file which will carry the new or changed records stating that it is the “new” or “updated” file. From this point forward in these blog posts, only use the “updated” copy of the Excel file for the mass changes. In fact, it’s a good idea to close the backup file to avoid any confusion.
Now, with the new/updated Excel file we are using we want to create a mapping so that if the customer has X payment term, then the cash flow payment term will be Y. We want to do it that way because changing each customer record one at a time would be too time-consuming, and changing every customer record to the exact same cash flow payment could cause inaccuracies.
First we need to create a new sheet or tab in Excel. Click on the “+” button as in the image below. We will use this sheet to create the mapping and use it in the Customer records.
Next, go to the Customer sheet in the Excel file and select column C, being sure to select the entire column.
Copy column C into the clipboard by either typing “Ctrl-C” or right-clicking in the highlighted range of column C and selecting “Copy.”
Now go to the new sheet that you just created in the Excel file, and paste the clipboard by either typing “Ctrl-V” or right-clicking and selecting “Paste.”
After pasting the data into the new sheet, go to the Data Ribbon, then Data Tools and select “Remove Duplicates.”
The pop-up message box below will appear. Check the “My data has headers” box and click “OK.”
You will get something like this:
This reduced the list of Payment Terms used in the Customer table down to only the values that are actually used in that field of the table (below).
To organize the list, please sort alphabetically as it will be easier to work with this way.
Now we have a list of the Payment Terms. However, for Cash Flow Forecasting Purposes, we want to adjust the Cash Flow Payment Terms out 15 days in case our customers pay us up to half a month later. In our next blog post, we will show you how to map the Payment Terms to the Cash Flow Payment Terms to reflect a NET 30 for Cash Flow Forecasting purposes (step 5).