Section 11
Lookup Import Information Table

This table permits you do define the values related to lookups. A lookup permits you to validate customer account numbers, addresses and names so that you are sure you are entering valid information into your transactions.

During entry of the transaction you can typically enter the name, address or account number and search for all matching entries. Some lookups are tailored to specific sub-systems, such as a Parking Ticket system, Permit System, etc., and the available lookup fields can be different.

When the display of matching entries is presented, you select the line that you want and the program will transfer the appropriate values back to your entry screen.

In order for this to function, you must export a text file containing the needed fields from your host system[s]. This file would typically contain the account number, name, address, and one or two balance amount (e.g., total due, past due). To import the file to the RASWIN program properly, you set the fields in the lookup parameters table to define the structure of the import file.

These are used by the main RASWIN receipt processing module, and by the import module.

Note: Starting with RASWIN version 5958, the IMPORT LOOKUPS function is fully integrated into the main RASWIN.EXE program. It is reached via the file

Using the same data values from the same table in the RASWINSQL database ensures that the same column names and settings are used, leading to fewer import/lookup problems.

In some cases, your import file is not simple enough for the logic of the IMPORT LOOKUPS module to process. When this happens, QSI will typically create a custom import program that will be run independently of the main RASWIN program. In either case, the basic setup of the lookup parameters table is the same.

Each field in the table is listed, along with a short description, below. This information is moderately complex, so please be sure to read and understand it fully if you plan to set up your own lookups. In this table, some fields server double duty. For example, the first column is the key value for each record. The different types of lookups (e.g., UB for utility billing, AR for Accounts Receiveable, etc) have multiple keys, each of which serves a specific purpose such as identifying the column names in the database, the captions that appear over the column data, etc., and the characteristics of your input file (defining the fields in each record, and how many characters they contain).

The setup of these values is critical to getting your lookups working smoothly and correctly.

The LOOKUP_TYPE column contains the NAMES of the various settings. These are description of what the setting does .. for example UB-REMOVE-NAME-COMMAS will remove commas from the name field read from the import file. The segments of these values are delimited with DASHES, not UNDERSCORES. The import module will attempt to adjust these values to contain dashes if UNDERSCORES are found in the setting names.

1. LOOKUP-TYPE
   
   This is the name of the lookup. The actual lookup table is located within the RASWIN database for fastest access. Each of lookup tables consists of 6 fields. The specific fields in each vary slightly based on the type of lookup and the table names and their associated fields. For example, you may have a lookup for UB, which would contain an account#, name, address,etc., and another lookup for A/R, which would contain an INVOICE#, name, due date, and another for animal licenses which would contain OWNER NAME, TAG#, Animal Name, etc.
   
   In order to link a Tran_Code to a specific lookup table, all you need to do is the put the appropriate LOOKUP_TYPE value in the trancodes table record field called ACCOUNT-VERIFY-TABLE. Multiple trancodes can share the same lookup table.
   
   NOTE: This is very important. The key values in this table consist of a lookup code (such as AR, AR2, AR3, UB, TX, etc.) which is derived from the LOOKUP_TYPE value. All the characters following the letters LOOKUP_ are used to determine this code.
   
   So, for example, the value LOOKUP_UB would be a UB lookup. This sub-code ( UB in this example) is used to assemble a key value which is used for the remaining settings. For example, there is a setting that permits you to remove periods in a customer name. The key value used to determine the setting for this value in the UB subsystem is UB-REMOVE-NAME-PERIODS.
   
   The setting used to determine if this is done for AR would be AR-REMOVE-NAME-PERIODS.
   
   In the examples that follow, we are using UB as the example lookup code, but remember than any of the valid codes can be used. When the program is loading the UB records from the export file it uses the UB type records when loading the AR records, it uses the AR type records, as there will likely be different formats for each of these export files since they come from different sub-systems.
   
   
2. UB-IMPORT-FILE-NAME
   
   This is the name of the text file exported FROM your host system. It must be in a location where the program can access it. For example, the export file name might be something like
   
   Q:\Quadrant\UTILITY.DAT.
   
   The exact setting will depend on your local network drive assignments. It can also be referenced with a UNC type file name that refers to a server name and path rather than a drive letter. This is preferable in most cases, as drive letters have a way of changing from time to time. For example, the path might be:
   
   \\FINANCE_SERVER\CASH\UTILITY.DAT.
   
   The key value goes in the LOOKUP_TYPE column, and the actual file name goes in the column labeled COL1
3. UB-IMPORT-FILE-NAME-TEMP
   
   This is the name of the local copy of the text file exported from your host system. When the import process is run, it copies the file listed above
   
   ( UB-IMPORT-FILE-NAME) to the local hard drive before processing it. This has been found to result in a faster import since the amount of network traffic is less. We suggest that the name be the same as the host export file name, except you would specify that it is located on the local drive. For example, if the export name, as in the above example was
   
   \\FINANCE_SERVER\CASH\UTILITY.DAT,
   
   RASWIN will copy that file to a local working directory and then import the records from the local file.
   
   The key value goes in the LOOKUP_TYPE column, and the file name goes in the column
   
   labeled COL1
