Lookup queries on child column using parent filter

Lookups supported in previous versions of the plugin could be based on one single column only. The new version allows to add lookups based on multiple columns in parent-child forms. Additional columns can be used to filter the lookup list in such a way that only parent related data is shown.

Example single column lookup

If a student wants to register for a course, the course id is stored alongside the student id. For this situation a lookup can be used to add a listbox showing all courses (e.g. module names) to the user. The user is able to select from a meaningful list instead of a list of course ids.

Example multi column lookup

To prevent that grades can be added for courses a student has never attended, a multi column lookup can be used. The first column of the lookup is the column for which the listbox is generated (there can be only one). To add a filter to the lookup query, additional parent columns can be added. All these columns need to be part of the parent key. In the example below the user can only select courses which the student has attended.

NOTE

To create a lookup you can use a view as well. In the example above a view is used to create a filter over two level: MODULES > COURSES > GRADES.

Peter Schulz is a lecturer in information technology at the HAN university of applied sciences. He is an experienced programmer with more than 25 years experience in Oracle databases and tools. Peter is the developer of WordPress plugin WP Data Access.

Leave a Reply

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