Lookup Types,
There are three types of Lookups that can be used on a field: Values Lookup, Lookup from table, and Drill Down Lookup. Each lookup is useful for creating uniformity in the way information is entered in your system. Uniformity is the key to accurate reporting in any database system.
Lookup Properties
The Lookup Properties section allows you to select a Lookup Type from those listed and configure said Lookup.
Values Lookup – This lookup is also called a Values Lookup because it allows you to type in values to be selected from a simple drop-down on the field. This is a very basic lookup. All of the values for the lookup are entered in the properties for the field.
Lookup from Table – As its title suggests, this lookup has values which are pulled from a table in your system. The table can be one that is already in use or one which you create specifically for this field. This lookup is particularly useful for populating a single field from a list that can be added to, edited and deleted from.
Of this type, the Pop-up/Drop-down variety is the most versatile. It allows your users to use both methods to select the values for the field. It also allows for direct manipulation of the table by those who have editing permission on the form.
Lookup from table also includes an additional option called:
List – This lookup is designed to grab information from a table and use it to automatically populate specified fields in another table. For example, the work order form pulls information about the asset directly from the asset detail view. Another example is the PM Schedule which pulls information about PM Tasks directly from the Task File Listing. This type of list not only ensures conformity but also saves massive amounts of time. One click of the mouse can populate up to ten fields of information.
Values Lookup
To use a Values lookup, select this option within the Type section. In the Lookup Definition box, type the values that you want to display in the lookup (one value per line). To enter a blank line, leave one line blank and hit Enter/Return on your keyboard.
If you are editing a field with existing data, clicking the Populate list with existing field values link will pull this field's value from every record and place it into the list automatically.
When you're finished entering data into the box, click the Save button. You can now close this window and return to the Form Design screen. In data dictionary, this type of lookup is also called a Values lookup.
Note: The last entry of the list will be the default value for the field unless a Default Value is used in the General Properties.
Lookup from Table
This type of lookup associates a simple lookup table to the field. You can either choose an existing table in the system with the drop down box (shown below) or use the Create table with Lookup Wizard to create a new table for the lookup.
If selecting an existing table in your system, choose the key field of your chosen table with the Key Field drop-down list and choose the table's description field with the Description drop-down list.
If selecting the table creation wizard, the system will create a new table with two fields, Code and Description. These field will be automatically populated into the Key Field and Description field drop-down lists for you.
Finally, select the Lookup Type for the lookup. A Drop-Down is a list contained in a collapsible field, similar to the Lookup field mentioned above. A Pop-Up is a list that opens in a separate window. This window allows you to add, edit and remove values in your list. When creating a new table, you will need to select this option so you can populate the table. A Drop-Down/Pop-Up Combo gives you the option to use the drop-down list or the pop-up window. When you have completed the page, click Save. This will save the settings and you can now close the screen.
To add values to the Lookup Table that you just created, you can either add a new record or edit an existing record. From there, click on the folder icon 
to the right of the field. This will open the Look-up Values table. Click the New button to populate the table with the desired values.
List – This type of lookup pulls information from another table in the system and places it on the form. An example of a list lookup is the Asset ID field on the Work Order form. Using the folder icon, you select an asset from the list and the Asset ID, Asset Description, Building and Floor are pulled from the Asset table onto the Work Order table. List lookups can pull information from any existing table in your system.
Create a list lookup by selecting List as the type of Lookup then select the Lookup Table. Click Edit Field Map to continue.
In the Field Mappings section there are two columns. The left column represents the Get data from fields (the table the information is coming from). The right column represents the Send data to fields (the current table to be populated).
- Begin mapping your fields by selecting a field in the left drop-down list.
- Identify the field on this form using the drop-down list in the right column.
- When the drop-down lists are displaying the appropriate fields, click the + button to the right of the first field mapping. This will populate the system names of your chosen fields onto the field mapping list.
- Continue this process until all of the fields that you want to have populated are in place.
- Click Done when you have finished the process. This will return you to the Lookup Properties section.
- Click the Save button to finalize the changes to the lookup.
Now that the lookup has been created, you can click on the folder icon next to the field with the list lookup to open the list view and search for a record. When you click the Select link next to the desired record, the remaining fields will be pulled onto the form.
Drill Down Lookup - This type of lookup allows you to use an existing table of data within the system to create a group of Drill Down lookups that will fill multiple fields on the form and narrow your search for the target value with each selection.
The image above shows the relationship between the data and how it should be defined. Once you have configured your Drill Down lookup correctly you will have 3 drop down lookups that rely on one another. First you will have Building with 2 values, A or B, then based on the selection a drop down for floor will populate with only the floors available in that building, after the floor selection you will then be presented with your final drop down with only the rooms that are available in the floor of the initial building. Once the record is saved, the building, floor, and room values will be populated on the record.
For this example, we'll start on our Contact record. You can see that on the form we have three custom fields. When creating a DrillDown lookup, you will create the lookup on the final level of the lookup. In our example this would be the Room level.
Start by hovering over Options then click on Customize Form to get into the form designer screen. Then select the properties icon for the field that will be the final level of the drill down and select the Lookup Properties tab. Once you've entered this screen select the DrillDown Lookup option. A Define link will appear below. Click this link to setup your new Drill Down lookup.
If you had a lookup on this field previously you will receive an additional popup message notifying you that you will clear the existing lookup. Select OK to continue or Cancel to return to the previous screen.
When the next panel displays it will say Level 1 at the top of the form and have 3 fields available.
- Table - The table holds the values you would like to filter through. This is the Assets table. You can either chose a system table such as Assets, any other default table in the system, or a custom table that you may have populated in the system.. If you need help setting up a custom table to store your values please contact a member of the support team and they will help you setup this table and populate it.
- Populate - This is the field on the form that you would like to populate. In our example, we are pulling information from the Building field on the Asset record and populating our first Custom field.
- Filter 1 - Filter 1 is the first field we are using in our table to filter to a specific group of records. In our example we will select the Building value that is in our Assets table.
-
Once you have selected all of the required values, the Save and Add Level options will become available. A DrillDown lookup only requires 1 level, however to be effective 2 or more should be utilized. In our example, we'll select Add Level to continue the Drill Down by selecting the Floor Level for the building.
Now on this screen for your next level you'll have 2 fields to select. Populate and Filter 2 field. These fields function just as they did in the prior screen, so Populate is the field on the Contact form we want to use to store our value (Floor / Level) and Filter 2 is the value we want to use to filter our records (Floor).
Once the fields have been populated you will then have access to the Save and Add Level options at the bottom of the panel. In our example we will add one more level to allow for selection of the actual room. Once again a new drop down has been created for Populate and Filter. Here we will select the Room/Space field and the Room field from the custom table as our filter.
Now that we have selected our values we can press Save to store the lookup values. Once you have saved the values you can then select the X button on the upper right corner to close this panel. You can also use the Edit button to make adjustments to this top level or Delete to remove this level and revert back to the previous level.
Now that you have returned to the main Properties panel you must select Save to store the change to this fields attributes.
Once you have saved your changes, remove all the fields being used in the drilldown except for the last field in the drilldown lookup and then exit design mode. You should now see that the custom fields now include dropdown lookups for Building, Floor/Level, and Room / Space. When the values are selected for these new lookups it will automatically populate the Building and Floor / Level fields on the table as displayed in the images below.
Go ahead and save your record and you'll see that the custom fields on your Contact record are populated with data you pulled from an Asset record.
