CiviCase Drupal Views Case Type

[pmoz]

Hi all,

I've been avoiding really setting up views for cases, but went in to try to play around today.

The goal was essentially to create a view of cases and filter by case type.

When I try to add the Case Type filter (or field for that matter) I get an error stating that there is no such field:

@import url(/sites/all/modules/civicrm/css/civicrm.css);
Sorry but we are not able to provide this at the moment.
DB Error: no such field

In ConfigAndLog I see that it's looking for the Label column:

Nov 18 15:39:03  [info] $backTrace = #0 C:\inetpub\wwwroot\sites\all\modules\civicrm\CRM\Core\Error.php(239): CRM_Core_Error::backtrace("backTrace", TRUE)
#1 [internal function](): CRM_Core_Error::handle(Object(DB_Error))
#2 C:\inetpub\wwwroot\sites\all\modules\civicrm\packages\PEAR.php(931): call_user_func((Array:2), Object(DB_Error))
#3 C:\inetpub\wwwroot\sites\all\modules\civicrm\packages\DB.php(975): PEAR_Error->PEAR_Error("DB Error: no such field", -19, 16, (Array:2), "SELECT  id, Label  \n FROM civicrm_case_type \n \n \n \n \n ORDER BY weight  ...")
#4 C:\inetpub\wwwroot\sites\all\modules\civicrm\packages\PEAR.php(564): DB_Error->DB_Error(-19, 16, (Array:2), "SELECT  id, Label  \n FROM civicrm_case_type \n \n \n \n \n ORDER BY weight  ...")
#5 C:\inetpub\wwwroot\sites\all\modules\civicrm\packages\DB\common.php(1905): PEAR->raiseError(NULL, -19, NULL, NULL, "SELECT  id, Label  \n FROM civicrm_case_type \n \n \n \n \n ORDER BY weight  ...", "DB_Error", TRUE)
#6 C:\inetpub\wwwroot\sites\all\modules\civicrm\packages\DB\mysql.php(899): DB_common->raiseError(-19, NULL, NULL, NULL, "1054 ** Unknown column 'Label' in 'field list'")
#7 C:\inetpub\wwwroot\sites\all\modules\civicrm\packages\DB\mysql.php(328): DB_mysql->mysqlRaiseError()
#8 C:\inetpub\wwwroot\sites\all\modules\civicrm\packages\DB\common.php(1216): DB_mysql->simpleQuery("SELECT  id, Label  \n FROM civicrm_case_type \n \n \n \n \n ORDER BY weight  \n")
#9 C:\inetpub\wwwroot\sites\all\modules\civicrm\packages\DB\DataObject.php(2442): DB_common->query("SELECT  id, Label  \n FROM civicrm_case_type \n \n \n \n \n ORDER BY weight  \n")
#10 C:\inetpub\wwwroot\sites\all\modules\civicrm\packages\DB\DataObject.php(441): DB_DataObject->_query("SELECT  id, Label  \n FROM civicrm_case_type \n \n \n \n \n ORDER BY weight  \n")
#11 C:\inetpub\wwwroot\sites\all\modules\civicrm\CRM\Core\PseudoConstant.php(586): DB_DataObject->find()
#12 C:\inetpub\wwwroot\sites\all\modules\civicrm\CRM\Case\PseudoConstant.php(164): CRM_Core_PseudoConstant::populate(NULL, "CRM_Case_DAO_CaseType", TRUE, "Label", "", NULL, "weight", "id")
#13 [internal function](): CRM_Case_PseudoConstant::caseType("Label", FALSE)
#14 C:\inetpub\wwwroot\sites\all\modules\civicrm\drupal\modules\views\civicrm\civicrm_handler_field_pseudo_constant.inc(59): call_user_func_array("CRM_Case_PseudoConstant::caseType", (Array:2))
#15 C:\inetpub\wwwroot\sites\all\modules\views\includes\handlers.inc(65): civicrm_handler_field_pseudo_constant->construct()
#16 C:\inetpub\wwwroot\sites\all\modules\views\includes\handlers.inc(87): _views_create_handler((Array:9), "handler", "field")
#17 C:\inetpub\wwwroot\sites\all\modules\views\views.module(1221): _views_prepare_handler((Array:5), (Array:21), "case_type", "field")
#18 C:\inetpub\wwwroot\sites\all\modules\views\includes\view.inc(2469): views_get_handler("civicrm_case", "case_type", "field")
#19 C:\inetpub\wwwroot\sites\all\modules\views\includes\admin.inc(4196): views_db_object->add_item("page", "field", "civicrm_case", "case_type")
#20 C:\inetpub\wwwroot\includes\form.inc(15

