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 6:28 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
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
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
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
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.