MASTER FILE QUERY
|
Function
To provide reports and labels based on data within Loan Master files and Loan Activity Report (history). Operating Instructions From the GMS-RLSS Main Menu, select Master File Query.
Options from Loan Master files include:
Click on the field to be searched to highlight it, then tap enter to bring into field. In several situations, this will result in a second combo box offering selections. Example: If the selected field is “Status”, a secondary combo box will list all status codes allowing you to click on the status you want the program to find. If the secondary combo box is used for selection, the selection in step 2 should be “equal to”. When this box is clicked, the field will automatically list the criteria selected in step 1. At this point, a screen prompt will appear asking if you wish to make further selections, which will narrow the search. Selecting “yes” will allow further options to be chosen from the combo box in step 1. Important Note: If search criteria is to include data found within Optional Fields and along with other data, such as loan amount, it is important that the data that is not connected to an Optional Field be chosen prior to selecting from the Optional Fields. 2. Search Criteria You may select one of the following options: equal to not equal to less than greater than If search criteria is a date, enter as MM/DD/YY. Note: If search criteria includes an Optional Field, please refer to “Accessing Data within Optional Fields” below. Note: If search criteria includes a date range (ex: Maturity Date 01/01/11 – 12/31/11) select the field Maturity Date, tap enter, and enter greater than 12/31/10. When prompted “more selections?” respond “yes”. Again, select Maturity Date in step 1, tap enter, and enter less than 01/01/12. This will result in the query locating any loan with a maturity date within the year 2011. 3. Fields to be included on report Any field listed in the box may be selected for the report. To select, click on the desired field. If more than one field is to be included on the report, hold down the Ctrl key while selecting additional fields. Fields selected in step 1 are not automatically included in step 3. If you wish to see the data within the field to be searched selected in step 1, select the same field in step 3. Note: If report criteria includes Optional Fields Data, please refer to “Accessing Data within Optional Fields” below. 4. Report Format Select from the following options: List the Data Labels If labels is selected, they will automatically include borrower’s name and address. No fields need be selected from step 3. 5. Sort Options Select from the following options: Loan Number Alphabetical If you wish to include addresses in your report, check that box. Should you wish to save these selections for later retrieval, check this box. You’ll then be asked to name your report selection. Special Icons Retrieve Report: Allows retrieval of a previously created and saved report. Retrieve Selections: Allows retrieval of previously saved selection criteria (see below for instructions) Accessing Data within Optional Fields Because Optional Fields and the data within those fields are held in a separate table within the database, using Master File Query and Optional Fields requires some special processing. In the combo box under step 1 (search criteria) you will find a listing for “Optional Fields”. When you select Optional Fields and tap enter, a display will show all the optional fields in your database. Select the one you wish to search for specific data. Then go to step 2 and enter the specific data within that field you wish to find. When additional Optional Field search criteria is desired, and the screen prompts “Do you wish to make more selections”, click on “Yes”, and repeat steps 1 and 2 for any additional criteria. In step 3, select both “Optional Fields” and “Optional Fields Data”. This will result in both the Optional Field Title and the data within that field (as selected in step 1) showing on the report. A good example of this process would be an optional field titled “Financial Y/N”, established as a text field. Data entry would then provide opportunity to enter a Yes or No response to assist with tracking which borrowers are responsible for submitting financial reports. A report could be pulled listing the loans that have “Yes” within the field by taking the following steps:
When the report is generated in Excel, Optional Fields will be one column, and Optional Fields Data will be listed in the next column. Using Excel features to sort the information within this column will result in a report that is easy to read. If you need the report to include a date range (example: which loans are due for UCC renewal in June, 2011) in step 1 select Optional Field “UCC Renewal”, tap enter, and then in step 2, enter “greater than” 05/31/11. When prompted “Do you wish to make more selections?” respond “yes”. Repeat step 1 and again select Optional Field “UCC Renewal”, tap enter, and in step 2, enter “less than” 07/01/11. This will result in the program locating loans with UCC Renewal dates from 06/01/11 to 06/30/11. In step 3, be sure to select both “Optional Fields” and “Optional Fields Data”. Additional data may be included by selecting more than those two fields in step 3. In situations where a range of dates is desired Quick Date Listings found on the Features Menu is a good alternative. The data included on the Quick Date Listings report is standardized and cannot be altered. It contains loan number, borrower name, and contact information along with your selected date field. When a report is desired that reflects data within a multitude of Optional Fields, but it is not necessary to limit the search to specific criteria within those fields, select the fields to be displayed in step #1. To select more than one question, hold down the CTRL key and click on the desired fields. Tap Enter, and when the cursor moves to step #2, click on the “equal to” indicator, and tap Enter again. When the screen prompts for additional criteria, select “no”. Proceed to step #3, and select Optional Fields and Optional Fields data. The resulting report will include all the data within the selected Optional Fields. Important Note: If search criteria is to include data found within Optional Fields along with other data, such as loan amount, it is important that the data that is not connected to an Optional Field be chosen prior to selecting from the Optional Fields. Saving/Retrieving Reports Once you have selected all criteria and report options, click on Print. This will launch Microsoft Excel, displaying a report that includes the data selected. If you wish to then save the report, click on “file” and “save” and direct the file to the folder of your choice. The report can be retrieved at a later date by clicking on the Retrieve Report icon and selecting your file or by launching Excel and opening the file, as with any other Excel file. Saving, Retrieving and Editing Selection Criteria Report selection criteria may be saved within Master File Query for later retrieval. This may be especially useful when reports are created reflecting a multitude of data fields that are needed on an ongoing basis. Saving a created report in Excel will save the data that is displayed within the report at that time. Retrieving the criteria and creating the report again within the RLSS program will result in the report reflecting updated information. Master File Query reports reflect data found within the Loan Master files and Loan Activity as they are at the time the report is created. When selection criteria is saved and used at a later date, the data reflected on the report will be updated data, including any changes to Loan Master files and any new activity recorded through Loan Activity. Once steps 1 through 5 are completed, the criteria entered may be saved for future use by putting a checkmark in the “Save report selections” box. Once the “Save report selections” is checked, clicking on Print results in a screen prompt to name the report selections. Each saved criteria must have a unique name. If you enter a name that has already been used, you will receive a message that the file already exists and have an option to overwrite or save using a different name. Overwriting will replace the previously saved criteria with the newer criteria. Saving criteria will retain the items selected in steps 1, 2, and 3. Step 4 (Report Format) and step 5 (Sort Options) are not saved. When retrieving saved criteria, these steps will default to “List the Data” and “Alpha”, but may be edited if desired. If selection criteria has been previously saved, clicking on “Retrieve Selections” results in a screen listing of all saved reports. When a report is to be prepared using criteria as saved, click on the desired report name so it is highlighted, click on “ok”, then click on Print. If the saved criteria includes a date range (example Maturity Date within 2010) that range can be edited by selecting “Edit Date”: A grid will appear showing the date range as it was originally saved. Click on “Edit Date” if you wish to alter that date range. For example, a report was designed to display loans with a maturity date within the year 2010. The dates displayed may be edited to produce a report reflecting loans that mature outside the originally saved range. For example, if a report is now needed to show loans that mature in 2011, edit the “Less Than” date to 01/01/12, and the “Greater Than” to 12/31/10. Once the date range is edited, select “Save” to save the newer criteria. Clicking on Print will then produce the report utilizing the revised date ranges. Things You Should Know
|
|
Copyright © 2019 GRANTS MANAGEMENT SYSTEMS, INC.