Create a Lookup Relationship

Tables can be linked to other tables through a lookup relationship. This link tells GroveSite to look up the data options for a field in a Lookup Table. For example, each different part in a Parts Database table may have a different manufacturer, but you will choose from the same list of manufacturers each time, which are stored in a Lookup Table.

Creating a lookup relationship enables you to select an entry from a standardized set of data you have stored in a Lookup Table. A database table can have multiple lookup fields. (Note: You must be an Organization Administrator and have the database editor feature.)

Video: Database Lookup Relationships

Step 1: Define a Lookup Table

Picture DB Lookup Define Lookup

Select Manage Database from your Org Admin menu. Next you will click [new table] in the database editor. This new table will include the data you will be looking up from other tables. Create as many Lookup Tables as needed to store your lookup information. For example, if you want to be able to lookup a location from a table, you will create a new table called Equipment Location. Within this database you will add fields that you want for each location (location, description, etc.). Be sure to select Save Table when finished. Repeat this process for any table you want to use as a lookup table.

Step 2: Define a New, Main Database Table

Picture DB Lookup Define Main

Select Manage Database from your Org Admin menu. Next you will click [new table] in the database editor. Give your table a name and then begin adding fields. To add a Lookup field, type in the name for the field and then select the Lookup Relation type from the drop down list. It will first show up as incomplete.

Picture DB Lookup Field Options

Click the field name to access Field Options. From the dropdown, select the Lookup Table you defined in Step 1. Don’t forget to click Save Field Options. Repeat this step for every lookup field you want included in the database you just created. Don’t forget to click Save Table when you are done adding your fields. Then click Exit Org Admin to return to your workspace.

Step 3: Add the Database Definitions to Pages in Your Site

Picture DB Lookup Page Edit

Turn on Edit from the top commands on your site and then select [new page] in the left navigation. Select My Database Tables and use the pull down to identify your table that you created in Step 1. In this example it would be the Equipment Location Lookup Table. Continue through the Add Page Wizard. Repeat this until each Lookup table is listed as a page in your site.

Picture DB Lookup Select Table

Once they are added you can add the table(s) you created in Step 2. In this example it would be the Parts Database Table that contains Lookup Fields.

Picture DB Lookup Relation

You will notice that when you click Next, you are asked to assign the site and page to be used as the data source for each Lookup Relation Field. To do this, just select Edit next to each row, and update the Site and Page you stored your Lookup Table on. Remember to hit Save after updating each field’s assignment. Once you have completed this for all Lookup Relation fields, click Next.

Alternatively, you can create a separate site within your org to host the lookup tables. In this case, you would follow the same steps except you would add the lookup tables on one site and then use that other site name when assigning the site and page (as above) for your main table.

Step 4: Add Data to Lookup Tables

First add data to your Lookup Tables. This will be all of the items you wish to refer to/lookup in your tables. Let’s say you add 3 records to the Location Lookup Table page for Hong Kong, Long Beach, and Phoenix.

Step 5: Add Data to Your Main Database Table

Picture DB Lookup Add Data to Main

Now go to your primary database page. From your table, select [Add a New…] at the top. Here you will be able to see a drop down list populated with the values from the Lookup Table.