Filter by custom field

Filter by custom field

di paul ashley -
Numero di risposte: 10

I'm using the latest version of the plugin on Moodle 2.2. When I try to filter by a custom field ( one I created in the users profile) I get the following error 'error reading from database'. The filter works fine when I use one of the standard user fields.  

Is it just that it won't work with custom fields, even though they are listed in the report filter?

Many thanks

Paul

Media dei voti:  -
In riposta a paul ashley

Re: Filter by custom field

di Juan Leyva -
Immagine Core developers Immagine Moodle HQ Immagine Particularly helpful Moodlers Immagine Plugin developers Immagine Testers

Hi Paul,

can you enable the debugging in Moodle?, is in Server - Debugging - Select Developers and display errors

Try again, and copy and paste here the full error, don't forget to disabling debugging later

Regards

Media dei voti:  -
In riposta a Juan Leyva

Re: Filter by custom field

di paul ashley -

Here's the message with debugging turned on


Notice: Undefined index: conditions in C:\moodle\blocks\configurable_reports\components\filters\fuserfield\plugin.class.php on line 98

Error reading from database


Thanks

Media dei voti:  -
In riposta a Juan Leyva

Re: Filter by custom field

di paul ashley -

Any progress?  Has any one else come accross this.  Its realy causing me some issues and would love to get it resolved.  Happy to give access to our test system if that helps.


Many thanks

Media dei voti:  -
In riposta a paul ashley

Re: Filter by custom field

di Chris M -

Yes, I have this issue too and it is a problem as we have many custom profile fields which we need to report on.  (Using 2.3.)

The report itself also pulls data into the wrong fields - although this same data displays correctly in the user profile. 

The debug report I have is very long so I sent it separately to Juan in the hope that he can spare the time to review and fix his excellent plugin.

Media dei voti:  -
In riposta a paul ashley

Re: Filter by custom field

di Juan Leyva -
Immagine Core developers Immagine Moodle HQ Immagine Particularly helpful Moodlers Immagine Plugin developers Immagine Testers

Hi,

first of all, besure that you are using the stable version that you can download from moodle.org/plugins

second, I need to know which SQL query is causing the error reading from database message, Paul, are you sure you have select the DEVELOPERS mode in the debuggin screen?

Media dei voti:  -
In riposta a Juan Leyva

Re: Filter by custom field

di paul ashley -

Yes downloaded and installed the latest version (2011040103)


In developers debug mode I get the following

Notice: Undefined index: conditions in C:\moodle\blocks\configurable_reports\components\filters\fuserfield\plugin.class.php on line 98

Error reading from database

More information about this error

Debug info: SQLState: 42000<br>
Error Code: 421<br>
Message: [Microsoft][SQL Server Native Client 10.0][SQL Server]The ntext data type cannot be selected as DISTINCT because it is not comparable.<br>

