Skip to content
+1-888-319-3663

EONE BLOG

Tech Tuesday: Bringing in expenses from an outside system to NAV/D365 Business Central


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.

 

 

Leave a Comment





RECENT POSTS


Happy Thanksgiving from eOne! - US Office Closed November 25 & 26
Popdock: What is your Locale?
Q4 2021 Partner All Hands Call - Recording Now Available
An Announcement regarding SmartList Builder and SmartView from eOne Solutions
Join us for the Q4 2021 Partner All Hands Call!

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
2017 SmartConnect Integration Bootcamps
New Releases of Extender and SmartList Builder

CATEGORIES

TAGS

Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships partners Popdock release Reporting SalesForce SalesForce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.