How to apply filter based on custom profile field

How to apply filter based on custom profile field

by Sarath P -
Number of replies: 25

Hello everyone,

I am trying to add a filter for a custom profile field in the configurable report. I have tried adding a filter for username using the User field filter and used this %%FILTER_USERS:user2.username%% in my SQL query and it's working fine.

I have a custom profile field for businessunit. Can someone please tell me how to implement a filter for custom profile fields? Which filter should I use in the SQL query?

This is the query that I am using:

(SELECT d1.data FROM prefix_user_info_data d1

 JOIN prefix_user_info_field f1 ON d1.fieldid = f1.id AND f1.shortname = 'businessunit'

 WHERE d1.userid = user2.id

) AS 'businessunit',

(SELECT d1.data FROM prefix_user_info_data d1

 JOIN prefix_user_info_field f1 ON d1.fieldid = f1.id AND f1.shortname = 'location'

 WHERE d1.userid = user2.id

) AS 'location',


Moodle version: 3.10

Thanks in Advance.

Average of ratings: -
In reply to Sarath P

Re: How to apply filter based on custom profile field

by Randy Thornton -
Picture of Documentation writers
Sarath,

FILTER_USERS filter can work directly on the custom fields just as it can on the predefined fields. Choose that field in the Filters tab.

Then in the code, use a filter statement that looks like this.

%%FILTER_USERS:d1.data%%

where d1 would need to be the alias to the subselect where you are joining on the short name of business unit. You should then change the alias in the second subselect to not use d1 or f1, but just make them d2 and f2.

For example, on my test site there are two fields like this:

SELECT u.username,

    (SELECT d1.data 

    FROM prefix_user_info_data d1

    JOIN prefix_user_info_field f1 ON d1.fieldid = f1.id AND f1.shortname = 'testdrop'

    WHERE d1.userid = u.id

    ) AS "Drop_Down",

    (SELECT d2.data 

    FROM prefix_user_info_data d2

    JOIN prefix_user_info_field f2 ON d2.fieldid = f2.id AND f2.shortname = 'birthdate'

    WHERE d2.userid = u.id

    ) AS "Birthdate"

    FROM prefix_user u

    WHERE 1=1 

    %%FILTER_USERS:d1.data%%


NOTE: you can only use ONE FILTER_USERS declaration per query. So, you will have to choose whether you want the pull down for that to be the username or the businessunit value. You can't have both, unfortunately.




Attachment screenshot_10388.jpg
Average of ratings: -
In reply to Randy Thornton

Re: How to apply filter based on custom profile field

by Sarath P -
Hi Randy,
Thank you for your detailed response. I am also stuck with the search text filter. This is what I did - In my query, I have added %%FILTER_SEARCHTEXT:user2.username:~%% and in the Filters section I have provided %%FILTER_SEARCHTEXT:user2.username:~%% as ID Number and label as 'Search Username'. After adding the details, when I type a username and click apply, it's still showing all other users. Did I miss anything?
Is there any documentation with examples of the filters?
Average of ratings: -
In reply to Sarath P

Re: How to apply filter based on custom profile field

by Randy Thornton -
Picture of Documentation writers
Sarath,

The help for that fields in not very clear. You need to do the following:

1) In your code, you must add a identifier to your filter like this FILTER_SEARCHTEXT_ID1

where ID1 is just any arbitrary identifier you want. I usually just use _1 or _2 but you could use _username or to make it clearer just _U. There must be an underscore. So,

%%FILTER_SEARCHTEXT_U:user2.username:~%%

will work for you.

2) In the filter tab for the ID, add ONLY the identifier part without the underscore line, so 1 or 2 or U or whatever you use.

This method allows you to have two filters to search with, so:

%%FILTER_SEARCHTEXT_U:user2.username:~%%

%%FILTER_SEARCHTEXT_F:user2.firstname:~%%

then you would have two filters with IDs of U and F and Labels of "Username" and "Firstname"

Etc.


Attachment screenshot_10401.jpg
Average of ratings: Useful (2)
In reply to Randy Thornton

Re: How to apply filter based on custom profile field

by Motasem Al Haj Ali -

Hi Randy, 

I am trying this last two hours and I can't make it work. Is it still work for you?

The text filter box appear but when I try it, it dose not work. The column that I search is a tag column and contain more than one value separated with commas. 

IS "GROUP BY" impact the search text filter? 

Cheers 

Mo

Average of ratings: -
In reply to Motasem Al Haj Ali

Re: How to apply filter based on custom profile field

by Randy Thornton -
Picture of Documentation writers
That could be the issue. I don't see why commas would be an issue, so it may be the grouping.

If you run the query without the GROUP BY, does the search filter work? Could you post a sample of part of the query where you are using the filter with the tags?
Average of ratings: -
In reply to Randy Thornton

Re: How to apply filter based on custom profile field

by Motasem Al Haj Ali -

Hi Randy, 


Thanks a lot for quick reply. 

