REA 9 Database Clean-up Speaker Notes REA 9 Database Clean-up By Tim Creagh [email protected] | http://www.gorea.com REA Webinar The following are the speaker notes from a webinar given by Tim Creagh, Author of REA (Real Estate Assistant). The topic of this session was Cleaning up your database – standardizing entries, locking down fields, checking for duplicates, exporting data to excel for clean-up, using global replace to fix existing entries and cleaning up import database before it contaminates your production data... Overview Garbage in, Garbage Out has become more than a famous saying. It effects most of now as we add more information to our systems. This class focuses on methods to help you eliminate bad data and clean up your existing data. We will focus on 6 levels to help: • • • • • • Standardizing your data entry Locking down fields using popup tables Merging duplicate records Cleaning up field entries using global replace Cleaning up import data before you import it Exporting data to Excel and importing it back into REA 9 Standardizing your data entry Create rules within you company to standardize the way data is entered. For example come up with address standards and ask everyone to follow those. You’ll find examples in the REA Manual and REA Help file under address standards. Keep it simple and create rules for any field that you use for searches. REA Inc. www.goREA.com Page 1 Locking down fields using popup tables Steps: 1. Right click on the field you want to lock. 2. Click Properties on the fly-out menu. 3. Check the box labeled “Must use popup table. a. If you want to allow multiple choices click “Comma-separated values” too. 4. Click OK. 5. In the field click the button with “…” and fill in the default answers. Merging duplicate records Steps: 1. Open the table you want to duplicate check. 2. Set any query you want to limit the check. 3. Click the Lookup menu and select Check for duplicates. 4. Select the field(s) you want to use for the check. a. It’s best to start with a lot of fields and then remove fields in successive checks. 5. Highlight the fields you have selected and click Fuzzy. 6. Click OK. 7. Pull down the Fields combo box and set the fields you want to see. 8. Click the plus sign to see the duplicates. 9. Drag the record you don’t want and drop it on the record you do want. Cleaning up field entries using global replace Steps: 1. Query your database for the records you want to replace. 2. Click the Lookup menu. 3. Choose Global Replace. 4. Set the field you want to replace 5. Set the value. 6. Click OK REA Inc. www.goREA.com Page 2 Cleaning up import data before you import it. Steps: Check your data in Excel before you import it into REA. Never assume the data you are importing has standards. For the most part data you get from other sources has not been standardized or if it has the data won’t follow your standards. 1. Use Find and Replace in Excel to replace values in the data with values you use. 2. Use the Sort capability in Excel to view the data in combinations that make it simple to standardize the information. 3. Remove columns that contain bad data. Exporting data to Excel and importing it back into REA 9 Steps: 1. Use Global Replace to create a unique id in your data. a. Open the table you want to fix b. Click the Lookup menu and select Global Replace. c. Field to replace is “Import record”. d. Click Expression and click the button with “…”. e. Click Functions. f. Click the plus sign in fron of Miscellaneous. g. Double-Click “NewID()” h. Click OK 2. List the fields you want to export to Excel. a. Press F5 to list your data. b. Click the Fields combo and select the fields you want. c. MAKE SURE YOU INCLUDE “Import record” as one of the fields. d. Import record will contain a bizarre set of characters that guarantees a unique identifier for each record. Example 555889b-b444-3dod-a556d-47ceaafil3e39 e. Click the Excel button f. Fix your data DO NOT CHANGE the import record field. 3. Import your cleaned up data back into REA. a. MAKE A BACKUP from the file menu first. REA Inc. www.goREA.com Page 3 b. Create your import map from the file menu. c. Set the key to Import Record and match that field to your database. d. Set the import to updates existing records “Always”. Please join us for other Webinars which you can sign up for on our web site at http://www.goREA.com or call us at (858) 729-0154 ext 2 for questions and more information. Sincerely, Tim Creagh | President Real Estate Assistant 7863 Girard Ave Suite 303 | San Diego, CA 92037 W 858.729.0154 F 858.777.5737 [email protected] | http://www.gorea.com REA Inc. www.goREA.com Page 4