Support

    You are here:  
  1. Home
  2. Support
  3. Changelog and Roadmap
Toggle Submenu
  1. Forums
  2. Recent
  3. Categories
  4. Tags
  5. Users
  6. Badges
Support is currently Offline
Support Offline
It's past support hour. We are taking some rest and will come back stronger tomorrow. Promise!
Official support hours:
Monday To Friday except Saturday and Sunday
From 09:30 To 17:00
Our current time: Friday 5:51 am
Add a reply View Replies (9)

Resolved JSN User list - Selection based on custom field

  1. davidunwin
    davidunwin
  2. General
  3. Monday, January 22 2018, 01:18 PM
I have a custom field 'internal'
The the field has a number of selections
0|Executive Team
1|Main Committee
2|Planning sub-committee
3|Administration
4|Member

I am trying to display just Executive team members in the user list
So I set the SQL to be this: internal = (0)

But when I use this it selects everybody

How can I just select the Executive team??

Any help would be great
Attachments (2)
Custome Field.png Custome Field.png
Modules mod_jsnusers.png Modules mod_jsnusers.png
  1. jsn_users
Accepted Answer
admin
admin Accepted Answer
Admin
Hi,
you have choose to use multiples values for your select, so the values are stored into JSON format.

So in your case you can use Custom SQL Where like this 
internal LIKE '%"0"%'


see docs at http://docs.easy-profile.com/index.php/article/sql-custom-where
  1. more than a month ago
  2. General
  3. # Permalink
Responses (9)
davidunwin
davidunwin Accepted Answer
Great, that works.... Thank you so much for your quick response... much appreciated
  1. more than a month ago
  2. General
  3. # 1
davidunwin
davidunwin Accepted Answer
Just one thing the documentation reads like this for these types of fields

NOTE: fields like checkbox or select (with multiple value enabled) store the data in DB with JSON format, so something like this ["music","art","design"], so to filter users you need to use SQL LIKE command, example to have users interested to music: interests LIKE '%"music"%'

Indicating you should use the text value.
  1. more than a month ago
  2. General
  3. # 2
WWA
WWA Accepted Answer
Pro
I would like to exclude certain selected values in a list. What is the JSON equivalent of NOT_IN or the opposite of LIKE?
For example: ["current","awaiting-payment","suspended"]
What is filter for users NOT suspended?
DISLIKE '%"suspended"%'
  1. more than a month ago
  2. General
  3. # 3
admin
admin Accepted Answer
Admin
Hi,
correct SQL syntax is 
NOT LIKE '%"suspended"%'
  1. more than a month ago
  2. General
  3. # 4
WWA
WWA Accepted Answer
Pro
user_id NOT IN (210) payment_status NOT LIKE '%"Suspended"%'

This displays 0 Users. I'm not sure what's wrong.
  1. more than a month ago
  2. General
  3. # 5
admin
admin Accepted Answer
Admin
Hi,
try with something like this ;) 
user_id NOT IN (210) AND payment_status NOT LIKE '%"Suspended"%'
  1. more than a month ago
  2. General
  3. # 6
WWA
WWA Accepted Answer
Pro
This doesn't seem to work. Users who are suspended still show in list. It is a select list but not multiple selection. Is that the problem?
  1. more than a month ago
  2. General
  3. # 7
admin
admin Accepted Answer
Admin
Hi,
if a select does not have multiple selection option then the correct Where is 
user_id NOT IN (210) AND payment_status NOT LIKE 'Suspended'
  1. more than a month ago
  2. General
  3. # 8
WWA
WWA Accepted Answer
Pro
That works!! Thanks!!
  1. more than a month ago
  2. General
  3. # 9
  • Page :
  • 1


There are no replies made for this post yet.
However, you are not allowed to reply to this post.

Request Support

Support is currently Offline
Ask a Question
Official support hours:
Monday To Friday except Saturday and Sunday
From 09:30 To 17:00

Support Availability

Working days: Monday to Friday. The support staff is not available on weekends; in the most of cases tickets will not be answered during that time.

Reply time: Depending on the complexity of your support issue it's usually between a few minutes and 24 hours for paid members and about one week for free members. When we expect longer delays we will notify you.

Guidelines

Before you post: read the documentation and search the forums for an answer to your question.

When you post: include Site Details if you request a support (you can use the form below the reply in Site Details tab).

Auto Solved Question: If after a week the author of the post does not reply to a request by moderator, the question will be marked as resolved.

Language: only English

Search Users

Easy Profile® is not affiliated with or endorsed by Open Source Matters or the Joomla Project. Joomla is Free Software released under the GNU/GPL License.

We use a few cookies

Our site uses non fat, delicious cookies which help us improve your user experience. You can read more about it in our Legal page.