It works for me finallysmile, the problem was that I put the filters after grouping and it seems it should be after WHERE. What confused me is that no need for AND even if I have more than one filter. 

This is my code now and it is work, before that I placed the GROUP BY syntax before the filter one. 

WHERE ma.id = cm.instance

AND mc.id = %%COURSEID%%

%%FILTER_SEARCHTEXT_F:tag.name:~%%

%%FILTER_SUBCATEGORIES:mcc.path%%

GROUP BY mc.id, mu.id, ma.id


Now I am going to globalize this report but I need this report to show the data based on the user permission. So if the user is a teacher for three courses to be able to access the data for the three courses from one course block, is that possible?  

This report is connected to a specific plugin, I am happy to share it with the community later and I have some other reports I would like to share them with the community too.

Thanks a lot

Mo

Average of ratings: Useful (1)
In reply to Motasem Al Haj Ali

Re: How to apply filter based on custom profile field

by Randy Thornton -
Picture of Documentation writers
Mo,

More reports are always welcome on the Ad-hoc contributed reports page.

I've also added a section to the documentation to clarify how the filter syntax works.

Average of ratings: Useful (3)
In reply to Randy Thornton

Re: How to apply filter based on custom profile field

by Motasem Al Haj Ali -

Hi Randy, 


Many thanks, I think it is so clear now. But did you notice the syntax in the following examples in the same page, all filters come after GROUP BY or ORDER BY.

1- List all courses WITHOUT Syllabus

2- List of courses WITHOUT a resource with a name starting by "syllabus"

3- List of courses have MULTIPLE resource with a name like "Syllabus%"

4- Extra Credit Items by Name Only

5- Rubrics without zero values in criteria.

Regards

Mo


Average of ratings: -
In reply to Motasem Al Haj Ali

Re: How to apply filter based on custom profile field

by Randy Thornton -
Picture of Documentation writers
The first three have the GROUP BY in its subselect not in the main clause.

The fourth and fifth ones don't work, they have the old style of search text syntax. Even if you fix them to something like %%FILTER_SEARCHTEXT_C:c.idnumber:~%% it will only work if you move the filter statement into the WHERE clause.