SELECT DISTINCT(data) as data FROM mdl_user_info_data WHERE fieldid = '3' AND userid IN ('1','2','3','4','5','6','7','8','9','10','11','12','13','14','15','16','17','18','19','20','21','22','23','24','25','26','27','28','29','30','31','32','34','39','40','41','42','43','44','45','60','62','63','64','70','72','73','77','78','79','80','81','82','84','85','86','88','89','90','91','92','93','94','95','97','98','99','102','103','104','105','106','107','108','109','110','111','112','113','114','115','116','117','118','119','122','123','124','125','126','127','128','129','130','131','132','133','135','136','138','139','140','141','143','144','145','146','147','148','149','150','151','152','155','156','157','158','161','162','164','166','168','169','174','175','176','177','178','181','184','186','187','188','189','190','191','194','196','197','198','199','200','201','205','210','211','212','213','214','215','216','217','218','221','222','223','225','233','234','235','236','237','238','239','241','244','245','246','247','248','249','252','253','254','255','256','258','263','264','265','266','267','268','269','270','271','272','274','275','276','277','278','284','287','288','289','290','291','292','293','294','295')
[array (
0 => '3',
1 => '1',
2 => '2',
3 => '3',
4 => '4',
5 => '5',
6 => '6',
7 => '7',
8 => '8',
9 => '9',
10 => '10',
11 => '11',
12 => '12',
13 => '13',
14 => '14',
15 => '15',
16 => '16',
17 => '17',
18 => '18',
19 => '19',
20 => '20',
21 => '21',
22 => '22',
23 => '23',
24 => '24',
25 => '25',
26 => '26',
27 => '27',
28 => '28',
29 => '29',
30 => '30',
31 => '31',
32 => '32',
33 => '34',
34 => '39',
35 => '40',
36 => '41',
37 => '42',
38 => '43',
39 => '44',
40 => '45',
41 => '60',
42 => '62',
43 => '63',
44 => '64',
45 => '70',
46 => '72',
47 => '73',
48 => '77',
49 => '78',
50 => '79',
51 => '80',
52 => '81',
53 => '82',
54 => '84',
55 => '85',
56 => '86',
57 => '88',
58 => '89',
59 => '90',
60 => '91',
61 => '92',
62 => '93',
63 => '94',
64 => '95',
65 => '97',
66 => '98',
67 => '99',
68 => '102',
69 => '103',
70 => '104',
71 => '105',
72 => '106',
73 => '107',
74 => '108',
75 => '109',
76 => '110',
77 => '111',
78 => '112',
79 => '113',
80 => '114',
81 => '115',
82 => '116',
83 => '117',
84 => '118',
85 => '119',
86 => '122',
87 => '123',
88 => '124',
89 => '125',
90 => '126',
91 => '127',
92 => '128',
93 => '129',
94 => '130',
95 => '131',
96 => '132',
97 => '133',
98 => '135',
99 => '136',
100 => '138',
101 => '139',
102 => '140',
103 => '141',
104 => '143',
105 => '144',
106 => '145',
107 => '146',
108 => '147',
109 => '148',
110 => '149',
111 => '150',
112 => '151',
113 => '152',
114 => '155',
115 => '156',
116 => '157',
117 => '158',
118 => '161',
119 => '162',
120 => '164',
121 => '166',
122 => '168',
123 => '169',
124 => '174',
125 => '175',
126 => '176',
127 => '177',
128 => '178',
129 => '181',
130 => '184',
131 => '186',
132 => '187',
133 => '188',
134 => '189',
135 => '190',
136 => '191',
137 => '194',
138 => '196',
139 => '197',
140 => '198',
141 => '199',
142 => '200',
143 => '201',
144 => '205',
145 => '210',
146 => '211',
147 => '212',
148 => '213',
149 => '214',
150 => '215',
151 => '216',
152 => '217',
153 => '218',
154 => '221',
155 => '222',
156 => '223',
157 => '225',
158 => '233',
159 => '234',
160 => '235',
161 => '236',
162 => '237',
163 => '238',
164 => '239',
165 => '241',
166 => '244',
167 => '245',
168 => '246',
169 => '247',
170 => '248',
171 => '249',
172 => '252',
173 => '253',
174 => '254',
175 => '255',
176 => '256',
177 => '258',
178 => '263',
179 => '264',
180 => '265',
181 => '266',
182 => '267',
183 => '268',
184 => '269',
185 => '270',
186 => '271',
187 => '272',
188 => '274',
189 => '275',
190 => '276',
191 => '277',
192 => '278',
193 => '284',
194 => '287',
195 => '288',
196 => '289',
197 => '290',
198 => '291',
199 => '292',
200 => '293',
201 => '294',
202 => '295',
)]
Stack trace:
  • line 394 of \lib\dml\moodle_database.php: dml_read_exception thrown
  • line 252 of \lib\dml\sqlsrv_native_moodle_database.php: call to moodle_database->query_end()
  • line 368 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->query_end()
  • line 773 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->do_query()
  • line 807 of \lib\dml\sqlsrv_native_moodle_database.php: call to sqlsrv_native_moodle_database->get_recordset_sql()
  • line 110 of \blocks\configurable_reports\components\filters\fuserfield\plugin.class.php: call to sqlsrv_native_moodle_database->get_records_sql()
  • line 129 of \blocks\configurable_reports\report.class.php: call to plugin_fuserfield->print_filter()
  • line 17 of \blocks\configurable_reports\filter_form.php: call to report_base->add_filter_elements()
  • line 169 of \lib\formslib.php: call to report_edit_form->definition()
  • line 150 of \blocks\configurable_reports\report.class.php: call to moodleform->moodleform()
  • line 629 of \blocks\configurable_reports\report.class.php: call to report_base->print_filters()
  • line 97 of \blocks\configurable_reports\viewreport.php: call to report_base->print_report_page()
Media dei voti:  -
In riposta a paul ashley

Re: Filter by custom field

di Juan Leyva -
Immagine Core developers Immagine Moodle HQ Immagine Particularly helpful Moodlers Immagine Plugin developers Immagine Testers

So it seems to be a problem related to a SQL query that doesn't work in SQL Server

Chris, are you using also SQL Server?

Media dei voti:  -
In riposta a paul ashley

Re: Filter by custom field

di Juan Leyva -
Immagine Core developers Immagine Moodle HQ Immagine Particularly helpful Moodlers Immagine Plugin developers Immagine Testers

I think I fixed it, please can you replace the contents of your components/filters/fuserfield/plugin.class.php file with the contents of this url:

https://raw.github.com/jleyva/moodle-block_configurablereports/MOODLE_21_STABLE/components/filters/fuserfield/plugin.class.php

Regards

Media dei voti:  -
In riposta a Juan Leyva

Re: Filter by custom field

di paul ashley -

Juan

Thanks so much for your effots on this, it is apprechiated.  I now get a different error.  It loads the filter, which it didn't before, but I get a 404 - file or directory not found error.  With debuging on I get the following (before I apply the filter);

Notice: Undefined index: conditions in C:\moodle\blocks\configurable_reports\components\filters\fuserfield\plugin.class.php on line 98

Did you remember to make the first column something unique in your call to get_records? Duplicate value 'Ansbury Staff' found in column 'data'.
  • line 816 of \lib\dml\sqlsrv_native_moodle_database.php: call to debugging()
  • line 110 of \blocks\configurable_reports\components\filters\fuserfield\plugin.class.php: call to sqlsrv_native_moodle_database->get_records_sql()
  • line 129 of \blocks\configurable_reports\report.class.php: call to plugin_fuserfield->print_filter()
  • line 17 of \blocks\configurable_reports\filter_form.php: call to report_base->add_filter_elements()
  • line 169 of \lib\formslib.php: call to report_edit_form->definition()
  • line 150 of \blocks\configurable_reports\report.class.php: call to moodleform->moodleform()
  • line 629 of \blocks\configurable_reports\report.class.php: call to report_base->print_filters()
  • line 97 of \blocks\configurable_reports\viewreport.php: call to report_base->print_report_page()
Media dei voti:  -