Add a many to many relationship

Prerequisite: You must be familiar with the Syntax used in Tutorials and have already created an extension.
We also assume that you are familiar with dashboard design within iTop and OQL.

Maybe, you know already how to do it… Nevertheless, you could check:

• Limitations
• Filter (for 3.1.0+)
• Uniqueness Rule (for 3.1.0+)

This tutorial will explain how to create a many-to-many relationship between two classes,

1. starting by explaining what cannot be done with such relationship
2. then how to create the Link class
3. and how to make this relation visible from one or both of the remote classes

For the purpose of the exercise, let's document a relationship between Person and Location, in which we would store an accreditation level and the last time the person went on the site.

A many-to-many relationship in iTop is stored as a class with a special XML tag is_link in its properties, set to 1.
This tells iTop how to handle such class in the User Interface, Console and Portal.

When creating a link class, be aware that this type of class does not support every type of fields. Also it seems obvious for multiple, some are more like a current (3.1.0 and below) iTop limitation.

Unsupported fields:

• Caselog
• LinkedSet, LinkedSetIndirect
• Text, HTML, Template,
• File, Image
• Duration
• TagSet, EnumSet

• class id: The naming convention for Link class, start with lnk in lowercase, followed by the first class in alphabetic order, so here Location, then To, followed by the last class name, here Person
• parent: cmdbAbstractObject
• category: bizmodel
• db_table: iTop convention is to use the class name in lowercase
• naming and reconciliation: it's quite logic to name and identify the relationship with the 2 related object, it's a unique name only if the relationship does not support duplicates between 2 objects.

itop_design / classes

    <class id="lnkLocationToPerson" _delta="define">
      <parent>cmdbAbstractObject</parent>
      <properties>
        <category>bizmodel</category>
        <abstract>false</abstract>
        <db_table>lnklocationtoperson</db_table>
        <is_link>1</is_link>
        <naming>
          <attributes>
            <attribute id="location_id"/>
            <attribute id="person_id"/>
          </attributes>
        </naming>
        <reconciliation>
          <attributes>
            <attribute id="location_id"/>
            <attribute id="person_id"/>
          </attributes>
        </reconciliation>
      </properties>

Handling the fields:

• Two AttributeExternalKey person_id and location_id must be declared
• Naming convention for an ExternalKey is the class name in lower case, followed by _id
• For a link class, it's not recommended to use any different naming as it complicates the writing of OQL queries by users, when not followed.
• Additional fields can be added to the Link class, taking into account the above limitations.

itop_design / classes

    <class id="lnkLocationToPerson" _delta="must_exist">        
      <fields _delta="define">
        <field id="location_id" xsi:type="AttributeExternalKey">
          <sql>location_id</sql>
          <filter/>
          <dependencies/>
          <is_null_allowed>false</is_null_allowed>
          <target_class>Location</target_class>
          <on_target_delete>DEL_AUTO</on_target_delete>
        </field>
        <field id="person_id" xsi:type="AttributeExternalKey">
          <sql>person_id</sql>
          <filter/>
          <dependencies/>
          <is_null_allowed>false</is_null_allowed>
          <target_class>Person</target_class>
          <on_target_delete>DEL_AUTO</on_target_delete>
        </field>
        <field id="accreditation" xsi:type="AttributeEnum">
          <sql>accreditation</sql>
          <default_value>restricted</default_value>
          <is_null_allowed>false</is_null_allowed>
          <values>
            <value id="restricted">
              <code>restricted</ code>
            </value>
            <value id="unrestricted">
              <code>unrestricted</ code>
            </value>
          </values>
          <display_style>radio_horizontal</display_style>
          <dependencies/>
          <tracking_level>all</tracking_level>
        </field>
        <field id="last_visit" xsi:type="AttributeDate">
          <sql>last_visit</sql>
          <is_null_allowed>true</is_null_allowed>
          <label>Last visit</label>
          <tracking_level>all</tracking_level>
        </field>
      </fields>
    </class>

Handling the class presentation: details and list are mandatory, default_search and search are optional,

