How can I restore a Unit record that I accidentally deleted?

Answer: Users occasionally delete records only to realize that is not the desired results. Caution should be used in deleting records and administrators should limit user’s ability to delete records. The best precaution to avoid accidental deleting of records is to only give users Read / Write permission as opposed to Full permission. This can be done under Administration / User Accounts.

Actually, once a record is deleted, it cannot be retrieved. The record is gone. However, there are some steps you can take to accomplish the essence of restoring the record.

Example: Suppose you delete a Unit record only to realize that you should have simply made the record inactive. By deleting the record, you can no longer view the unit maintenance history; even though the work order history still exist in the database.

You can accomplish the essence of restoring the Unit record by taking the steps below. All unit linked data such as work orders, services and service history is linked to the unit via the unit RecordNumber field. By re-entering the unit and overriding the RecordNumber field, you can re-establish the data links.

Note: This involves some data handling through ExpressSQL and extreme caution should be used. The steps below should be read and followed carefully. This may require the assistance of a system administrator or someone with an understanding of database data manipulation.

  1. In the work order screen, use the search capabilities to locate a work order that related to the unit that has been deleted. Make a note of the work order number.
  2. In the Unit screen, insert a new unit record completing all unit data as was previously entered on the deleted unit.
  3. When finished inserting the data, post the unit record. ExpressMaintenance will assign the unit record the next sequential RecordNumber value. Make a note of the new RecordNumber which appears directly under the picture frame.
  4. Run ExpressSQL or any other favorite SQL query tool. If you do not have ExpressSQL installed, see the tech bulletin at the following link: How do I download, install and setup ExpressSQL?.
  5. In ExpressSQL, select your SQL server and select the ExpressMaintenance database. Click on the SQL Query Data tab.
  6. In the table listing on the left side of the Explore Data tab, locate the WoMaster table and click on it.
  7. In the upper middle section, edit the SQL statement to appear as follows where ‘9999’ is the number of the work order noted in step 1 above (see screen 1 below). “select * from WoMaster where Numbered=9999
  8. Click the Execute SQL button to view the work order record (see screen 1 below).
  9. When the query is executed, the work order record will appear in the lower middle section. Make a note of the number in the Unit column which actually corresponds to the old unit RecordNumber.
  10. In the table listing on the left side of the Explore Data tab, locate the Units table and click on it.
  11. In the upper middle section, edit the SQL statement to appear as follows where ‘9999’ is the new unit RecordNumber as noted from the Unit screen in step 3 above (see screen 2 below). “select * from Units where RecordNumber=9999
  12. Once the unit record is displayed, edit the RecordNumber field to the old unit RecordNumber as noted in step 9 above. Post the change.
  13. Exit ExpressSQL
  14. Return to ExpressMaintenance and re-query the Units records. The unit RecordNumber should now be the old RecordNumber and all other data should link properly.

Screen Shot #1


Screen Shot #2