Pre-Filter Relational List

[automan25]

I'm trying to pre-filter a list that is joined to a parent list. Let's say this child list is a list of fishing licenses (the many side of the relationship). The parent list is a list of fishermen; the names of the license holders (the one side of the relationship). Each fisherman may have more than one record in the child list if they have obtained multiple licenses over the years. What I would like to be able to do is pre-filter the license list to only show licenses belonging to fishermen whos most recent license is expired. I can easily pre-filter the list to show all expired licenses, but this will also show fishermen with current licenses who have had expired licenses in the past. I hope I explained this clearly. Any ideas how to do this?

 

[PaulV888]

Hi,

You should be able to come very close by doing a sub query on your child table by limiting the number of records returned to 1.
You can do this with something like this:

SELECT MAX(license.ID) FROM licenses WHERE 1 GROUP BY fishermanID

Check that in myphpadmin, this should give you the latest/newest row id per fisherman.

Then in your pre-filter you can make a condition of type Query like
license.id IN SELECT MAX(license.ID) FROM licenses WHERE 1 GROUP BY fishermanID

You should be able to combine that with your expired pre-filter.

Paul