

Get in touch

Download Plugin Now
  • Follow
  • Follow
WP Data Access
  • Download
  • Support
  • Features
  • Pricing
  • Documentation
    • Tool Guide
    • App Builder
    • Data Explorer
    • SQL Query Builder
    • Plugin Settings
    • Legacy Tools
    • Remote Connections
a
M
M
  • Download
  • Support
  • Features
  • Pricing
  • Documentation
    • Tool Guide
    • App Builder
    • Data Explorer
    • SQL Query Builder
    • Plugin Settings
    • Legacy Tools
    • Remote Connections
Download Plugin Now

Legacy Tools

  • Deprecating in the near future

Tables

  • Getting Started
  • Creating and publishing
  • Responsive data tables
  • SEO friendly data tables
  • Adding hyperlinks
  • Extension Manager
  • Style Manager
  • Language settings
  • Styling
    • Style Manager
    • Premium styling
    • Global styling
    • Code Manager Styling
    • Manual styling
    • Styling issues
  • Filters
    • Static filters
    • Interactive filters
      • URL parameters
      • Search Panes
      • Search Builder
      • Search form
      • Advanced search
      • Geolocation search
  • Export & other buttons
    • Export to PDF, CSV, Excel, SQL
    • Print & copy buttons
    • Column selection buttons
    • Custom buttons
  • Advanced features
    • Advanced settings
    • Large tables & performance
    • Progressively SHOW MORE
    • Custom queries
    • Custom Post Types
    • Row grouping
    • Responsive master-detail
    • Master-detail tables
    • Non admin user access
  • Demos & tutorials
    • Demos
    • Tutorials

Forms

  • Data Projects
  • Projects and templates
  • Supported Data Types
  • One-To-Many relationships
  • Many-To-Many relationships
  • Registration page
  • Manage table options
  • Shortcode usage
  • Data Forms
    • Data Forms
    • Overview
    • Project Demo
    • Page Demo
  • Advanced features
    • Adding filters
    • Custom validation
    • Add less/more button
    • User roles
    • Managing roles and users
    • Advanced table options
    • Advanced lookups
    • Front-end toolbar
    • Transfer to other WP site
  • Demos & tutorials
    • Demos
    • Tutorials

Templates

  • Project Templates
  • Creating templates
  • Table Settings
  • Relationships
  • List Table
  • Data Entry
  • Reconcile

Designer

  • Getting started
  • Introduction
  • Basic mode

Dashboards

  • Getting started
  • Dashboards
  • Sharing dashboards
  • Dashboards Widgets
    • Dashboard Widgets
    • Publication Widget
    • Project Widget
    • Chart Widget
    • Custom Code Widget
    • Database Widget
    • Sharing Widgets

Charts

  • Chart Widget

Code

  • Shortcodes
    • Overview
    • wpdataaccess
    • wpdadataproject
    • wpdadataforms
    • wpdadiehard
    • wpdageomap
    • wpdawidget
  • Plugin Variables
    • Environment variables
  • Hooks & filters
    • Overview
    • Hooks
      • Overview
      • wpda_add_search_actions
      • wpda_add_search_filter
      • wpda_before_list_table
      • wpda_after_list_table
      • wpda_wpdataaccess_prepare
    • Filters
      • Overview
      • wpda_column_default
      • wpda_before_simple_form
      • wpda_after_simple_form
      • wpda_construct_where_clause
  • API
    • WP Data Access API
    • Remote database access
    • CRUD Example
    • Extensions
      • Alternative search algorithm
      • Alternative buttons extension
  • Code Manager
    • Overview
    • Write code
    • Share(d) code
View Categories

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. 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.

Data model WP Data Access Student Administration System

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.

Share This Article :
  • Facebook
  • X
  • LinkedIn
  • Pinterest
Still stuck? How can we help?

How can we help?

Updated on 2025-02-10
Advanced table optionsFront-end toolbar

