Design a web database (PHP/MySQL) structure for tracking foreclosures given an initial Excel spreadsheet. Allow a user to go to an individual property record to modify particular fields. Be able to generate a report of property records that were sold at auction but have not shown up on the MLS.
All columns from the example spreadsheet will be imported except "cb" and "ncb". When uploading, allow user to specify "Auction Date" and "County" which will be populated for each record imported. User will be able to select previously used Counties.
1. Data maniuplation pages
Give a user ability to update an individual property record to show a property was "REO" or "found on MLS".
- Sold type is either "REO" or not (from the "L or 3rd" column; "L" means "REO")
- Checkbox for "MLS"
- Checkbox for MLS enables a "MLS Date" date field and a "MLS #" numeric field
- Add checkbox field "Viewed" (for when property has been physically inspected)
- Add a "Notes" field to each record, max allowable character limit
- Add "Hide" checkbox field for those that we don't want showing up in reports any more
- Create an array of MLS numbers of properties found in MLS, per report, to be copied and pasted (123456,234567,456789...) from the bottom of the page
2. Report pages (hiding records designated as "Hide", sortable by each column)
Report #1a, b, & c: Show all records that have not been found on MLS, and went through the auction process but not to a 3rd party ("auctioned but no bidders") on a specific interval (less than 60, 60-89, 90-119, 120+ days)
Report #2: Show all records designated as "MLS", ordered by "MLS Date"
3. Home page
- Links to reports of properties that have had no activity within the last 60/90/120+ days (showing the # of properties that meet that criteria). No activity means "MLS" checkbox is not checked since the record was entered
- Link to reports of properties with "MLS" checkbox checked.
- Link to import data page
- Multiple counties will be tracked
- "L or 3rd" designates "REO" or "3rd Party"... when field = "3rd", that is "3rd party"
- Data manipulation pages setup and usability is a moving target. A small trial and error period should be afforded for this part