Expense Reporter

Expense Reporter is a free and open source dbFront application that allows your employees to submit expense reports which would pass through management and finance for approval and processing.

Todo List: Lists open Expense Reports for the current user: Employee, Manager, Approver, or Payor.
Expense Form: Specify the Department, Employee, Authorization Number, Purpose and more...
Expense Details: Including Expense Account, Type, Amount and Tax information.
Expense Mileage: Automatically calculates claimed amount based on distance and approved mileage rate.
Receipts: Upload and view PDF, JPG, GIF and PNG format receipts.
Workflow: Manages the movement of the Expense report from Employee to final payment.
Auditing: Logs all changes to the core Expense Report tables.

Demo

A demo version of the Expense Reporter is available on our Demo Site.

Design Notes

Workflow

One of the most interesting aspects of the Expense Reporter application is the workflow as an employee's expense report moves through the approval process. The identity of the current user and the record status enable or disable specific Expense Report functionality. The workflow is managed at the database level but expressed in dbFront.

ExpenseStatus

The ExpenseStatus table lists the available statuses and their priority. A status with a priority greater than zero causes the expense record to be added to the TodoList. A priority of zero means that the process is done (e.g. Complete or Cancelled), and the expense report is removed from the TodoList.

ExpenseWorkflow

The ExpenseWorkflow table lists the valid To/From status combinations and which user can trigger them. NOTE: The workflow table lists two extra columns (Caption and ButtonAction) that will be used in future releases.

Audit

dbFront's contribution to the audit process is to set the %username% for the audit columns and to allow Admin users to view the audit data.

At the database layer, the Audit implementation requires the inclusion of the following SQL in the Insert and Update database triggers.

INSERT INTO ExpenseReporter.ExpenseAudit
       ([Tablename],[Action],[ExpenseReport_Id],[AuditUserId],[Json])
SELECT
       'ExpenseMileage',@Action,n.ExpenseReport_Id,n.AuditUserId,
       REPLACE((
          SELECT * FROM ExpenseMileage ser
WHERE ser.ExpenseMileage_Id=n.ExpenseMileage_Id
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
),',"',','+CHAR(13)+CHAR(10)+' "')
FROM inserted n;

This will insert a JSON formatted copy of all record fields into the audit table. JSON was chosen because of its compact representation.

Once the user has submitted their Expense Report, then dbFront will disable the user's ability to delete the current record to ensure that the audit history can't be lost or orphaned.

Todo List

The Todo List is created using a single-field table that holds the primary key for all active expense reports and a database view to assemble the necessary Expense Report details.

A trigger adds or removes Expense Report keys to the Todo List table based on their status.
A view assembles the columns and workflow-based permissions for the record.
Row security hides or shows the records based on the current user and their roles.
Priority is determined by the record age (since submission) and status.
A combination of dbFront layout and CSS is used to format the Todo List and hide the form.
Actions buttons are used to open the current to-do item or create a new record.

Installation

The installation package contains three items necessary for the installation:

apps folder: This folder contains support files for the database application,
ExpenseReporterDatabaseScript.sql: The SQL Server database script used to install all of the support structures and business logic,
ExpenseReporterConfig.jsonData: The dbFront configuration file containing: Layout, Security and Visibility expressions, etc..

There are three steps to getting the Expense Reporter installed.

dbFront Installation

The first step would be to download and install dbFront. dbFront Download

Database

The next step is to allocate a database on a Microsoft SQL Server and run the included database script named ExpenseReporterDatabaseScript.sql. The script will generate the necessary database structures and application components for the Expense Reporter application.

The script will also create the user appExpenseReporter. This username should be used by dbFront when it connects to the database.

Apply Configuration

Once dbFront has connected to the ExpenseReporter database via the appExpenseReporter user, then you should be able to immediately start browsing the database structure.

To complete the setup, you will need to:

Copy the contents of the apps folder into the "C:\Inet\dbFront" folder. The final structure should be "C:\Inet\dbFront\apps\ExpenseReporter\" and contain the files "logo.png" and "style.css".
Copy the file ExpenseReporterConfig.jsonData into the folder "C:\Data\dbFront\Export\"
From within dbFront, open the Advanced View and import the new configuration file into the FreeExpenseReporter application.

That should complete the setup.

Upgrading

The entire Expense Reporter Application package is considered customizable application code. It is fully expected that individual installs will be customized to meet the client's specific needs. Only the dbFront application itself is directly upgradeable.

We will continue to improve the Expense Reporter Application, but any improvements would need to be manually integrated into your custom install. To facilitate this, you should place your Expense Reporter Application code in version control to allow for easy management as you make your own customizations and for when you want to apply upgrades.

License

The Expense Reporter application files are licensed under the MIT license.

Expense Reporter structures, relationships, static data and business logic (database script),
Documentation including workflow diagrams,
dbFront configuration and CSS styles.

The Logo was found/created using the NameCheap Logo Maker. The author is: Khuzema

The Expense Reporter Application uses dbFront to provide the application UI. dbFront is licensed separately.

Release History

Please note the minimum required dbFront version and either download the application version appropriate for your current dbFront release or upgrade your dbFront install before installing the Expense Reporter package.

Release Date	Release Version	Min dbFront Version	Details
2022-09-26	1.02	1.2.2.5463	Added Schema to Verify procedure. Fixed action type calculation in triggers. More sample data. Sample validation in Verify procedure. Security Updates. Updated Employee lookup so that it would only return valid options.	Download
2022-09-19	1.01	1.2.2.5463	Trim Audit Fixed old references to [dbo]	Download
2022-09-09	1.00	1.2.2.5463	Initial Release