[SOLVED] List Advanced Search Database join Element

[marcq]

Hi,

Could someone explain me how I can filter in list view database join elements.
When I'm choosing a database join element into my Advanced Search Popup I can't select the values :


List view parameter :



Thank you in advance for your tips and support.

Cheers, marc

 

[cheesegrits]

I'll have to look at your setup, but the credentials I have for the GPRH site don't work.

-- hugh

 

[cheesegrits]

Note that the usual reason you may not see values in the advanced search dropdown is that we apply your join element "WHERE filter" to the query that builds the options for the filter dropdown. So if you use any element placeholders in that, like ...

"WHERE {thistable}.country = '{youtable___country_id_raw}'"

... or whatever, that won't yield any results in the filter dropdown, because element placeholders won't have any values (there's no "row" of data associated with the filtering).

I've been meaning to add an option to those settings, to specify whether or not to use the main WHERE filter in the filter context. I added a separate "Filter WHERE" a while back, and had intended to add an option saying whether to use that as well as, or instead of, the main WHERE. But never got round to it.

Anyway, that's one possibility.

-- hugh

 

[marcq]

Thank you Hugh for your explanations,

In my case, "Joins where and/or order by statement (SQL)" and "Filter Where" are empty in both database join elements.

I would appreciate if you could have a look, credentials are now working and you have access to the backend. You will find informations about the concerned list, form and database join elements into My Sites | GPRH | Notes.

Cheers, marc

 

[cheesegrits]

OK, seems to be because you have the "Filter Data" in the filter settings for that element set to "Recorded data" (well, "Global", which is taking your global default). And none of the rows in the table have a value set for "type de finance d'entre".

The difference between the two "Filter Data" modes, for join elements is, is:

"Recorded Data" only shows you options that actually exist in the form's data.

"Show All" shows you all possible options, ie. all options from the joined table.

So say you have a "Contact" form, with a join to "US States". If you have your "Filter Data" set to "Show All", the filter dropdown will show you all 50 states, regardless of whether any of those states have been selected in submitted Contact forms. If you have it set to "Recorded Data", and only 3 states have been selected in all your submitted Contact forms, the filter dropdown will only show you those 3 states.

So because that join element has no data in any rows, the dropdown for the filter will be empty when set to "Recorded Data".

I changed it to "Show All", so you now see all three possible options from the joined table. Even though none of those will yield any results.

-- hugh

 

[marcq]

Thanks a lot for those helpful information and "case study" Hugh. It is clear and it is working as expected. Cheers, marc