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.
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! 🙂
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
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!
Vote noted! 🙂
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.
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
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.
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.
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
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?
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
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.