(See my explanation of how this works now here: https://docs.moodle.org/311/en/ad-hoc_contributed_reports#Using_the_Search_Text_filter).


Eg for the fourth one, this is the proper syntax for the latest CR :

WHERE gi.itemname LIKE '%extra credit%'
AND gi.gradetype = '1'
AND gi.hidden = '0'
AND gi.aggregationcoef = '0'
AND c.visible = 1
AND (SELECT COUNT( ra.userid ) FROM prefix_role_assignments AS ra JOIN prefix_context AS ctx ON ra.contextid = ctx.id WHERE ra.roleid = 5 AND ctx.instanceid = c.id) > 0
%%FILTER_SEARCHTEXT_C:c.idnumber:~%%

GROUP BY Course_ID, gi.id
ORDER BY StartDate, Course_ID

Where in the Filters definitions you have a Search Text filter with an ID Number set to "C".
Average of ratings: Useful (2)
In reply to Randy Thornton

Re: How to apply filter based on custom profile field

by Motasem Al Haj Ali -

Thanks Randy, 


Average of ratings: -
In reply to Motasem Al Haj Ali

Re: How to apply filter based on custom profile field

by Randy Thornton -
Picture of Documentation writers
You're welcome.

Actually the old style of search text box without the id number will still work when using only one search text filter, for backwards compatibility. But it is better to use the new syntax in any case. I find people always request adding more search boxes smile on multiple fields. This feature was added to Config Reports version 3.9.0 from November 2020.
Average of ratings: -
In reply to Randy Thornton

Re: How to apply filter based on custom profile field

by Motasem Al Haj Ali -
Hi Randy,

I was trying the above drop down menu (in your post Wednesday, October 13, 2021, 1:12 AM) but for some reason I just have been directed to an ERROR page.

My Test Code:
SELECT mu.firstname, mu.address
#adress column contain the program names, and it is in the user table

FROM prefix_user AS mu

WHERE RAND()<0.0001
#I used RAND() for this test but in another report I have no RAND() still the same
%%FILTER_USERS:mu.address%%

My Filter Tab:
I added the user field filter, but it keep direct me to Error page. not sure why.

By the way this query will pull very small number of rows. 

Regards
Mo
Attachment Captureds.PNG
Attachment Capturejk.PNG
Average of ratings: -
In reply to Motasem Al Haj Ali

Re: How to apply filter based on custom profile field

by Randy Thornton -
Picture of Documentation writers
This works for me:

SELECT mu.firstname, mu.address
FROM prefix_user AS mu
WHERE 1=1
%%FILTER_USERS:mu.address%%

with that filter on.

A http 500 error is a server based error and could be caused by a lot of things outside the reports. And it's probably a web server error, not a database error.
Average of ratings: -
In reply to Randy Thornton

Re: How to apply filter based on custom profile field

by Motasem Al Haj Ali -

Thanks Randy, 

I suppose there is a problem in the plugin, as that dose not work for me. 

Please take a look here. 

https://github.com/jleyva/moodle-block_configurablereports/issues/207


Mo

Average of ratings: -
In reply to Motasem Al Haj Ali

Re: How to apply filter based on custom profile field

by Randy Thornton -
Picture of Documentation writers
I've seen that bug too, which is why I avoid using more than one user filter. Instead, as a workaround, I use a search text field instead for one of the tables.
Average of ratings: -
In reply to Randy Thornton

Re: How to apply filter based on custom profile field

by Motasem Al Haj Ali -

Yeh, The search filter working fine, but I was looking for the drop down menu to have a list of programs that are in our selected data. I hope this problem will be fixed soon. 


Thanks Randy

Average of ratings: -
In reply to Motasem Al Haj Ali

Re: How to apply filter based on custom profile field

by Motasem Al Haj Ali -

Randy, 

I thought I should update you with this.

I tried to test the user field filter in my local Moodle and it is work for me, I think the problem is that this filter retrieve the data from the system and not form the current report, so in the production Moodle site, it needs to retrieve a massive amount of data for that drop down menu and that what cause the problem.

I have 5 users in my local Moodle and one of them first name is 'admin', so I tried this code, but I still have the different 5 users in the dropdown filter or a user field filter. 

SELECT mu.firstname, mu.address

FROM prefix_user AS mu

WHERE mu.firstname LIKE 'admin'

%%FILTER_USERS:mu.firstname%%


If the next version can retrieve the data based on the conditions or restrictions in the report that would solve the problem. Or we should find a way to customize the filter separately to suit the our need. something like the following

(%%FILTER_USERS:mu.firstname%% WHERE courseid = 1234) >>> this code of course dos not work


Regards

Mo


Average of ratings: -
In reply to Motasem Al Haj Ali

Re: How to apply filter based on custom profile field

by Randy Thornton -
Picture of Documentation writers
Mo,

Yes, that would be great if it could do that. FILTER_USERS has no context to control the list now.

Another possible solution would be if instead of a table and field, you could put an Alias name for the source - then you could make a sub select anywhere in the query and use its results.
Average of ratings: -
In reply to Randy Thornton

Re: How to apply filter based on custom profile field

by Motasem Al Haj Ali -

Randy, 

I had tried a few ways but so far no one worked for me. Please let me know if it is work for you. 

Regards

Mo


Average of ratings: -
In reply to Motasem Al Haj Ali

Re: How to apply filter based on custom profile field

by Randy Thornton -
Picture of Documentation writers
I've never gotten it to work in the users filter. You can do things in some of the other filters, for example I often use CONCAT(u.firstname, u.lastname) in the SEARCH text filter. But that's because David Saylor added some fixes last year to the serach text filter. The standard filters like USER and course don't have any flexibility.

Another possible solution would be to clone the SEARCHTEXT filters and instead make it appear as a drop down list of the possible values. That would be nice.

Only someone actually editing the plugin itself could do this, but it does have the ability to have extra filters added in the code.
Average of ratings: -
In reply to Randy Thornton

Re: How to apply filter based on custom profile field

by Motasem Al Haj Ali -

This is the class link 

At the time I was looking in the code I found something about sending emails, do you know any thing about that?


Thanks

Mo

Average of ratings: -
In reply to Motasem Al Haj Ali

Re: How to apply filter based on custom profile field

by Randy Thornton -
Picture of Documentation writers
No I don't. I always use the Ad-hoc database queries plugin to send email out since that's one of its core functions. I haven't used it on the Config Reports plugin so I am not sure exactly how it works.
Average of ratings: -
In reply to Randy Thornton

Re: How to apply filter based on custom profile field

by Motasem Al Haj Ali -

I could not find any thing about the emailing feature too, but When you create new report and only when you select the type of the report is 'sql report' you have the choice to schedule the query to run every day at night. I can't understand this choice. I am going to schedule it and try what will happened. 

Do you think this will send an email to the block's manager? Or do you think we need to add a code to send the email? anyhow we will see. 

Thanks

Mo


Average of ratings: -
In reply to Motasem Al Haj Ali

Re: How to apply filter based on custom profile field

by Randy Thornton -
Picture of Documentation writers
To my knowledge there is no email function at all in Config Reports. The Schedule the query function simply runs the queries as they are at the set time.

You can test this by simply setting a report to run and setting the time - and you won't get any mail.

I recall from long ago that this feature is related to pulling data from a remote database source if you are using one. However, I don't do that and don't know exactly what the scheduling is for. Perhaps someone else here does use that and can speak to it.

If I have to email reports, I copy/paste the code into the Ad-hoc queries plugin and set up the email that way. I use it for emails and I use Config Reports for user interactive reports. It would be great if these were one plugin that did both, but I doubt that will happen.
Average of ratings: Useful (2)