More than one change manager for a category

[Mark McElhenny]

Hi we have a custom extension created by a previous employee that lets us select 3 different categories and when we do it sets the change manager responsible for that category. As the change manager field is a drop down box I've been asked if we could have two names for the operations group so that we can select either person for the Operations category. I've tried modifying the xml file however what ever I do just causes errors on the CM page after I have re ran setup. Attached is the XML file as it is now. I think its the following section where I need to add the extra name I just need to know the right syntax. The last line below assigns James to the Apps category, Jeff to the Operations and Elaine to the edi category (not sure how as nothing is defined in that last line about edi) I'd like to add another person under operations so that we can chose between Jeff and the other person.

<field id="category" xsi:type="AttributeEnum" _delta="redefine">
<values>
<value id="applications">applications</value>
<value id="edi">edi</value>
<value id="operations">operations</value>
</values>
<sql>category</sql>
<default_value>applications</default_value>
<is_null_allowed>true</is_null_allowed>
</field>
<field id="changemanager_id" xsi:type="AttributeExternalKey" _delta="redefine">
<filter>
<![CDATA[SELECT Person WHERE Person.friendlyname = IF(:this->category = 'applications', "James LoCicero", IF(:this->category = 'operations', "Jeffrey Montag", "Elaine Hines"))]]></filter></field>

[Jeffrey Bostoen]

One way to do it would be to add an "OR" condition with another IF.

Although I think it's not a good practice to hardcode the names here, just for the kind of situation you're running into. I think in the ideal solution you could link people to the change categories, and then derive them that way.

[Mark McElhenny]

Hi Jeffrey are you able to show me how to construct the above with the OR command, tried a couple of ways and just get error messages when I open CM. Thanks

[Jeffrey Bostoen]

I'd honestly seriously recommend against it and do a proper revision to avoid this kind of hardcoding.

But if you really want to go that way, it will look sth like this (if Jeff and Elaine are the people you want)

<![CDATA[SELECT Person WHERE Person.friendlyname = IF(:this->category = 'applications', "James LoCicero", IF(:this->category = 'operations', "Jeffrey Montag")) OR Person.friendlyname = IF(:this->category = 'applications', "Elaine Hines", "any-non-matching-name") ]]></filter>

Your ideal solution however would probably be creating a class so you have a link between one or more people on the one hand and the categories on the other hand.

[Mark McElhenny]

Hi Jeffrey, thanks will set this up. I agree it al needs redoing but this is just a quick fix as my superiors are pushing me for it.

[Mark McElhenny]

OK so tweaked it as I needed to add the extra user, the idea is we have two people available to choose as Change Manager when Operations is the category.

"<![CDATA[SELECT Person WHERE Person.friendlyname = IF(:this->category = 'applications', "James LoCicero", IF(:this->category = 'operations', "Jeffrey Montag")) OR Person.friendlyname = IF(:this->category = 'operations', "Grant Albitz", "Elaine Hines") ]]>"

However I get the following error when I go to CM

Error: Failed to issue SQL query: query = SELECT COUNT(DISTINCT COALESCE(Person_contact.id, 0), COALESCE(Person_person.id, 0)) AS COUNT FROM contact AS Person_contactINNER JOIN (person AS Person_person LEFT JOIN contact AS Person_manager_id_contact ON Person_person.manager_id = Person_manager_id_contact.id) ON Person_contact.id = Person_person.id WHERE ((((CAST(CONCAT(COALESCE(Person_person.first_name, ''), COALESCE(' ', ''), COALESCE(Person_contact.name, '')) AS CHAR) = IF(('applications' = 'applications'), 'James LoCicero', IF(('applications' = 'operations'), 'Jeffrey Montag'))) OR (CAST(CONCAT(COALESCE(Person_person.first_name, ''), COALESCE(' ', ''), COALESCE(Person_contact.name, '')) AS CHAR) = IF(('applications' = 'operations'), 'Grant Albitz', 'Elaine Hines'))) AND COALESCE((Person_contact.finalclass IN ('Person')), 1)) AND COALESCE((Person_manager_id_contact.finalclass IN ('Person')), 1)), mysql_errno = 1064, mysql_error = You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '))) OR (CAST(CONCAT(COALESCE(Person_person.first_name, ''), COALESCE(' ', ''' at line 1.

[Jeffrey Bostoen]

Sorry, missing quote

<![CDATA[SELECT Person WHERE Person.friendlyname = IF(:this->category = 'applications', "James LoCicero", IF(:this->category = 'operations', "Jeffrey Montag", "any-nong-matching-name")) OR Person.friendlyname = IF(:this->category = 'applications', "Elaine Hines", "any-non-matching-name") ]]></filter>