itop_design / classes

    <class id="lnkLocationToPerson">     
      <presentation _delta="define">
        <list>
          <items>
            <item id="location_id"><rank>10</rank></item>
            <item id="person_id"><rank>20</rank></item>
            <item id="accreditation"><rank>30</rank></item>
            <item id="last_visit"><rank>40</rank></item>
          </items>
        </list>
        <default_search>
          <items>
            <item id="location_id"><rank>10</rank></item>
            <item id="person_id"><rank>20</rank></item>
          </items>
        </default_search>
        <details>
          <items>
            <item id="col:col1">
              <items>
                <item id="location_id"><rank>10</rank></item>
                <item id="person_id"><rank>20</rank></item>
                <item id="accreditation"><rank>30</rank></item>
                <item id="last_visit"><rank>40</rank></item>
              </items>
              <rank>10</rank>
            </item>
          </items>
        </details>
      </presentation>
    </class>

Resulting in an iTop 3.1 to show this pop-up when adding a Visitor to a Location

Might be good to add labels

itop_design

  <dictionaries>
    <dictionary id="EN US" _delta="must_exist">
      <entries>
        <entry id="Class:lnkLocationToPerson/Name" _delta="define"><![CDATA[%2$s is a visitor of %1$s]]></entry>
        <entry id="Class:lnkLocationToPerson/Attribute:person_id" _delta="define"><![CDATA[Visitor]]></entry>
        <entry id="Class:lnkLocationToPerson/Attribute:location_id" _delta="define"><![CDATA[Location]]></entry>
        <entry id="Class:lnkLocationToPerson/Attribute:accreditation" _delta="define"><![CDATA[Accreditation]]></entry>
        <entry id="Class:lnkLocationToPerson/Attribute:accreditation+" _delta="define"><![CDATA[Accreditation level]]></entry>
        <entry id="Class:lnkLocationToPerson/Attribute:last_visit" _delta="define"><![CDATA[Last visit]]></entry>
        <entry id="Class:lnkLocationToPerson/Attribute:last_visit+" _delta="define"><![CDATA[Date when this visitor came for the last time to this site.
Empty if they never came.]]></entry>
      </entries>
    </dictionary>
  </dictionaries>

With the above definition for the XML tag naming, you get a friendlyname for the link which is not very user friendly. On the other hand, to see it you have to explicitly run an OQL query

SELECT lnkLocationToPerson

The result:

To workaround this, you can use the automatic _friendlyname field created for each external key.

itop_design / classes

    <class id="lnkLocationToPerson">
      <properties>
        <naming>
          <attributes>
            <attribute id="location_id_friendlyname"/>
            <attribute id="person_id_friendlyname"/>
          </attributes>
        </naming>

If you just want to keep a single link between a given Person and a given Location, then you must create a Uniqueness Rule to prevent such duplicate entry to occur

itop-design / classes

    <class id="lnkLocationToPerson" _delta="must_exist">  
      <properties>
        <uniqueness_rules>
          <!-- Using "no_duplicate" for the "id" handle the message automatically -->
          <rule id="no_duplicate" _delta="define">
            <attributes>
              <attribute id="person_id"/>
              <attribute id="location_id"/>
            </attributes>
            <filter/>
            <disabled>false</disabled>
            <is_blocking>true</is_blocking>
          </rule>
        </uniqueness_rules>
      </properties>
    </class>

Maybe you want to limit the locations on which a Person can be authorized to those of his own organization.

• You must get those organization as externalFields within the Link so you can use them to filter,
• and set a dependency on the external keys (and not the ExternalField as this does not work!)
• Of course the filter must be defined on both external keys

itop_design / classes

    <class id="lnkLocationToPerson" _delta="must_exist">        
      <fields>
        <field id="location_id" xsi:type="AttributeExternalKey" _delta="must_exist">
          <filter _delta="define">SELECT Location WHERE org_id = person_org_id</filter>
          <dependencies _delta="define><attribute id="person_id"/><dependencies>
        </field>
        <field id="person_id" xsi:type="AttributeExternalKey" _delta="must_exist">
          <filter _delta="define">SELECT Person WHERE org_id = location_org_id</filter>
          <dependencies _delta="define><attribute id="location_id"/><dependencies>
        </field> 
        <field id="location_org_id" xsi:type="AttributeExternalField" _delta="define">
          <extkey_attcode>location_id</extkey_attcode>
          <target_attcode>org_id</target_attcode>
        </field>
        <field id="person_org_id" xsi:type="AttributeExternalField" _delta="define">
          <extkey_attcode>person_id</extkey_attcode>
          <target_attcode>org_id</target_attcode>
        </field>

