Hi Kyle,

Thanks for the additional info, but it does not work, I get no listings when using:

( ( SELECT COUNT(*) FROM `#__cbsubs_subscriptions` AS sub WHERE sub.`user_id` = u.`id` AND sub.`status` = 'X' ) >= 1 ) AND ue.`cb_branch_code` = 'NSW'

I do get listings (via the CB backend) when searching for "Expired" listings with "NSW" in the cb_branch_code field.

I also tried:

( ( SELECT COUNT(*) FROM `#__cbsubs_subscriptions` AS sub WHERE sub.`user_id` = u.`id` AND sub.`status` = 'X' ) >= 1 AND ue.`cb_branch_code` = 'NSW' )

That did not work either, so I also tried:

( ( SELECT COUNT(*) FROM `#__cbsubs_subscriptions` AS sub WHERE sub.`user_id` = u.`id` AND sub.`status` = 'X' ) >= 1 ) AND u.`cb_branch_code` = 'NSW'

That gave this error:

Unknown column 'u.cb_branch_code' in 'where clause' SQL=SELECT COUNT( DISTINCT u.id ) FROM #__users u JOIN #__user_usergroup_map g ON g.`user_id` = u.`id` JOIN #__comprofiler ue ON ue.`id` = u.`id` WHERE u.block = 0 AND ue.banned = 0 AND ue.approved = 1 AND ue.confirmed = 1 AND g.group_id IN (18, 8) AND ( ( ( SELECT COUNT(*) FROM `#__cbsubs_subscriptions` AS sub WHERE sub.`user_id` = u.`id` AND sub.`status` = 'X' ) >= 1 ) AND u.`cb_branch_code` = 'NSW' )

Can you please let me know what else I should try?

Thanks,

Dion.

Hi Kyle,

The cb_branch_code is just a Text Field, but I did try your further suggestion AND ue.`cb_branch_code` LIKE '%NSW%' and I still don't get any listings with this either.

it's so much easier from the backend in the Community Builder: User Managment page using the advanced "Search Tools", where you can easily and quickly select "cb_branch_code" as the CB field with "Equal to" as the Select Operator and "NSW" for this field and "Expired" for Any Subscription and it works perfectly, using the provided drop-down fields. Can you please add this type of "Search Tools" for the front end filtering, like the backend?

We need this to work from the front end, what else can I try?

Thanks,

Dion.

Hi Kyle,

The problem is not:

( ( SELECT COUNT(*) FROM `#__cbsubs_subscriptions` AS sub WHERE sub.`user_id` = u.`id` AND sub.`status` = 'X' ) >= 1 )

or

( ( SELECT COUNT(*) FROM `#__cbsubs_subscriptions` AS sub WHERE sub.`user_id` = u.`id` AND sub.`status` != 'A' ) >= 1 )

that both display results, the issue is when adding the following as you suggested that then displays no results:

AND ue.`cb_branch_code` = 'NSW'

or

AND ue.`cb_branch_code` LIKE '%NSW%'

Why make it so difficult, if you simply had another drop-down field in the "Filter" tab for Basic searching (in the List Managment) for CBSubs Subscriptions with their Status options (as you have for the backend CB User Management), then this would solve this issue.

Can you please resolve this issue?

Thank you,

Dion.