Not sure if this is related, but we are having CiviCase troubles today after the latest upgrade.

I've never tried to add the Case Type field before so I'm not sure if this worked prior to the upgrade.

As always, thanks.

[petednz]

can you confirm you have other civicrm based views working?

[pmoz]

I do.
Mostly around contacts and relationships.

The case ones work except when I try to add that one "Case Type"  field.

[petednz]

i just created a 'civicrm cases' View - added Case Type field, added Case Type filter - no problems - screenshot attached.

Does it help shed any light? Any chance your system has an invalid Case Type (if such a thing is possible)

[petednz]

an export too since i had it

[pmoz]

Thanks for the reply Pete.

I'll poke around a bit in the table.

Any ideas what might constitute an invalid case type?

[petednz]

nope but i just thought the issue might be at the civi end rather than the civi<->Views wiring

[demeritcowboy]

Take a look here and see if case-sensitive field names are the problem, i.e. it's looking for Label but the field name might be label.
http://dev.mysql.com/doc/refman/5.5/en/identifier-case-sensitivity.html

If not, then if you do "describe civicrm_case_type" does it have a field named label?

[pmoz]

Thanks guys for lending a hand.

I need it.

Upon further review, I do think there is something wrong with case_type table not with Views integration.

We've only created two case types in this install and one has a space in the name field and is marked, "the case type name includes deprecated characters."  It is disabled though the ui and correctly displays as disabled in the table.

As far as "Label" I don't see it anywhere in the table.  Maybe I am misinterpreting the error message in the ConfigAndLog.

The fields I have are:
id
name
title
description
is_active
is_reserved
weight
definition

[pmoz]

And more thoughts...

As I search through prior issues with the case_type table, they all mention to the option_value table which does have a field titled "label," with a lowercase l.

I do not see any reference to either of the custom case types in that table however.

This is a case type that was created earlier using xml.  The xml file name includes the space as well.  However, within the case type ui, the case has no activities or roles defined, while the xml has a list of activities and roles.  So it appears that the two aren't communicating.  When I go into the case type in the ui and add an activity, it saves as expected, but when I go back into the case type ui, the changes are not there.  Also in the table, the definition field for this case type is blob.

As far as I can tell, there were two test cases opened with it some time ago and deleted some time ago.

This issue may be related to mine as I am also getting a malformed link to case type page:
http://forum.civicrm.org/index.php/topic,34659

[demeritcowboy]

This sounds like some kind of upgrade problem where the table didn't get created/updated properly. See also possibly related https://issues.civicrm.org/jira/browse/CRM-15343

If you can't fix it by tweaking, are you able to go back to a previous version and try the upgrade again, maybe with the latest release?

[demeritcowboy]

Or, on second look, it looks like not having a label column is correct:
https://github.com/civicrm/civicrm-core/blob/master/xml/schema/Case/CaseType.xml

Maybe something in the view definition is still referencing label?

[demeritcowboy]

See line 96 here:
https://github.com/civicrm/civicrm-drupal/blob/7.x-master/modules/views/components/civicrm.case.inc

So it seems like it does want label, so I'm not sure how it worked for Pete?

[pmoz]

That's interesting.  Good thought process.

This upgrade made it into production as we did not stumble upon this until after testing.  So fixing this somehow would really save us quite some time.

Thanks for your help demeritcowboy.  I really appreciate it.

Pete, do you have any more insight?

[petednz]

not sure what you want so here is lots - if you see me on skype by all means ping me (see PM)

in the site i was referring to civicrm_case has the following columns in the table
id
case_type_id
subject
start_date
end_date
details
status_id
is_deleted

at civicrm/admin/options/case_type?group=case_type&reset=1 it shows
Label | Value | Decription | etc

civicrm_option_group shows that case_type = group 26

civicrm_option_value has the standard fields including Label and Name

and setting option_group_id = 26

shows the Case in question has

Label = Alt Ed Contract
Name = Alt Ed Contract


The XML in question is AltEdContract.xml

and in it has

<CaseType>
  <name>AltEd Contract</name>

(don't ask me what that has one space and not the other!)