Filter for custom profile field

Filter for custom profile field

by Kimber Warden -
Number of replies: 5

Using the Filter tab, I've added a filter for a custom user profile field: School. In the summary for the filter, it says, "profile_school"

What code can I put in the SQL to reference the filter?

%%FILTER_USERS:u.profile_school%%  doesn't work. I select a school from the dropdown on View Report and nothing happens.

I've also tried %%FILTER_USERS:prefix_user_data_info.data%% with the same results. Can anyone help?

Kimber

Average of ratings: Useful (1)
In reply to Kimber Warden

Re: Filter for custom profile field

by Peter Bowen -

Custom user profiles are stored in a different table, using a slightly different syntax.

I have created Division as a custom field, and use the following to insert the filter. (It may not be elegant, but it works)

  INNER JOIN prefix_user_info_data AS ad
  ON fs.userid = ad.userid
  WHERE ad.fieldid=19
  %%FILTER_DIVISION:ad.data%%

You will need to check in the table mdl_user_info_field to see which id holds the custom field you are looking for, but that number would then be the fieldid in mdl_user_info_data.

I hope that helps somewhat?

Cheer
Peter

 

Average of ratings: Useful (1)
In reply to Peter Bowen

Re: Filter for custom profile field

by Kimber Warden -

Do you mean that you first created a custom filter plug-in called DIVISION and then called it using FILTER_DIVISION? Or are you saying you created the filter on the Filter tab in Customizable Reports?

On the filter tab, I'm able to create a user filter field for the custom profile field "School." The Summary column of the filters table says "profile_school."

I've tried calling the filter with

%%FILTER_USERS:prefix_user_info_data.data%%

and

%%FILTER_SCHOOL:prefix_user_info_data.data%%

and

%%FILTER_profile_school:prefix_user_info_data.data%%

and

%%FILTER_PROFILE_SCHOOL:prefix_user_info_data.data%%

But all of these return "no records found" when I run them on the View Report tab. Do I need to create a custom filter plug-in?

Average of ratings: Useful (1)
In reply to Kimber Warden

Re: Filter for custom profile field

by Jaswant Tak -

Try this one


SELECT u.firstname, u.lastname, u.email,
        MAX(CASE WHEN f.shortname = 'DIVISION' THEN d.data ELSE '' END) AS DIVISION
FROM prefix_user u
JOIN prefix_role_assignments ra ON ra.userid = u.id
JOIN prefix_role r ON r.id = ra.roleid AND r.shortname =  'student'
JOIN prefix_user_info_data d ON d.userid = u.id
JOIN prefix_user_info_field f ON d.fieldid = f.id
WHERE f.shortname IN ('DIVISION')
%%FILTER_USERS:d.data%%

GROUP BY u.id

Average of ratings: Useful (2)
In reply to Kimber Warden

Re: Filter for custom profile field

by Joan Cervan -
Hi, I have the same problem... I solved it adding a searchtext filter, as shown:

SELECT u.username AS Usuari, UserData.data AS "Unitat de Negoci", b.name AS Insignia, CASE WHEN b.courseid IS NOT NULL THEN (SELECT c.shortname FROM prefix_course AS c WHERE c.id = b.courseid) WHEN b.courseid IS NULL THEN "*" END AS Context, CASE WHEN t.criteriatype = 1 AND t.method = 1 THEN "Activity Completion (All)" WHEN t.criteriatype = 1 AND t.method = 2 THEN "Activity Completion (Any)" WHEN t.criteriatype = 2 AND t.method = 2 THEN "Manual Award" WHEN t.criteriatype = 4 AND t.method = 1 THEN "Course Completion (All)" WHEN t.criteriatype = 4 AND t.method = 2 THEN "Course Completion (Any)" ELSE CONCAT ('Other: ', t.criteriatype) END AS Criteriatype, DATE_FORMAT( FROM_UNIXTIME( d.dateissued ) , '%Y-%m-%d' ) AS dateissued, CONCAT ('link') AS Details FROM prefix_badge_issued AS d JOIN prefix_badge AS b ON d.badgeid = b.id JOIN prefix_user AS u ON d.userid = u.id JOIN prefix_badge_criteria AS t ON b.id = t.badgeid JOIN prefix_user_info_data AS UserData ON u.id = UserData.userid WHERE t.criteriatype <> 0 AND UserData.fieldid = 2 %%FILTER_SEARCHTEXT:UserData.data:~%% ORDER BY UserData.data, u.username, dateissued DESC

It is a modified version of badges report shown here to show a user custom field and filter by it.

Average of ratings: Useful (1)
In reply to Joan Cervan

Re: Filter for custom profile field

by Randy Thornton -
Picture of Documentation writers


Jaswant's solution posted above worked fine for me in 3.1

The filter is: %%FILTER_USERS:d.data%% where you have done the standard joins to the profile field, though the important part is the CASE statement that restricts that data to the field you want.


Average of ratings: Useful (2)