List dropdown filter missing value when "where" clause active

[juuser]

Hi,

This threads heading might be a bit confusing, but here's my problem in more detail.

I have a databasejoin element which pulls values from prefix_users table. As some users are not active, I have entered a WHERE clause {thistable}.block = 0 as I don't want my users to be able to select inactive user names from the dropdown. This part works as expected.

But now, when I have a dropdown filter in list for that element, I can only filter active users. I would like to be able to filter all the values that exist in the list for that field.

I hope my explanation was clear enough

BR,
Martin

 

[troester]

There's a "filter where".
Here you can "undo" the data-where, a bit tricky: filter where is appended with AND
setting it to
1=1 OR {thistable}.block = 1
should do.

 

[juuser]

Thanks Troester,

but I'm struggling a bit with this. When I add the following "1=1 OR {thistable}.block = 1" to "Data Where" clause, I get all the names from users table in the dropdown, whether they are blocked or not

BR,
Martin

 

[troester]

Not to Data-Where/"joins where...", to the Data-Where/"filter where"

I get all the names from users table in the dropdown, whether they are blocked or not

I thought in the filter you want to see all users.
If you only want to see (in filter) all users (blocked or not) who are recorded in the list (so not all users from #_users) set "Recorded data" in element List view/filters settings.

 

[juuser]

It seems that "set "Recorded data" in element List view/filters settings." is exactly what I need, strange I hadn't noticed this myself.

Unfortunately, when I set "filter-where" field to "1=1 OR {thistable}.block = 0", it still shows blocked users in element dropdown on form edit.
when I set "filter-where" field to "{thistable}.block = 0", it doesn't show blocked user name in list filter although there is a record with that person's name.

So it seems filter-where overrides "Filter Data ->Recorded data" setting

 

[juuser]

Friendly bump

 

[cheesegrits]

Just got back from a road trip.

Works for me, in "Recorded Data" mode, with a WHERE filter of ...

{thistable}.blocked = '0'

... and a Filter WHERE of ...

1=1 OR {thistable}.blocked = '1'

https://www.screencast.com/t/c0JkdwrUp

I get all three uses in the filter, and only the not blocked ones in the element dropdown.

-- hugh

 

[juuser]

Thanks, it works now! It was just my stupid mistake, I removed "joins where" when added "filter where" clause.

BR,
Martin