Query field to show date in year-month format

J2.5.16 CB1.9.1 CBS3.0.0

I want to create a field that shows the expiry date of the user's active subscription (when one exists) in year-month format (something like 2014-02). I have tried to use the query plugin to create this field but it refuses to appear on the designated profile tab (or throw any error messages).

I have attached a screen shot of the Query field configuration. It is my first use of the Query plugin and I am not experienced in SQL queries so I expect the issue is my understanding or execution. The query I am currently using (I have tried others unsuccessfully!) is:

SELECT DATE_FORMAT(`expiry_date`,'%Y-%m') FROM `sag13_cbsubs_subscriptions` WHERE `user_id` = '[user_id]' AND `status` = 'A';

Seams to work fine in my tests. Replace the table prefix though so Joomla can replace it automatically. Try the below.


Ensure the CB Query Field plugin it self is also enabled within CB > Plugin Management.

Thanks Kyle.

Still didn't show - no error or field title either. But your assurance that my query was OK pointed me to try other things.

I was testing this in the backend view and the culprit seemed to be that I had the field's Location | Profile Edit set as the default Disabled. Changing this to Enabled showed the field (and correct value). I gather that the backend view which has edit functionality is affected by this setting.

What is a little funny and a little sad is that now I have this working, I find that the generated data is not stored in the database as i assumed (but probably calculated on the fly). I wanted this field to allow grouping by it in Fabrik's report generator. It seems I need to work out how to generate it on the fly within Fabrik!

Query fields don't store anything. They query the database and output the results. So you won't be able to do userlist filtering or anything of the sort on a query field.