[SOLVED] Error is display list with regex

Hi,

The solution what we needed is this;

We have 7000 users. And many of them are in 'teams', because the have had a weekend with a team. So we give them numbers like 301, 302 etc. And for example 301 are 10 people, but someone can be also in 2 or more teams.
We use numbers like 301, but also 'staf' members so we are able to give them more access to some pages or management info.

How can we do this in a good way trought CB?

You can't, because there is no SQL function to handle your situation. Normally you'd use REGEXP, but you allow the field to be empty in some situations and you can't have an empty REGEXP in SQL as it will fatally error the SQL. The best way to do this is to not filter the userlist at all and just have the field searchable so the user can search whatever team they want. There maybe an SQL function to handle this though, but beyond REGEXP usage I'm not aware of one. You're welcome to check in the below MYSQL documentation though.

dev.mysql.com/doc/refman/5.6/en/func-op-summary-ref.html

It's relatively simply to check for a single value in a list of values as shown earlier, but checking for a list of values in a list of values becomes a lot more complicated for SQL; especially if either side of the operator can be empty.

I'm not entirely sure the below will work, but it maybe worth a shot. Idea is to condition the filter entirely.


Note the above assumes your field is a select field so it replaces |*| with REGEXP "or" usage of |.

Hi,

Thank for your reply. Your suggestion does not work.

But how can we create a whole new other system of tagging people into teams and let them only see some contactinfo from the members of the same team? And with this they are also in a certain group according to their teamnumber.

Just tried the below in SQL and I think it might work for your usage.


Basically what it does is if the users cb_teams field has a value it'll do the REPLACE and try to match all users using REGEXP. If cb_teams field has no value it trys to match REGEXP with the string NOVALUE, which will always fail resulting in no users displayed.

Please give the above two solutions a try and see if either will fit your needs.

The last one works; Thank you very much!

Hi Krileon,

This solution is working, but now we have a small 'problem'.

We have team 320 and team 20
People from 320 see also people of team 20.

Can we be more specific with the query so only exact teamnumbers are searched on?