We are working on a series of fully functional and free sample database applications. Our first application is an Expense Reporter. This Expense Reporting application demonstrates the advanced topics of Workflow and Auditing.
The Expense Reporter includes all of the database scripts and support files so it is easy to modify to meet your specific needs.
Features
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.
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 a direct function of the database structure and the supporting static data. The role of dbFront is simply to conditionally enable or disable specific functionality such as security, fields and buttons.
Expense Status
The ExpenseStatus table lists the available statuses and their priority. The Expense Records are added to, or removed from the Todo List based on the status priority. A status priority of zero means that the process is done (e.g. Complete or Cancelled) and should be removed from the TodoList.
Expense Workflow
The ExpenseWorkflow table lists the valid To/From status combinations and who can trigger them.
Auditing
Similar to the workflow functionality, the Auditing functionality happens almost completely in the database. dbFront sets the %username% into designated audit fields on update and insert. A database trigger then creates a JSON copy of the record and appends that to a separate audit table.
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;
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.