In our recent Microsoft Dynamics Case Study, we went over how Pivott’s integration with Microsoft Dynamics simplified and automated our customer's contract management process. Microsoft Dynamics is a powerful accounting tool, but it's not commonly used in commercial real estate, and it’s unusual to issue vendor checks without invoices. To address these challenges, we developed integrations for both the “Business Central” and “SL” versions of Microsoft Dynamics. Here’s how we did it.

Integration for Microsoft Dynamics 365 Business Central

Pivott’s MS Dynamics 365 Business Central integration uses Configuration Packages to template your data for import. Once set up, these packages can be easily reused. While Configuration Packages are highly customizable, they can also be complex, so we’re here to assist with the setup process.

Configuration Package Setup

Pivott is designed to be flexible and adapt to your MS Dynamics needs. We recommend choosing your preferred import setup, and Pivott will align with it. Here’s how to do it:

1. Click 🔍 and type "Configuration Packages" (then click the link with the same name).
2. Click New.
3. Name both the Code and Package Name fields something simple, like “Pivott.”
4. In the first row, enter the Table ID for the GL account you typically use for journal entries. Since MS Dynamics can be configured in many ways, be sure to consult with your administrator.
5. Adjust the Number of Fields Available and Number of Fields Included to match the data in Pivott.
6. Click Export to Excel (see the next steps below).

These steps only need to be done once.

MS Dynamics Excel Template

The exported Excel file should be shared with Pivott for configuration. This file helps us understand what MS Dynamics expects from us. Be sure to include only the fields that Pivott can provide.

‍Important Note: The Excel file contains hidden XML components that are not visible. Therefore, you cannot simply copy and paste the fields into a different spreadsheet.

Importing Pivott’s Data in MS Dynamics

Based on the Excel template you provided, Pivott will export data in the same format. Here’s how to import it into the Configuration Package you created:

1. Click 🔍 and type “Configuration Packages” (then click the link).
2. Select "Pivott" (or the name you assigned earlier).
3. Click Import from Excel and upload the file from Pivott.
4. Click Apply Package (but don't stop here! Keep going!).
5. Close all the windows related to Configuration Packages.
6. Navigate to Finance > General Journal.
7. Click Pivott.
8. Preview the journal entries to confirm.
9. Click Post.

Repeat this process every month or periodically when you have new data to upload.

Integration for Microsoft Dynamics SL

Uploading general ledger or accounts payable entries to Dynamics SL is simpler but less user-friendly compared to the Business Central version. It also requires an administrator to activate the necessary modules.

Unlike Business Central, Dynamics SL accepts files in CSV format, not Excel. In our example, we aimed to create a single CSV file that instructs Dynamics SL to generate around 200 check payments each month. Here's an example using only two landscaping vendors.

CSV Example

Let's say you need to queue two checks for printing:

1. A check to GrassGuys for $1500:
   
   • $1100 for services at Alpha Apartments
   • $500 for services at Beta Apartments
   • A $100 credit with GrassGuys
2. A check to GreenLawn for $1300:
   
   • $900 for services at Alpha Apartments
   • $450 for services at Beta Apartments
   • A $50 rebate with GreenLawn

Before Pivott

Before using Pivott, someone had to manually calculate the amount owed to each vendor, after applying credits, every month. This process was repeated over 200 times each month and took up a significant amount of time during the first half of the month.

Implementing Pivott

Pivott used a combination of AI and human verification to import all spreadsheets, contracts, and financial records into the software. Even the vendor directory was created from a single import. The customer only needed to verify the data and attend training sessions on how to make any changes.

Maintaining Data

Things change regularly: vendors are added or removed, credits and rebates fluctuate, and properties are updated monthly. Pivott's interface supports two types of user groups: 1) View-only users who can answer their own questions, and 2) Administrators who handle daily and weekly updates. Rather than having each department manager maintain their own version of a spreadsheet, Pivott standardized the process for everyone.

After Pivott

At the end of each month, a single CSV file is exported from Pivott and uploaded to Dynamics SL, completely eliminating the need for manual labor.

CSV Format

• Exactly 1 line at the top of the CSV file that just says “Level0” (creatively named)
  
  • Add one line in the CSV file for every check denoted as “Level1” (aptly named)
    
    • One line for every sub-category on the check as “Level4” (optional - sorry that it’s not level #2)
    • VO is an AP Voucher vs GL which is a General Ledger journal entry
  • How to Integrate Pivott with Microsoft Dynamics: A Technical Tutorial
• Additional columns for vendor codes (e.g. 8111), sub accounts (e.g. 8111-120), descriptions, dates, etc. can also be configured. These columns very much depend on your implementation.  The following CSV only conveys the simplicity of the formatting.

Upload CSV to Dynamics SL

This single CSV file is uploaded to Dynamics SL, which will automatically generate checks based on the number of "Level1" entries. Please consult your Dynamics administrator for access to this upload feature, as it is not enabled by default.

integrating Pivott with Microsoft Dynamics—whether through Business Central or Dynamics SL—streamlines the vendor payment process and significantly reduces manual effort. By automating the generation of checks and leveraging flexible data formats like Configuration Packages and CSV files, Pivott ensures that your accounting processes are both efficient and scalable. With the ability to handle frequent changes in vendor information and other financial details, Pivott allows your team to focus on higher-priority tasks. The integration transforms complex workflows into seamless, automated operations, ultimately saving time and reducing the risk of errors.

Need help integrating? Book some time with our team!