Let's assume we want to see, the Authorized locations of a Person, from the details of the Person

1. declare an AttributeLinkedSetIndirect field and name it with the remote class name in lowercase, here location follow by s_list
2. Add it to the details presentation, if you want to see it

itop_design / classes / class@Person

      <fields>
        <field id="locations_list" xsi:type="AttributeLinkedSetIndirect" _delta="define">
          <ext_key_to_remote>location_id</ext_key_to_remote>
          <ext_key_to_me>person_id</ext_key_to_me>
          <linked_class>lnkLocationToPerson</linked_class>
          <count_min>0</count_min>
          <count_max>0</count_max>
          <duplicates>false</duplicates>
        </field>
      </fields>
      <presentation>
        <details>
          <items>
            <item id="locations_list" _delta="define">
              <rank>200</rank>
            </item>
          </items>
        </details>
      </presentation>

Same question, you may or may not want to see the “Authorized visitors” as a separate tab in the details of the Location. Assuming you want the declaration is similar:

itop_design / classes / class@Location

      <fields>
        <field id="persons_list" xsi:type="AttributeLinkedSetIndirect" _delta="define">
          <ext_key_to_remote>person_id</ext_key_to_remote>
          <ext_key_to_me>location_id</ext_key_to_me>
          <linked_class>lnkLocationToPerson</linked_class>
          <count_min>0</count_min>
          <count_max>0</count_max>
          <duplicates>false</duplicates>
        </field>
      </fields>
      <presentation>
        <details>
          <items>
            <item id="persons_list" _delta="define">
              <rank>200</rank>
            </item>
          </items>
        </details>
      </presentation>

itop_design

  <dictionaries>
    <dictionary id="EN US" _delta="must_exist">
      <entries>
        <entry id="Class:Location/Attribute:persons_list" _delta="define"><![CDATA[Visitors]]></entry>
        <entry id="Class:Location/Attribute:persons_list+" _delta="define"><![CDATA[List of Persons authorized to visit this location]]></entry>
        <entry id="Class:Person/Attribute:locations_list" _delta="define"><![CDATA[Authorized locations]]></entry>
        <entry id="Class:Person/Attribute:locations_list+" _delta="define"><![CDATA[List of locations that this Person is authorized to visit]]></entry>
      </entries>
    </dictionary>
  </dictionaries>

From iTop 3.1, the + label is displayed as tooltip

You may want to filter the objects which are proposed when adding locations from the edition screen of a Person (and vice-versa)
also there is a XML filter tag for this purpose, it still does not work in 3.1.0, so you must use a PrefillSearchForm method, to preset the search criteria, here the organization

class:location

public function PrefillSearchForm(&$aContextParam)
{
    if ($aContextParam['dest_class'] == 'Person') {
        $aContextParam['filter']->AddCondition('org_id', $this->Get('org_id'));
    }
}

You must do it twice, on Person class when searching for Location and on Location class when searching for Person.

What happen if I create a three objects relationship?

What if I allow its edition from the 3 directions, what if there are dependencies between 2 of them, one being a sub-object of the other one (linked with a one-to-many relationship)

Different cases:

• Adding the Organization level, so a Person could visit different Customers Locations
  • If link can be added from the Location, the Organization must be automatically set and be read-only, is it possible, not sure?
  • If Link can be added from the Person, location_id should be limited to those of the same org_id so filtered and dependent of the org_id field
  • Maybe the Link should not be editable from the Organization, to avoid replacing an access by another one, thus deleting the first one without understanding the consequences (It happened on iTop DeliveryModel on previous iTop versions, to advanced iTop users, because a three legs link was editable from a wrong side)

With an iTop 3.1.0 or higher, you can display a many-to-many relationship, using a widget similar to the TagSet and EnumSet.

You must modify its tag display_style in XML to set it to property, the default value if not set is tab. Here I used _delta=“force” because I suppose that I don't know if the tag exists or was omitted when that field was first declared.

itop_design / classes / class@Person / fields

        <field id="locations_list" _delta="must_exist">
          <display_style _delta="force">property</display_style>
        </field>

Result in edition	and in read mode