12 Comments

  1. Chiara
    Chiara on 2020-04-27 at 8:26 am

    Hi Peter,

    is there a possibility to integrate a new entry into the lookup?
    So for example if I want to select a “status” for a product and i did a lookup for this status field. Then I see that the status I want to use isn’t in the table status yet. Can I add a new status directly in the product page, which is then automatically added to the table status in my db? Or do I always have to use an extra page with a specific customer entry?

    Thank’s in advance!
    Chiara

    PS: you’re suggestion last week worked out very well! Thank you so much! 🙂

    Reply
    • Peter Schulz
      Peter Schulz on 2020-04-27 at 8:24 pm

      Hi Chiara,

      Sorry, but you cannot add a new entry into the lookup. To manage a lookup table, you need to create an administration page for it. I added your request to my to do list. I’ll try to add it in the future, but I cannot promise when it is available. The list is quite long… 😉

      Good to hear it worked out well! 🙂

      Best regards,
      Peter

      Reply
      • Sue Hemmens
        Sue Hemmens on 2021-11-22 at 7:06 pm

        Can I add a vote for this feature? Or a workaround where you could have an action button to open the management form if the lookup doesn’t contain the value you need.

        Fantastic product!

        Reply
        • Peter Schulz
          Peter Schulz on 2021-11-24 at 3:53 pm

          Vote noted! 🙂

          Reply
  2. Jeffrey
    Jeffrey on 2020-10-18 at 11:02 pm

    Hey Peter,

    I am loving your plugin, but I am trying to figure out how to auto populate a field based on another selection.

    For example:

    Table_A has names and descriptions of possible classes. Table_B has a relationship with Table A allowing me to Lookup the names of the classes from Table_A and select one from a dropdown in Column_1. In Column_2 I want it to auto populate the Description from that chosen class in Column_1.

    Reply
    • Peter Schulz
      Peter Schulz on 2020-10-19 at 6:54 am

      Hi Jeffrey,

      Thank you for your nice words! 🙂

      This video explains how to add a lookup:
      https://wpdataaccess.com/docs/data-apps/manage-table-options/

      In short:
      – Open Manage Table Options
      – Add Table_A to your table options
      – Edit Table_A options
      – Add a lookup to your Table_A options that uses TableB

      Hope this helps,
      Peter

      Reply
      • Jeffrey
        Jeffrey on 2020-10-20 at 9:24 pm

        Thank you for the reply!

        That is not quite what I am looking for. I have set up the Lookup for Column_1 in Table_B.

        The part that I am struggling with is the Inner Join portion of it. I want Column_2 to auto fill with information from Table_A after I use the dropdown in Table_B Column_1.

        As a recap of what I am trying to make happen:
        Add Row to Table_B
        Select option from Dropdown in Column_1
        Column_2 auto fills with option that relates to the selection.

        Or Alternatively. (Not to complicate things.)
        If I was able to limit Column_2 to only give me what if available based on Column_1’s selection.

        I just dont understand how to limit it with the additional fiels in the relationships to restrict what options it gives me.

        Reply
        • Jeffrey
          Jeffrey on 2020-10-22 at 7:34 pm

          Update*

          I don’t know a lot about SQL but I am learning. I figured that it was worth a try to import some code directly because it looks like that is what is required for some advanced filtering.

          I took your second example above `wpda_sas_course_lookup_with_filter`and saved it to a notepad doc as Test.sql. I also changed the name of the view to ‘test’ instead of `wpda_sas_course_lookup_with_filter’.

          I was hoping to see it in the list of table/view names so that I could see how it would appear or be able to manage it after it was created. Sadly it wasn’t. Should it appear in the list if imported or will it not show up if imported?

          I really like this program, and wish I understood more about SQL, but this is really helping understand how SQl functions.

          Reply
          • Peter Schulz
            Peter Schulz on 2020-10-22 at 8:56 pm

            The wpda_sas_course_lookup_with_filter is quite complicated. It uses the parent key to filter the child table. It is a lookup with an extra filter. The simplest way to create a lookup is to add it to a single column and create a listbox of available values.

            Can you send me some screenshots? You can use the contactform if you prefer to send it in private. THX

            Reply
  3. Alex
    Alex on 2021-04-14 at 11:12 am

    Hi Peter, I would really love to implement this as well. Since my setup is much simpler than yours (similar to Jeffrey’s request) I even more wonder why I can’t get it right: I have two tables:
    1) tableview1 with 1200 rows and 2 columns: col1 enum (‘a’, ‘b’, ‘c’), col2 varchar(255).
    2) table2 with 500 rows and 50+ columns: e.g. col10 enum (‘a’, ‘b’, ‘c’), col11 varchar(255)
    I want a “simple” lookup for col11 (table2) using col2 (tableview1) filtered by col10 (table2). Is that possible?

    Reply
    • Peter Schulz
      Peter Schulz on 2021-04-14 at 1:01 pm

      Hi Alex,

      If I understand your question correctly, your lookup needs to filter on the data of another column in the same row. That is currently not supported. Sorry! Let me know if I misinterpreted your question.

      Best regards,
      Peter

      Reply
      • Alex
        Alex on 2021-04-23 at 11:44 am

        Hi Peter, you understood correctly! I have now realized that this approach is not practicable in many ways, this is only one disadvantage. I will switch to relational scheme using more tables, but it was good for my learning curve. Thanks, Alex.

        Reply

Submit a Comment Cancel reply

Your email address will not be published. Required fields are marked *

Table of Contents
  • Lookups to other databases
  • Static conditional lookups
  • Dynamic conditional lookups
WP Data Access
  • Follow
  • Follow
Quick Links
$

Blogs

$

Tutorials

$

Demos

Get in touch
$

Premium support

$

Free support forum

$

Contact us

Resources


WordPress plugin directory



YouTube tutorials

Copyright © 2025 | All Right Reserves

We use cookies to ensure that we give you the best experience on our website. If you continue to use this site we will assume that you are happy with it.Ok