Filtering lookup listbox

Hi @kebhin,

Interesting question! ??

You can do this with a dynamic conditional lookup. This is explained here:
https://wpdataaccess.com/docs/data-projects/advanced-lookups/

You basically need to create a view and use the view in your lookup. Let me know if you need help…

Good luck,
Peter

Thanks Peter, but I don’t think dynamic conditional lookup does it. This depends on creating a view that can filter the condition I’m looking for.
Actually the view concept is very simple – it would be

create view listbox as select member_id,member_name from members where group_id=$$USERID$$

But of course I can’t use $$USERID$$ in a view definition

Essentially I need to get a variable that is not stored in the database as part of the view or listbox condition. If I could somehow get 2 columns (group_id+member_id) into the Source column name of the relationship that would also work

Just a thought, Template > Relationship screen there is an option for WHERE clause, can you try there.

Hi Rahim and @kebhin,

I found a way to store the WordPress user ID in the database! ??

This new feature allows plugin users to use the WordPress user ID in queries, database triggers, procedures, functions and views. The only limitations is that it works within WP Data Access only.

To use it in views you need to create a database function per database for which I will add a button to the Data Explorer.

It will be available in the next release. I’ll let you know…

Best regards,
Peter

• This reply was modified 2 years, 3 months ago by Passionate Programmer Peter.

Thanks Peter that is fantastic news and will really enhance the possibilities managing relations in WP Data Access

Hi all,

I released version 5.2.3 early today which is the first version that supports access to the WordPress user ID in SQL. ?? You can now access the WordPress user ID from your queries, views, triggers, procedures and functions.

Here is the documentation:
https://wpdataaccess.com/2022/08/18/wordpress-user-id-in-sql/

You’ll also find a description how to make other WordPress user info like user login or email available in SQL.

Hope this help! Let me know if you need assistance.

Best regards,
Peter

Hi Peter

That looks good. I am just finalising a release of my site and once that is deployed I will upgrade my development to 5.2.3
A question occurs to me about the use of the function in views. If I’m logged in as an admin user, go into Data Explorer and create the view as documented, does that mean when logging out and logging back in as a normal user that the view will work as expected (i.e using the currently logged in user id)?

Best regards
Kebhin

Hi Kebhin,

The function is stored in the database, just like your view. If you disconnect and connect again, they will both stil be there. No worries! ??

The session variable is a different thing. It becomes available when you connect to the database (presuming you connect through WP Data Acces) and is gone when you disconnect. When another user uses the same connection (from the pool), the session variable is changed to match the correct WordPress user ID.

Play around and let me know what you think… ??

Best regards,
Peter