When bringing expenses from another system into Dynamics NAV or Business Central the expense file downloaded can have a different name every download. With NAV/BC you need to have an additional offset total that is the sum of all expenses but is a negative amount. Since the files will be named differently each time, we would use a folder data source to process the nightly download from your expense system. In this scenario, we are using Concur process to download files nightly via a scheduled FTP process. We would still use a folder data source map to process the file nightly. However, our destination of this map will be an Excel file. The reason the destination is an Excel file, we can control the name of that file, and use it in a second map.
The second map would use an ODBC connection to the Excel file, so that we can query the data from the sheet created AND be able to do a “union” to add the total line with the sum of the amounts as a negative amount. The second map would then create the Purchase Invoices in NAV/ BC. This may seem very complex, but it is actually fairly simple. Let’s take a look.
Here is the first map source definition, you will see it is a normal folder data source map:
For the destination, we are going to an Excel file directly. The reason again, for the second map to an ODBC connection we need an Excel file with the same name each time.
For the destination mapping we created two calculated field, one we called it “GROUPBY” and set it to a value of “TOTAL”. The reason to add this with the same value on every line so we can group by this column to get the sum of all the expense lines.
To get all lines to show on a purchase invoice, we sometimes need to manually add a line number to the output file. We can accomplish that with a calculate field returning a global variable, which we named GBL_LINE_NUMBER. To get the line number to increment for each line written to the file we will add code to the restriction section of the destination:
This increments the global variable by 1 for each line processed. We will create the second calculated field we called LINENUMBER to return the global variable:
Once we get the restriction and the calculated fields created, we are ready to map our fields. The easy way to map all columns across, is to select the following, Destination Options – Copy Source Columns:
Save the destination mapping and run this first map. This will create the Excel file, and we don’t care the order of the columns, we need the Excel file created in order to create the second map.
For the second map, we will do a Bulk Data load map with an ODBC connection to the Excel file:
Here is the query, your column names will be different, but wanted to show the query to explain what we are doing. The top query gets all of the data from the new Excel file, the bottom query gets the total of the TotalReport_Tax field summed by the “GroupBy” field. We want the grand total of all the lines summed up. We do Min() and Max() to get columns, but those are never mapped for the “Offset” line. We need to do this so the columns match up on both queries.
select GroupBy, Employee, LineNumber, Vendor, VendorID, ExpenseType, ClaimDate, ApprovalDate, ReportName, Company, Department, EmployeeCode, Totalreport_Tax, ‘Line’ as Type
from [Sheet1$]
union
select GroupBy, min(Employee), max(LineNumber), min(Vendor), min(VendorID) , min(ExpenseType), min(ClaimDate) , min(ApprovalDate) , min(ReportName), min(Company) , min(Department), min(EmployeeCode), sum(Totalreport_Tax) * – 1, min(‘Offset’)
from [Sheet1$]
group by GroupBy
If we do Preview, you can see we have the “Offset” line with the sum of -5243.51
Now that we have all of the source data including a line number and a separate line for the total, you can now start mapping your fields to the NAV/BC Purchase Invoice process. You would be able to group by the fields needed and the line number to ensure all lines get added to the purchase invoice. You would be able to check if _TYPE = “Offset” to set the fields correctly on the line mapping.
Once you test the second map and get the transactions to successfully create in NAV/BC, you would go back to the first map and add a task on Map Success to run the second map automatically.
Set the first map up to run on a schedule, and both maps will run getting the expenses into Dynamics NAV/Business Central for you.