We manage a number of escrow payments. Typically, clients pay money to us and we then pay that money on to other people. We currently manage this on Excel but we are getting more and more of them so we need to change to an on-line database that is accessible on computers and smartphones with admin access and client access each being able to edit and view different things.
We also need to be able to auto-install replicas of an empty database for new projects.
We have a server and domains we can use.
We need some functionality, queries and reports that I will describe below and a main-menu. We would also like a 'portal page' on the internet that we can select what escrow system we want to work on and then login.
The main menu would have:
> Add escrow (add a new escrow record)
> Edit escrow (search and edit an escrow)
> Clear escrow (do the clearing function below)
> Reports & Queries
> User control
I worked out I think we will need 3 tables to begin with.
The default table will be:
> Name of escrow
> Fee percentage (percentage field)
The payments table will be variable and we will need to be able to add/edit/delete records
The primary data table:
> Record number (auto-number)
> Date (dd/mm/yyy)
> Amount (£0.00)
> AML (tick box)
> Complete (tick box)
> Fee (£0.00) - which is the amount x our fee from the default
> Payments (we need to be able to add the name from the payments table and add an amount or percentage. We will also need to able to more than one payment.)
> Total payments (total of the payments and our fee)
> Balance due (amount - total payments. If it is not zero, it should flag up)
> Approved (tick box and we need to put in a password)
> Approved by (the user who's password was used)
> Approved date (dd/mm/yyyy)
> Cleared (tick box - comes from the clearing functionality described below)
> Cleared date (dd/mm/yyy - comes from the clearing functionality described below)
> Notes/comments (multi-line and the ability to add documents as attachments)
Once it is approved, it cannot be edited unless the password is put in again but once it is cleared it cannot be edited at all.
Once this data is in, we need to be able to clear the uncleared escrows. I thought we could click on the main menu and there would be a list of the uncleared escrows. We could tick the "cleared" tick box (see on main data table) on one or a multiple of them, click a 'clear escrow' button, enter a date and password and it would clear those selected escrows.
Once we clear, it would generate two reports we can print and it would store the reports to recall later on if we need to.
1. Escrow report for each cleared record - it would print the escrow number, name, amount, fee, payments, total payments and the date it was cleared.
2. Summary report - it would print a list of the records cleared, the date it was cleared and the individual record amounts, a total of the amounts and a count of the records.
We will need to be able to recall these records by: name, escrow number and by a date or date range. We need to select whether we want the summary or the record report as well. Then once we put in the criteria, a list of reports comes up, we select the ones we want and it PDF generates on screen that we can save or print.
On reporting, we should also be able to see a list of uncleared escrows on screen in PDF we can print or save.
Finally, user access:
> Admin users can see everything and do everything (except approve)
> We need an approval password that can be edited. Without it, nothing can approved or cleared
> Clients can only search for and see (not edit/delete) the reports
Functionality is important but it needs to look good, the reports must be set out nicely with our company name on the top, the date generated, etc. We are looking to do this really nicely.
I think I have covered everything I thought of. If there's anything I have missed, please just ask.