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