Lease Management and Billing customized for Business Central
•Overview
Our client owns and manages 13 office buildings among other real estate and hotels.
We helped them manage their financials since 2008 with Microsoft Dynamics GP.
This past year we moved their financials to Microsoft Dynamics 365 Business Central –Cloud
As of January 1, 2023, the 13 office buildings are now live in Business Central. All sales, purchasing and accounting are done only in Business Central
Before PDG began the Business Central implementation, I went onsite and spoke to the owner and each of the office staff to see what their requirements were for his new system.
I found out that the staff weren’t using the lease management system that we had developed for them in 2015 (Visual Studio and SQL Integrated to GP). Beautiful system which the owner had requested and paid for; but the staff weren’t using it, not for the last 5 years at least.
Instead, they managed all tenant leases with Excel.
The owner would create an Excel spreadsheet for each building with the tenant’s name in the first column and all the particulars in the 2nd through last column.
The Room or Floor; the Square Footage; the various services: Base Rent, Electric, Construction, Parking, Real Estate Taxes, and Other Operating Costs.
Then columns for Total Monthly Income; Annual Income; Rent/SF; Lease Start and End; Rent Increases and % use of the entire building.
This Excel file was called the “Rent Roll”. There was one for each of the 13 buildings.
The foundation of our custom App in Businss Central would be to create a Table and List Page which would mirror the client’s own “Rent Roll” Excel file.
Rent Roll
The client’s “Rent Roll” Excel with which they manage leases and billing
Rent Roll
Our duplication of the client’s Excel Rent Roll file as an editable Table and List Page within Business Central
Custom Development – based on the client’s Rent Roll
We customized Business Central so that it mirrored the Excel Spreadsheet they had been using for 20 years.
Fortunately, Business Central has a built-in development language, where custom integrations look like they are part of the base product. This language is called Application Language or AL. It was the hidden power behind NAVISION for 30 years. Microsoft wrote Business Central entirely with AL (Application Language).
Business Central is NAVISION in the Microsoft Cloud. All the functions that were behind NAV are now in Business Central but expanded and improved.
Since Business Central is fully integrated with Office 365 all the advanced features of 365 are available, such as In-Place Editing: where changes are saved without having to hit a “Save” button; Collaboration: multiple users can collaborate on the same screen simultaneously from anywhere in the world; and Duplicate Windows can be spun up instantly to view different part of Business Central all at once.
C-AL development language in NAV is AL (Application Language) in Business Central. The Development Platform is Visual Studio Code, which is a high-performance Text Editor with AI capabilities. For instance, type a few letters of a function and all the function parameters pop up.
While Business Central - Cloud relies on SQL Databases, developers no longer have direct access to the SQL Tables, but need to use the data functions within AL (Application Language) to access the data. NAV and now Business Central have very fast indexing functions to handle large amounts of data quickly.
The Customer Card in Business Central was customized to hold all of the data objects in the Rent Roll Excel file as well as the usual customer setup data.
Custom Tables and List Pages were created to manage all the Rooms, Suites, and Floors in each building.
Customer Card
Customer Card in Business Central duplicates the relevant lease management and billing data that our client uses in their Excel Rent Roll. This is not your standard Business Central Customer Card. This Customer Card has been customized to display all the Excel fields that our customer has been using for 20+ years.
Rent Roll Suite List
Rent Roll Suite List lists all the rentable spaces in the building, with the square footage for each.
The Sort Index column can sort the Suites (spaces) from the lowest level to the roof. That is how the original Rent Roll Excel file is sorted – from the bottom floor in the building upwards.
Our customization was designed to mimic and automate the Rent Roll Excel file.
Recurring Monthly Billing - with one click
Recurring Monthly Billing starts with the Rent Amounts fields on the Customer Card.
The next slide will show the Recurring Prices for the customer.
Recurring Monthly Billing
The Prices & Discounts Menu holds the specific billing for the services our client provides.
Each of these charges is matched to the charges on the Customer Card.
Each charge also represents a Service Item in the Inventory Table. That is how charges get applied to the General Ledger, based upon the Item.
Recurring Rental/Sales Invoices
A custom App was written using the AL Language embedded in Business Central. This will create all the monthly rent invoices with one click.
Actually, all the custom fields on the customer card and the list pages and underlying data tables for the Rent Roll Suite List required software development using the built-in AL (Application Language) as well.
Recurring Sales Invoices
By customizing Business Central with Application Language - Monthly Rental/Sales Invoices can be created for all tenants in any one of the 13 office buildings with just one click.
Sales Invoice
Each Sales Invoice will pull the exact information from the Customer/Tenant Card
The Inventory/Services and Sales Pricing match the Customer Card and the Customer Price List.
Posted Sales Invoice
The Posted Invoice ready to be emailed to the customer.
Customer Statement
The Business Central out-of-the-box statement, which is just a list of invoices and payments in date order, doesn't indicate which payment applies to which invoice. We were asked to customize the Business Central statement so that the applied payments would show directly underneath the invoice, as it was done in Great Plains.
Customized Business Central Statement
The out-of-the-box Business Central Statement doesn’t show the payments being applied to the respective invoices
The Customized Business Central Statement does show the payments applied to each invoice.
This is important when the end customer challenges the Accounts Receivable Department about the individual bills.
Reporting like this requires a knowledge of the underlying data tables and how to apply them correctly. It was done with Application Language, an AL Code Unit and a Word Layout.
Vendor G/L Distribution Accounts
In Dynamics GP the Vendor Maintenance Window has an option to set the Default G/L Distribution Account for each vendor.
In Business Central no such option exists. Yes, there is a kludgy work-around, but nothing as simple as in GP.
So, we decided to customize Business Central to add that functionality.
We added 2 new fields to the Vendor Card in Business Central. There is a dropdown for the Vendor Balancing Account with the ability to select any account from the G/L. Below that is a Read/Only field which displays the G/L Account Description.
Vendor G/L Distribution Accounts
Accounts Payable Transactions in Business Central are quickly done with a Vendor General Journal. You don’t need a Purchase Invoice Document to record the plumber’s bill or an insurance bill. Thus, a General Journal set to the Vendor Account enables quick entry of single line expenses.
The Vendor Journal entries will automatically insert the correct default G/L account for the Vendor’s invoice if the Batch Setup Window has that option checked.
Vendor Default G/L Distribution Accounts have been set on the Vendor Card in Business Central
This is the Vendor Journal which automatically defaults the G/L Account that was set on the Vendor Card. When this journal is posted the G/L will be updated for these Credits to the Vendor Accounts Payable with offsetting Debits to these Default G/L Expense Accounts listed below.
Custom MICR checks for Business Central
This client has 13 companies (office buildings) which need checks. They order blank check stock.
Our custom check application will recognize which company, which bank, which bank account, routing, and Transit/ABA #.
Using the MICR font is not as easy as Microsoft says.
Summary
As of January 1, 2023, our client was live in Business Central (moved from GP).
The built-in AL Language (Application Language) gave us the ability to transform Business Central into a modern version of the client's legacy Excel Lease Management system.
Automation of their existing manual Lease Management system
Customized pricing for all tenant services
One-Click recurring billing for all tenants in each building.
Customized monthly billing invoice.
Customized Statement, which show the application of which payment to which invoice
Easy entry of Payable Transactions by defaulting Vendor G/L Accounts
Customized MICR Checks on blank check stock
All the above embedded into Business Central as though it came directly from Microsoft.
コメント