Searchable Query field

I have a Query field called cb_userhastickets (or type Query using the Query CB plugin v5.0.4+ for CB 2.1.3) with a query that returns a text value of 'Yes' or 'No' depending on whether the user has any tickets in our ticketing system (RS-Tickets Pro). The query is:
SELECT ELT(EXISTS(select a.customer_id from #__rsticketspro_tickets a where a.customer_id= b.user_id)+1, 'No', 'Yes') FROM #__comprofiler b WHERE b.user_id = [user_id]

This works fine, displaying 'Yes' or 'No' appropriately, except that the resulting field is not searchable in front-end of back-end searches. We want to be able to filter users in CB by criteria where one of the conditions is whether or not they have tickets or not.

Is there a way, using this plugin or another or a combination to fetch data from a query to populate a field and have that field searchable?

Query fields are not searchable. They don't store a value in _comprofiler and CB has no idea how to go about inserting them into the userlist query to filter the users. You would need to store the value as a field or make a userlist with an Advanced filter that does a subquery to your tickets database table to pre-filter the list to those with a ticket or those without.

I did see the hover message on the fields list that indicated that the Query field type itself is not searchable. The idea that you would want to actually populate a Community Builder field (say a text field) with the results of a query to another database seems like it would be a non uncommon request. I was hoping someone at Joomlapolis would have an idea of the best way to do this.

I can obviously create a new text field that gets added to the comprofiler table and then just execute a SQL query to populate it from our ticketing system. That, unfortunately, does not provide a dynamic connection. I was hoping you folks had some dynamic way via CB Actions or one of your zillion plugins to actually create a dynamic connection to a database for the value of a field in CB.

Any ideas?

The idea that you would want to actually populate a Community Builder field (say a text field) with the results of a query to another database seems like it would be a non uncommon request. I was hoping someone at Joomlapolis would have an idea of the best way to do this.

Only way to do that is to use CB Auto Actions and a Field action to set a field to the result of a query field by substituting in the query field as the value. Example action usage as follows.

Field: cb_text
Operator: Set
Value: [cb_queryfield]
Translate Value: No

As for what trigger you do this on typically you'd use after user profile update and after user registration.

I can obviously create a new text field that gets added to the comprofiler table and then just execute a SQL query to populate it from our ticketing system. That, unfortunately, does not provide a dynamic connection. I was hoping you folks had some dynamic way via CB Actions or one of your zillion plugins to actually create a dynamic connection to a database for the value of a field in CB.

It is dynamic. It's pulling the existing value and not double storing it. What you want to do can't be done without double storing it. So you have to push the value to CBs database one way or another if you want it searchable.

Thank you. That's the pointer I needed. Now to go see if I can make it work (never used CB Actions before, so new learning curve).

Ok, apparently I need one more tip or bit of guidance. I created a new text field to save the Query result and installed CB Actions and created a new autoaction to set the text field to the result of the query field. I pick actions that would trigger it when someone registers, views their profile, or updates their profile - and those all appear to work perfectly one a user by user basis

Now I am looking to populate the field for all CB users, so I thought that if I used a trigger like onBeforeDisplayUsersList or onBeforeUsersListQuery and then created a userlist of all users with the new text field as part of the UserList and displayed it that would trigger an update for all users, but it does not.

Any tip on how I could get CB to perform the autoaction for all users in the Community Builder database?

Thank you for your guidance.