4. 
   
   UB-REMOVE-NAME-COMMAS UB-REMOVE-NAME-PERIODS
   
   UB-REMOVE-NAME-DASHES
   
   UB-REMOVE-NAME-DOUBLE-SPACES
   
   UB-REPLACE-NAME-ASTERISKS
   
   When records are exported from your host system the name field may punctuation such as periods, commas, extra spaces, etc. in various places. For example, you might have names such as
   
   JONES, BOB
   JONES, SMITH&WILSON
   JONES BROTHERS PLUMBING
   JONES BROTHERS CARWASH
   JONES, MR. BILL
   JONES, MRS WILLIAM
   JONES BROS. FUNERAL HOME
   
   
   We have found it is usually better to remove this punctuation when importing the records as it makes the name entries more consistent for searching purposes if you know that there are no periods or commas. If set to YES, these key values will clean up your name field text. The key value goes in the lookup_type column, and the actual value ( YES or NO) goes in the column labeled COL1
   
   
5. UB-COLUMNS
   
   These settings control the names of the columns stored in the database, and the corresponding titles that appear at the top of each column on the inquiry screen. We suggest you use the values supplied by Quadrant for the column names. The column titles can be changed as needed to more closely match the actual type of data. For example, the majority of the lookup tables have the first column set to ACCTNUM or CUSTNUM. In the case of a UB lookup this is pretty close to what the data value contains. In the case of a PK(parking citation) lookup, the data may contain a citation number or license plate number.
   
   However, the computer doesn't know the difference between a text value containing an account number and a citation number (to the computer it's just a string of characters). Therefore, to avoid potential confusion we have provided the CAPTION field for each of the 6 possible columns. This is where you should adjust the words so that they more closely match your needs. Quadrant will initially set up reasonable values for the descriptions based on the type of lookup, but you should feel free to change the captions as needed.
   
   The key value goes in the LOOKUP_TYPE column, and the actual column names and captions go in the columns labeled COL1 through COL6
6. UB-ST & UB-SZ
   
   These two settings define the positions and sizes of the individual fields within the output record created by your host system. The ST values (up to 6 of them) define the starting position for each of the possible fields, and the SZ values define the SIZE of the fields. For example, if your output records look like this:
   
   
   
   you would need to set the value for COL1 UB-ST to 1, and the
   
   UB-SZ value to 7. Likewise the other field starting and size values would need to be entered in order for the program to properly break the record into the individual fields when it is imported into the database. Quadrant will initially set up the correct value for these items, but you might need to adjust them at some point in the future if the output record from your host system should change. The key value goes in the LOOKUP_TYPE
   
   column, and the actual column start and size values go in the columns labeled COL1 through COL6, for each of the six possible fields.
7. UB-IMPORT-ZERO-DOLLAR-ITEMS
   
   If set to YES this causes the import program to import accounts with balances that are zero.
   
   Normally, this is the way you will want to have your account data imported, as in most cases, such as UB bill processing, it is possible that a customer might make a payment on an account that has a zero balance.
   
   In other situations, such as AR processing you might want to suppress zero balance accounts if your export process does not do so automatically.
   
   
8. UB-CLEAN-ADDRESS-FIELD
   
   If set to YES the program will make adjustments to the address field as data is imported from your export file into the lookup database. The program will remove some characters automatically. These include underscores, ampersands, double slashes, and quote marks. If the CLEAN-ADDRESS-FIELD setting is set to YES it will also remove additional characters such as periods, commas, and dashes. Most users opt for this setting as it makes your data more consistent in terms of entering your values for searching. For example, if the host system allows the user to enter some punctuation, such as periods, (e.g., 123 S. 4th. AVE.) the user would have to enter the periods during the search function if this was imported directly as exported. By cleaning up the data, it is much simpler for them to just enter 123 S 4th, and depending on how 'dirty' the host data it is much more consistent from entry to entry.
   
   
9. UB-REMOVE-ACCT-SPACES
   
   If set to YES the program will remove any spaces in the imported account number field.
10. UB-REMOVE-ACCT-DASHES
    
    If set to YES the program will remove any dashes from the imported account number field.
11. UB-DECIMAL-HANDLING
    
    RASWIN will automatically remove any $ or commas from the value because it is not possible to convert a value such as $123,456.78 as currency value directly, since it contains non-numeric characters. By removing the $ and commas it would be read correctly as 123456.78. If the value contains a dash, either before or after the dollar amount, RASWIN will treat it as a negative amount.
    
    If set to IMPLIED the program will assume that the decimal point in the amount fields is not present and will divide the amount read from the import file by 100 to determine the correct amount. In other words, if the amount is read as 12345. It will treat this amount as 123.45.
    
    If set to PRESENT the program will assume that the decimal point in the amount fields is present and will note divide the amount read from the import file by 100 to determine the correct amount. In other words, if the amount is read as 12345 it will treat this amount as 123.45
    
    If the import field DOES contain a decimal point, this the field will be treated as if the setting is PRESENT even if it set set explicitly to implied. So values such as 1234. and 1234.1 would be read as 1234.00 and 1234.10, respectively.
12. UB-PAYER-NAME-MESSAGE-CODE-CHARACTER
    
    This setting tells the program to apply one or more message codes to the imported record in the MSG field. The PAYER-NAME-MESSAGE-CODE-CHARACTER is a delimiter character. So if the setting is, say a tilde character, the import routine will look for a ~ character in the payer name field. Any characters that follow this character will be treated as message code characters which will then be displayed to the user in the MSG field on the lookup screen. These might alert them to a 'CASH ONLY' or similar situation with a particular customer account.
    
    The values of the message codes are stored in the MISCPARMS table using this format:
    
    When the lookup is performed, the message code will appear, and when the account is selected, a popup message will appear displaying a text value explaining the message code, as shown below:
    
    If more than one message code is linked to the account, all will be shown.
    
13. UB-SCREEN-TITLE
    
    This setting will be displayed on the import screen when doing a particular data set LOOKUP_TYPE import. An example of a reasonable value for this setting might be Utility Billing Lookup.