List view filtered by last join occurence.

[jh]

Hi

Sorry if Ive missed something basic but is there a way to have a list join that shows the last occurence of the join only?

On a list view I have table A and there is a DBjoin on table B. In the list view, table A has a left join to table B and it shows all occurences of table B, where I would like the last one to show only in the list view?

I think this could be done by time/date, or alternatively the id of table B. Or have I messed the joins up?

Many thanks for any help.

 

[cheesegrits]

You could probably filter the list view with a prefilter that does something like this:

Field: table_b.id
Condition: EQUALS
Value: SELECT MAX(tb.id) FROM table_b AS tb WHERE tb.parent_id = table_a.id
Type: query
Apply to: Public

Obviously change the table names (but not the "AS tb" alias), and the foreign key (parent_id) / primary key (id) field names to suit.

This should work in list view. Problem will be that it will get applied to all access of that list, so it'll get applied in form and details view as well. Which means that when you edit the form, you'll only get the last repeat, and when you save it, the ones not shown will get deleted.

You might be able to work round that by having two copies of the list - one for the list display with the prefilter, and one without the prefilter, and use the custom edit and view links on the filtered list to point to the unfiltered form/details views.

-- hugh