One objective of the Expense Reporter application is to show how much of your application is determined exclusively by the database design and how flexible and powerful that is.
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.
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.
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.
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
SELECT * FROM ExpenseMileage ser
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
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.
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.
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.
The first step would be to download and install dbFront. dbFront Download
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.
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.
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.