GMS RLSS Online Help
  • Home
  • Contents
  • Index
  • Search

WELCOME TO GMS RLSS ONLINE HELP

The help you need at  your fingertips.
<BACK

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.
  1. Select the fields you wish to search
Use the combo box to select fields to be searched. Options relate to data found within the Loan Master file or the Loan Activity Report.

Options from Loan Master files include:
Amortized Interest   
Area Code                  
Census                        
Closing Date
County
Daily Interest
Escrow Max 1
Escrow Max 2
Equity
First Due Date
Fund Amount
Income Level
Interest Days 360
Jobs Data
Late Fee
Loan Fund
Loan Amount
Note Date
Payment Amount
Payment w/fees
Private Fund
SIC Code
Status Date
Service Fee Amt
Total Payment
Veteran Status
Zoning Code ​
Approval Date
Borrower Type
TractCity
Contact
Collateral
Escrow 1 Amt
Escrow 2 Amt
Ethnicity
FID/SSN
Fixed Assets
Gender
Interest Rate
Interest Days 365
Loan Officer
Late Fee Days
Loan Type
Maturity Date
Optional Fields
Payment Frequency
Payment Dates
Public Fund
Status
State
Total Package
Total Years
​Zip Code
Options from Loan Activity Report include:
Activity Date
Escrow 1 Paid
Interest Paid
New Balance
Other Fees Paid
Principal Paid
Service Fee Paid ​

Activity Total
Escrow 2 Paid
Late Fees Paid
NSF Fee Paid
Paid-thru Date
Repayment



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:
  • Select “Optional Fields” from combo box in step 1.
  • Select “Financial Y/N” from the accompanying combo box and tap enter.
  • Select “Equal to” in step 2, and enter the response you are looking for (Yes or Y, depending on your method of data entry).
  • When prompted for further selections, respond “No”.
  • Select both “Optional Fields” and “Optional Fields Data” from combo box in step 3.

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
  • Always be sure Excel is not currently open on your computer before selecting Print in Master File Query.
  • When Master File Query is used, several tables are opened within the database. These tables may be quite large. It is important to maintain the integrity of your database by running the database utility Compact and Repair after utilizing Master File Query repeatedly.
  • If the chosen report format is “List the Data”, the program opens Microsoft Excel and produces the report within Excel spreadsheet format. Knowledge of Excel is necessary to properly format columns and produce totals or sub-totals.
  • Because reports are prepared using Microsoft Excel, if you modify the format (extending column widths, adding sub-totals, etc), be sure to save the report by selecting “file” and “save” from the Excel task bar before exiting Excel.
  • 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.
  • “Clear Selections” is a very important option. Once selection criteria has begun in step 1, the only way to remove those selections or start over is to select Clear Selections. Master Query can access several different tables within the GMS-RLSS database, and preliminary criteria selection directs the programming to the appropriate table. If criteria changes, the program often needs to close a table and open another. Using Clear Selections will help avoid producing reports containing data you no longer want included on the report.
  • Sometimes it is not important to know which loans meet the criteria, but only the number of those loans. Each report produced with format “list the data” will include a record count at the end of the report.
  • If you are trying to produce a report that includes all loans, simply skip steps 1 and 2, and continue to step 3, selecting the criteria to be included on the report. Refer to section “Accessing Data within Optional Fields” if the report is to include information found within Optional Fields. 
Picture
Copyright © 2019 GRANTS MANAGEMENT SYSTEMS, INC.
  • Home
  • Contents
  • Index
  • Search