Issue with semicolon separated values if

[TrautsYard]

When configuring and testing "Enroll by user profile fields" enrolment method it apears that if the string in the field is to long it fails to enroll the user.

We populate the field with a semicolon separated list containing the short name of Timetabled courses. Any course that is in column 5 or after does not result in the student being enrolled.

Example:

Criteria is field contains 09GEC01 User with " 09ENG09;09FFL08;09GEC01;09HPE08;09MAA02;09PED02;09RED08;09SCI08;09SFL01;ME2" enrolls User with " 09ART02;09EED06;09ENG01;09FFL05;09GEC01;09HPE02;09MAB03;09RED05;09SCI02;LO2" does not enroll

Thanks

[ndunand]

Hi,

That should not be an issue, we have fields with up to 100 values and the plugin deals with them without fail.

If you go and edit the method, you should get debugging info at the bottom of the page. Does it help pinpoint the issue? If not please post them here (make sure to redact any sensitive information).

[TrautsYard]

Firstly, Thanks for replying. I am using the class 09GEC01 as the example but this is occurring for all classes where the value is not in the first 3.

There are 26 users you are timetabled in the 09GEC01 Class and only the 15 with the value in the third column enrolled. I edited the values in the [mdl_user_info_data] table, moving the value to the third column. Ran the "Invalidate cache for Enrol by user profile fields" task. Ran the "Process all rules and enrolments for Enrol by user profile fields" task.

All 26 users enrolled. This Plugin would be perfect for our needs.

Can you provide any advice on what to check or look at? Any assistance would be appreciated.

We run Moodle 3.9.1+ on IIS 10 Win Server 2016, MS SQL 2016.

[ndunand]

Hello,

This is probably something I overlooked, as it should behave as you'd expect.

To pinpoint the issue, could you Edit the relevant enrolment method? Below the form there should be debugging information starting with customtext1... please post it here in full, making sure to redact any sensitive information.

[TrautsYard]

Thanks, Here is what I think you asked for.

customtext1= stdClass Object ( [rules] => Array ( [0] => stdClass Object ( [param] => TtbClasses [value] => 09GEC01 ) ) ) line 111 of \enrol\attributes\edit.php: call to debugging()

fieldsandrules= Array ( [customuserfields] => Array ( [1] => dob [2] => Status [3] => TtbClasses [4] => schoolyear [5] => HSE [6] => classof [7] => HG ) [rules] => Array ( [0] => stdClass Object ( [param] => TtbClasses [value] => 09GEC01 ) ) ) line 113 of \enrol\attributes\edit.php: call to debugging()

arraysql= Array ( [select] => RIGHT JOIN {user_info_data} d1 ON d1.userid = u.id AND d1.fieldid = 3 [where] => ( CONVERT(varchar(32), d1.data) = CONVERT(varchar(32), ?) OR CONVERT(varchar(32), d1.data) COLLATE Latin1_General_CS_AS LIKE ? ESCAPE '' OR CONVERT(varchar(32), d1.data) COLLATE Latin1_General_CS_AS LIKE ? ESCAPE '' OR CONVERT(varchar(32), d1.data) COLLATE Latin1_General_CS_AS LIKE ? ESCAPE '') [params] => Array ( [0] => 09GEC01 [1] => %;09GEC01 [2] => 09GEC01;% [3] => %;09GEC01;% ) ) line 115 of \enrol\attributes\edit.php: call to debugging()

sqlquery= SELECT DISTINCT u.id FROM {user} u RIGHT JOIN {user_info_data} d1 ON d1.userid = u.id AND d1.fieldid = 3 WHERE ( CONVERT(varchar(32), d1.data) = CONVERT(varchar(32), ?) OR CONVERT(varchar(32), d1.data) COLLATE Latin1_General_CS_AS LIKE ? ESCAPE '' OR CONVERT(varchar(32), d1.data) COLLATE Latin1_General_CS_AS LIKE ? ESCAPE '' OR CONVERT(varchar(32), d1.data) COLLATE Latin1_General_CS_AS LIKE ? ESCAPE '') line 118 of \enrol\attributes\edit.php: call to debugging()

countusers= 15 line 120 of \enrol\attributes\edit.php: call to debugging()

[ndunand]

Thanks for the details.

I looks live there might be some cropping of the field values, indicated by the CONVERT(varchar(32), d1.data) part. Could you then check the following point:

In the profile fields details, what is the "Maximum length" value (I typically use a length of 2048 for a similar field).

[TrautsYard]

Yes, I thought that might be it when I posted it. You are correct the max length is 2048(the default value). In my case I would not have more that 64 characters in any of the "User profile fields".

Thanks.

[ndunand]

Were you able to resolve it then? Should I close this issue?

[TrautsYard]

Sorry, Whilst I understand the issue, I am nor a Moodle Developer and I am not sure how to resolve this. Could you provide some guidance please?

[ndunand]

Well, this is not handled by the plugin, but rather by the database abstraction layer. What type of database are you using, and what Moodle version are you on?

[TrautsYard]

We run Moodle 3.9.1+ on IIS 10 Win Server 2016, MS SQL 2016.