---
title: Advanced lookups
description: "You can use a lookup to create a listbox to select a value from another table. The creation of a lookup is described in the video [Data Project Fine Tuning](/docs/manage-table-options/). The plugin used in that video is an older version.…"
url: "https://docs.legacy.wpdataaccess.com/docs/advanced-lookups/"
updated: "2025-11-26"
category: Legacy Tools / Forms / Advanced Features
---

# Advanced lookups

You can use a lookup to create a listbox to select a value from another table. The creation of a lookup is described in the video [Data Project Fine Tuning](/docs/manage-table-options/). The plugin used in that video is an older version. Starting from version 3.0.3 you can create lookups to other databases.

## Lookups to other databases

A lookup to another database allows you, to, for example, use your WordPress user accounts to manage roles and user access on a remote database. The plugin creates two parallel connections, one to your WordPress database and one to your remote database and joins the results. Adding this kind of lookup is just as simple as creating a lookup to a table in the same database, you just need to select the appropriate database from the listbox.

## Static conditional lookups

You might however want to add additional conditions to a lookup table. By default, this is not possible, but the plugin allows you to use a view for lookup purposes as well, which allows you add a filter to your lookup. An example could be a list of WordPress user with a specific role.

Let's presume we have a group of coaches in our organization. Each coach has a WordPress account and has at least the role coach. A coach might also have other roles, but we're not interested in these other roles. We just want to create a lookup which shows only coaches.

This can be achieved by joining the tables wp_users and wp_usermeta. Let's create a view **coaches** which gives us a list of all the coaches in our organization:

Create view coaches

```
create view coaches as
select ID, user_nicename
from wp_users, wp_usermeta
where ID = user_id
  and meta_key = 'wp_capabilities'
  and meta_value like '%coach%'
```

If we create a lookup to table wp_users, we get a list of all WordPress users. But if we now create a lookup to view coaches, we only get the WordPress users having role coach. We have succesfully added a condition to our lookup.

## Dynamic conditional lookups

The disadvantage of the previous lookup is that its condition is static. For some cases we want to be able to create a dynamic condition. Let's have a look at the following example.

The image below (click to enlarge) shows the data model of a Student Administration System. If we want to add a grade for a specific student, a row must be added to table **wpda_sas_grade**. We are however, allowed to only add grades for courses a student has subscribed to.

  [![](https://wpdataaccess.com/wp-content/uploads/2020/04/wp-data-access-sas.png)](https://wpdataaccess.com/wp-content/uploads/2020/04/wp-data-access-sas.png)

For our lookup we need to select the courses from table **wpda_sas_class**for this specific student. Since table **wpda_sas_class** does not contain any usable information for our lookup, we need to move up one more level to table **wpda_sas_course**. Unfortunately this table doesn't contain any useful information as well, so we need to go up one more level to table **wpda_sas_module**, where we finally find the module name which we can show in our lookup.

Again, we can use a view. Let's join the tables **wpda_sas_course**, **wpda_sas_module** and **wpda_sas_class**:

```
create view `wpda_sas_course_lookup_with_filter` as
select `wpda_sas_course`.`course_id` as `course_id`
,      `wpda_sas_module`.`module_name` as `module_name`
,      `wpda_sas_class`.`student_id` as `student_id`
from `wpda_sas_class`
  left join `wpda_sas_course` on `wpda_sas_class`.`course_id` = `wpda_sas_course`.`course_id`
  left join `wpda_sas_module` on `wpda_sas_course`.`module_id` = `wpda_sas_module`.`module_id`
```

Please notice that I selected column **student_id**, **course_id**and **module_name**. We will show the module_name in the lookup. The **course_id**and **student_id**are used in combination to find the courses to which the student is subscribed.

You can use this view to create a lookup for a parent-child page, for which the student table is your parent and the grade table your child. In this case, you need to add two lookup columns: **student_id**and **course_id**. The **course_id**must be the first column selected in your lookup. This column represents the select value and destination column. The **student_id** must be the second column in the selection and must represent the parent key.

The video below shows a demo of the implementation of the filter mentioned above.

**IMPORTANT** Please notice that tab Manage Table Options is no longer available. Table Options was renamed to Project Templates and can now be accessed directly from the plugin menu.

[Legacy: Dynamic lookup demo](https://www.youtube.com/embed/LUDNbdr827E?feature=oembed&controls=1)