Oracle CRM On Demand Documentation Library: Adding Many-to-Many Associations Between Different Object Types

Oracle CRM On Demand Desktop Customization Guide > Adding One-to-Many and Many-to-Many Associations Between Different Object Types >

Adding Many-to-Many Associations Between Different Object Types

A many-to-many (M:M) association type describes the association between two different object types when an object of each type can have many related objects of another type.

Before You Begin

Review the chapter on customization in Oracle CRM On Demand Desktop Administration Guide.

To add an association to Microsoft Outlook you must start by defining two objects that make up the association. Because associations are not available as standalone types in Oracle CRM On Demand, Oracle CRM On Demand Desktop adds them to Microsoft Outlook by querying the subtypes and then joining them to one object. For example, if you want to add an association between Account and CustomObject types, then you make two queries, one that returns all objects of CustomObject type under Account type, while the second returns all objects of type Account under Custom Object type. Data sets returned by these two queries are then joined as a single query, which is an Account.CustomObject.Association type in Microsoft Outlook. To define settings for these requests, several auxiliary types are defined in the od_meta_info.xml file. However, in the connector_configuration.xml and the od_basic_mapping.xml files, only one association type is defined, which is shown in this topic. The following procedure adds a many-to-many association between an Account object and a CustomObject object.

In this procedure, you update the following metadata files:

od_meta_info.xml
od_basic_mapping.xml
connector_configuration.xml
business_logic.js
views.xml
data_sources.xml
business_logic.js
forms_12.xml

For information on the contents of these files, see About the Metadata Files Updated During Customization.

Adding a many-to-many association between different object types

Define the association and auxiliary types in the od_meta_info.xml file:

Add the object types that make up the association by adding the following XML code that defines two objects in the od_meta_info.xml. For brevity, these type definitions contain only the required fields and a name field.

Add the object types that are used to compose queries in this example: Account.CustomObject and CustomObject.Account.

Adding the Account.CustomObject and CustomObject.Account object types is the same as adding other object types, except that these object type definitions include the following two fields with special fields:

The Id field for the first object in the association.

For the Account.CustomObject object, the Id field for the first object is AccountId. For the CustomObject.Account object, the Id field for the first object is CustomObjectId.

The Id field for the second object in the association.

For the Account.CustomObject object, the Id field for the second object is CustomObjectid and for the CustomObject.Account object, the Id field for the second object is AccountId.

The Id field for the first object in the association must always include the attribute values described in the following table.


Attribute Name	Attribute Value	Attribute Description
IsRefObjId	yes	Indicates to Oracle CRM On Demand Desktop that this field references a real object in the data model. NOTE: Always set this value to yes.
RefObjTypeId	For Account.CustomObject: Account For CustomObjectAccount: CustomObject	The type identifier of the first object that makes up an association.
RefObjIsParent	yes	Indicates to Oracle CRM On Demand Desktop that the referenced object is the parent object. NOTE: Always set this value to yes.

The Id field for the second object must include the attribute IsPrimaryKey with the value set to yes.

The following XML code defines the two objects that are used to compose requests in the od_meta_info.xml file. For the sake of brevity, these type definitions contain only the required fields and the name fields:

Add the intersection object types that are translated into Microsoft Outlook association types in this example: Account.CustomObject.Association and CustomObject.Account.Association.

Adding the Account.CustomObject.Association and CustomObject.Account.Association object types is the same as adding other object types, except that these object type definitions must include the following fields:

The association Id field
The association ModId field
The Id field for the first object in the association

For the Account.CustomObject.Association object, the Id field for the first object is AccountId. For the CustomObject.Account.Association object, the Id field for the first object is CustomObjectId.

The Id field for the second object in the association.

For the CustomObject.Account.Association object, the Id field for the second object is CustomObjectid. For the CustomObject.Account object, the Id field for the second object is AccountId.

The Id field for the first object in the association must always include the attribute values described in the following table.


Attribute Name	Attribute Value	Attribute Description
IsRefObjId	yes	Indicates to Oracle CRM On Demand Desktop that this field references a real object in the data model. NOTE: Always set the attribute name value to yes.
RefObjTypeId	For Account.CustomObject.Association: Account For CustomObject.Account.Association: CustomObject	The type identifier of the first object that makes up an association.
RefObjIsParent	yes	Indicates to Oracle CRM On Demand Desktop that the referenced object is the parent object. NOTE: Always set the attribute name value to yes.
ActualFldName	Id	The Id field name of the object type specified in the RefObjTypeId attribute. Typically the value is Id.
RefExposedToUI	true	Indicates to Oracle CRM On Demand Desktop that it must query the referenced object.
ActualObjTypeId	For Account.CustomObject.Association: Account For CustomObject.Account.Association: CustomObject	The type Id of the first object that makes up an association.
IsReadonly	yes	Indicates to Oracle CRM On Demand Desktop that the field is read only.
IsRequired	yes	Indicates to Oracle CRM On Demand Desktop that the field is required.
IsFake	yes	Indicates to Oracle CRM On Demand Desktop that the field is not used.

The Id field for the second object in the association must always include the attributes described in the following table.


Attribute Name	Attribute Value	Attribute Description
IsRefObjId	yes	Indicates to Oracle CRM On Demand Desktop that this field references a real object in the data model. NOTE: Always set the attribute name value to yes.
RefObjTypeId	For Account.CustomObject.Association: Account.CustomObject For CustomObject.Account.Association: CustomObject.Account	The type Id of the first auxiliary object that makes up an association from Step b.
RefObjIsParent	yes	Indicates to Oracle CRM On Demand Desktop that the referenced object is the parent object. NOTE: Always set the attribute name value to yes.
ActualFldName	For Account.CustomObject.Association: AccountId For CustomObject.Account.Association: CustomObjectId	The name of the id field for the first object in association from Step b.
RefExposedToUI	true	Indicates to Oracle CRM On Demand Desktop that it must query the referenced object.
ActualObjTypeId	For Account.CustomObject.Association: Account.CustomObject For CustomObject.Account.Association: CustomObject.Account	The type Id of the first auxiliary object that makes up an association from Step b.
IsReadonly	yes	Indicates to Oracle CRM On Demand Desktop that the field is read only.
IsRequired	yes	Indicates to Oracle CRM On Demand Desktop that the field is required.
IsFake	yes	Indicates to Oracle CRM On Demand Desktop that the field is not used.

The ModId field in the association object's definition must also include the IsFake attribute with the value set to yes. The Id field in the association object's definition must typically include the IsCompositeId attribute with the value set to yes, and the IsFake attribute with the value set to yes.

The following is the XML code that defines the two objects that are used to compose requests in the od_meta_info.xml file:

Add the intersection object definition to the od_basic_mapping.xml file by adding the code for the <type> element between the <database><types>...</types></database> elements in the following sample code:

<type id="Account.CustomObject.Association" icon=" type_image:Generic:16">
<field id ="AccountId">
<type>
<simple type="binary"/>
</type>
</field>
<field id ="CustomObjectId">
<type>
<foreign_key>
<type_id>CustomObjectId</type_id>
</foreign_key>
</type>
</field>
<field id ="LeftStatus">
<type>
<simple type="string"/>
</type>
</field>
<field id ="RightStatus">
<type>
<simple type="string"/>
</type>
</field>
</type>

The <type> element has the attributes described in the following table.


Attribute Name	Attribute Value	Attribute Description
id	Account.CustomObject. Association	The unique ID of this object type in Oracle CRM On Demand Desktop. Set this attribute to the same value specified in the TypeId attribute of the <object> element for the object in the od_meta_info.xml file.
icon	type_image:Generic:16	Defines the icon that is displayed for this type of object, for example, on a form Caption, in Outlook views, and so on. The value of this attribute is the resource key of an image, for example, a PNG file, 16 x 16 pixels, to be used and defined in any Oracle CRM On Demand Desktop resource file.

You must define the fields of the new object. For more information, see Adding Custom Fields to Oracle CRM On Demand Desktop.

This example adds the following fields to the Microsoft Outlook data model:

AccountId
CustomObjectId
The example also adds the following service fields (fields used by Oracle CRM On Demand Desktop internally that are not synchronized with Oracle CRM On Demand):
LeftStatus
RightStatus
These fields must be added to each child object and are processed by Oracle CRM On Demand Desktop.

Depending on whether the object to which you are adding an ID field for an association is stored in Microsoft Outlook storage or DB FACADE storage, the syntax of the <field> element is going to be one of the following:
If the CustomObject is stored in DB FACADE storage, as in this example, then use the following XML code:

<type>
<foreign_key>
<type_id>CustomObjectId</type_id>
</foreign_key>
</type>

If the CustomObject is stored in Microsoft Outlook storage, then use the following XML code:

For more information on Microsoft Outlook and DB FACADE storage, see About Microsoft Outlook and DB FACADE Storage.

Update the connector_configuration.xml with the new intersection object definition.

The changes made to the od_meta_info.xml and od_basic_mapping.xml files describe only the new object in a data structure, in either Microsoft Outlook or on a server. However, to synchronize items of this object type between Oracle CRM On Demand and Outlook, you must add the new object definition by adding the following structure to the <types> element in the connection_configuration.xml file:

For this specific example, use the following sample XML code:

<type id="Account.CustomObject.Association">
<view label="Account.CustomObject.Association" label_plural="Account CustomObjects" small_icon="type_image:Generic:16" normal_icon="type_image:Generic:24" large_icon="type_image:Generic:48" suppress_sync_ui="true"></view>
<synchronizer name_format=":[:(CustomObjectName) :]" threshold="20">
<links>
<link required="true" owned="true">AccountId</link>
<link required="true" owned="true">CustomObjectId</link>
</links>
<natural_keys>
<natural_key>
<field>AccountId</field>
<field>CustomObjectId</field>
</natural_key>
</natural_keys>
</synchronizer>
</type>

In this example, note the following:

The <type> element has a single attribute defined as in the following table.


Attribute Name	Attribute Value	Attribute Description
id	Account.CustomObject. Association	The unique ID of this object type in Oracle CRM On Demand Desktop. Set the ID value to the same value specified previously for the id attribute of the <type> element in od_basic_mapping.xml in Step a.

The <view> element defines some of the UI settings. It contains the attributes in the following table that you must define.


Attribute Name	Attribute Value	Attribute Description
label	Account.CustomObject. Association	The label that Oracle CRM On Demand Desktop uses for this type of object in the user interface. In particular, for synchronization issues, Oracle CRM On Demand Desktop uses this attribute to resolve duplicates and conflicts and to synchronize confirmation tabs of the Control Panel.
label_plural	Account CustomObjects	A plural label that Oracle CRM On Demand Desktop uses for this type of object in the user interface. In particular, for synchronization issues, Oracle CRM On Demand Desktop uses this attribute to resolve duplicates and conflicts and to synchronize confirmation tabs of the Control Panel.
small_icon	type_image:Generic:16	The definition of the icon to display for this type of object on a Control Panel, as a child element icon on a filter tree, in various lists of the Control Panel. The value of this attribute is a resource key of an image file, for example, a PNG file, 16 x 16 pixels, to be used and defined in any Oracle CRM On Demand Desktop resource file.
normal_icon	type_image:Generic:24	Definition of the icon displayed for this type of object on a Control Panel, as a top level element icon on a filter tree. The value of this attribute is a resource key of an image file, for example, a PNG file, 24 x 24 pixels, to be used and defined in any Oracle CRM On Demand Desktop resource file.
large_icon	type_image:Generic:48	Defines the icon to be displayed for this type of object, for example, the icon on a Sync dialog box. The value of this attribute is a resource key of an image file, for example, a PNG file, 40 x 40 pixels, to be used and defined in any Oracle CRM On Demand Desktop resource file.
suppress_sync_ui	true	Setting to a value of true hides this object from the Filters Panel.

The <synchronizer> element defines the sync settings for the object, such as link fields, natural keys, how this object is displayed if it appears in any list of the Control Panel, and so on. This example defines the attributes in the following table.


Attribute Name	Attribute Value	Attribute Description
name_format	:[:(CustomObjectName):]	This attribute's value defines a mask that is used to build this object identifier in any list of a Control Panel, such as, sync issues, duplicates, and so on. In this case, the value of the CustomObjectName field is displayed.
threshold	20	The minimum number of objects that Oracle CRM On Demand Desktop can delete at once without a user response. Oracle CRM On Demand Desktop synchronizes the deletions of less than 20 custom objects from Microsoft Outlook to Oracle CRM On Demand. If it does not delete objects automatically, it displays a confirmation dialog box for the user to confirm the deletion of more than 20 custom objects. This feature protects a user from deleting many custom objects accidentally. If a user deletes objects accidentally, then those objects are deleted from Oracle CRM On Demand after synchronization.

The <synchronizer> element also contains <links> and <natural_keys> elements.

The <links> element defines a set of link fields of the object. For this example, the link fields are AccountId and CustomObjectId. Oracle CRM On Demand Desktop requires knowledge of these fields to build an objects dependency tree during synchronization. Oracle CRM On Demand Desktop also must be able to determine which fields contain Ids of other objects, so that their values are converted properly from the Oracle CRM On Demand Id format to the Microsoft Outlook Id format and from the Microsoft Outlook Id format to the Oracle CRM On Demand.
The <natural_keys> element contains a set of field sets (<natural_key>) that are used to identify duplicated records during synchronization. A record is identified as a duplicate if there are two matching records in Oracle CRM On Demand and Microsoft Outlook, in which the match is defined by the fields specified in natural keys. In this example, the fields are AccountId and CustomObjectId.

Add the selectors' options in the SalesBook view. SalesBook is a dialog box with a built-in view control that displays a list of available objects for linking; with the SalesBook dialog, a user can pick any record and associate selected object:
1. Add selectors options to the business_logic.js file.
  The options in this example specify the basic settings for the Salesbook dialog box, which displays the CustomObject1 records. In the business_logic.js file, locate the //selectors options comment, and add your selectors options below the comment, as follows:
scheme.objects.get_object("CustomObject4").selectors_options = {
"source": {
"caption": "obj_customobj4_plural",
"view_id": "custom_objects4:salesbook",
"search_by": ["Name"],
"online": {
"like_template": "*{keyword}*"
}
}
};

where:
- custom_objects4:salesbook is the identifier of the child object's Salesbook view.
- Name is the field defined for the CustomObject4 object to be used for search purposes.
1. Define the SalesBook view and form in the views.xml file by locating the <res_root><data_view> elements and placing the following XML code sample in the <data_view> element:
<view id="view_od_custom_objects4">
<image_list>
<res_id type="normal">type_image:Generic:16</res_id>
</image_list>
<columns>
<column width="305" sort="asc">
<heading type="string">head_name</heading>
<field>Name</field>
</column>
</columns>
</view>

where:
- <column> is a column element of the grid
- <heading> contains The resource Id of the column heading
- <field> indicates the object's field name taken from the od_basic_mapping.xml file
  You can set the sort attribute to asc (ascending) or desc (descending).
  
  The code to display Associated custom_objects1 view on the Account form is as follows:
<view id="custom_objects1:form_view">
<image_list>
<res_id type="normal">type_image:Generic:16</res_id>
</image_list>
<columns>
<column width="305" sort="asc">
<heading type="string">head_name</heading>
<field>
<poly_join link="CustomObjectId">
<if_type name="Custom Object 1">
<simple>Name</simple>
</if_type>
</poly_join>
</field>
</column>
</columns>
</view>
Add the data source.
If the right object of the association is stored within Microsoft Outlook storage, then you must add the data source by adding the following in the <joint> element to the data_sources.xml file, as follows:

<data_source name="ActionJointCustomObject1" type_id="Account.Custom Object 1" resolve_rate="1"/>
Add the JavaScript code that defines the new many-to-many association by adding the following code to the business_logic.js file:
var account_co1 =
add_mvg_link("Account", "Custom Object 1", "CustomObject1Id", "AccountId",
                 "Account.Custom Object 1", "AccountId", "CustomObjectId",
                 "LeftStatus", "RightStatus",
                 null, null,
                 true, true, true, true);
account_co1.mvg1.dialog_template_params = {
"dialog_caption": "#obj_account_co1_plural",
"autocomplete_display_format": ":[:(Name):]",
"associations_view_caption": "#head_associations_co1",
"associations_view_id": "custom_objects1:mvg",
"primary_selector_display_format": ":[:(Name):]"
}
account_co1.mvg2.dialog_template_params = {
"dialog_caption": "#obj_contact_account_plural",
"autocomplete_display_format": ":[:(Name) :]",
"associations_view_caption": "#head_associations_accounts",
"associations_view_id": "accounts:mvg",
"primary_selector_display_format": ":[:(Name) :]"
}

The add_mvg_link function accepts the following parameters, where:
- left_type is the type of the first linked object, for example, Account.
- right_type is the type of the second linked object, for example, CustomObject1.
- left_obj_primary is the field of the Account object. In the example, this field contains the primary identifier for the CustomObject1 object. If no primary is required, then set this argument to null.
- right_obj_primary is the field of the CustomObject1 object. This field contains the primary identifier for the Account object. If no primary is required, then set this argument to null.
- assoc_type is the identifier of the association type described in the od_meta_info.xml file and the od_basic_mapping.xml file.
- left_link is the field of the association object that contains the identifer of the Account.
- right_link is the field of the association object that contains the identifier of the CustomObject1.
- left_assoc_status is the field of the association that contains the status of the Account.
- right_assoc_status is the field of the association that contains the status of the CustomObject1.
- Null parameter (unused).
- Null parameter (unused).
- left_primary_refresh_required is used to update the object identifier for the primary field of the Account that you specify in the left_obj_primary attribute, if the primary object is changed, and if the left_primary_refresh_required attribute is true.
  If you use the <writer class="link_fields" ...> element in the od_basic_mapping.xml file for this field, then set the left_primary_refresh_required attribute to true.
- right_primary_refresh_required is used to update the object identifier for the primary field of the CustomObject1 that you specify in the right_obj_primary attribute, if the primary object is changed, and if the right_primary_refresh_required attribute is true.
  If you use the <writer class="link_fields"...> element in the od_basic_mapping.xml file for this field, then set the right_primary_refresh_required attribute to true.
- assoc_left_link_refresh_required is used to update the AccountId field of the association that you specify in the left_link attribute, if the Account object is changed.
  If you use the <writer class="link_fields" ...> element in the od_basic_mapping.xml file for this field, then set the assoc_left_link_refresh_required attribute to true.
- assoc_right_link_refresh_required is used toupdate the CustomObjectId field of the association that you specify in the right_link attribute, if the CustomObject1 object is changed.
  If you use the <writer class="link_fields" ...> element in the od_basic_mapping.xml file for this field, then set the assoc_right_link_refresh_required attribute to true.
  
  To add a new association without primary links, see the description of the left_obj_primary and right_obj_primary arguments in this topic. The LeftStatus and RightStatus fields contain the status objects, such as unsaved, deleted, and so on.
Extend the user interface (UI) as follows:
1. Add the MVG (multi-value group) control.
  An MVG control displays an association with other objects. This association can be a many-to-many association, or a many-to-one association.
  
  In the example, the CustomObject1 form contains the MVG control with the primary selector and button to expose many-to-many associations with Account to the UI. The advantage of this control is that it occupies less space.
  - Extend the Custom Object 1 form in the forms_12.xml file with the following XML code sample:
<cell>
<stack layout="horz" spacing="5">
<cell>
<mvg_primary_selector id="AccountToCustomObject1">
<source type="Account.Custom Object 1" left_id="CustomObjectId" item_value="AccountId" display_format=":[:(AccountName):]"></source>
<field>AccountId</field>
</mvg_primary_selector>
</cell>
<cell size="21">
<button id="btn_mvgAccount">
<text>...</text>
</button>
</cell>
</stack>
</cell>
- Extend the Custom Object 1 form handler in forms.js with the following JavaScript code:
  
  If Custom Object 1 has a primary Account, then use the following
  
  register_mvg_dialog(ctx, "Account", "AccountToCustomObject1", "btn_mvgAccount");
  
  If Custom Object 1 has no primary Account, then use the following
  
  register_mvg_dialog(ctx, "Account", "AccountToCustomObject1", "btn_mvgAccount", { "tag": "mvg", "dialog_id": "mvg_dialog_no_primary"});
1. Add the View control with buttons.
  In this example, the Account form contains a View control with buttons to expose many-to-many associations from CustomObject1 to the UI. The advantage of this control is that it displays all associated objects directly on a form.
  - Extend the Account form in the forms_12.xml file with the following XML code:

<cell size="13">
<stack layout="horz">
<cell size="105">
<static id="0x20050"><text>#view_od_co1</text>
</static></cell>
<cell>
<stack layout="vert">
<cell size="7"></cell>
<cell size="1">
<edge id="0x20051"/></cell>
</stack>
</cell>
</stack>
</cell>
<cell size="22">
<stack layout="horz" padding="5">
<cell size="150">
<static id="0x20050"><text>#lbl_primary_co1</text>
</static>
</cell>
<cell>
<mvg_primary_selector id="AccountToCustomObject1">
<source type="Account.Custom Object 1" left_id="AccountId" item_value="CustomObjectId" display_format=":[:(Name):]"></source>
<field>CustomObject1Id</field>
</mvg_primary_selector>
</cell>
</stack>
</cell>
<cell>
<stack layout="vert" spacing="5">
<cell>
<stack layout="horz" padding="5">
<cell>
<data_view id="co1_view" tab_order="56">
<source type="auto" name="Account.Custom Object 1"></source>
<view id="custom_objects1:form_view"></view>
<restriction>
<group link="and">
<binary field="AccountId" condition="eq">
<value type="variable">id()</value>
</binary>
<binary field="RightStatus" condition="ne">
<value type="string">deleted</value>
</binary>
<binary field="LeftStatus" condition="ne">
<value type="string">unsaved</value>
</binary>
</group>
</restriction>
</data_view>
</cell>
</stack>
</cell>
<cell size="22" attraction="far">
<stack layout="horz" spacing="5" padding="5">
<cell size="140" attraction="far">
<button id="btn_remove_co1"><text>#lbl_remove_co1</text>
</button>
</cell>
<cell size="140" attraction="far">
<button id="btn_co1"><text>#lbl_add_co1</text>
</button>
</cell>
</stack>
</cell>
</stack>
</cell>


If the object on the right side of the association is stored within Microsoft Outlook storage, then locate the following XML element:

<source type="auto" name="Account.Custom Object 1"></source>

Replace the element with the following element:

<source type="auto" name="ActionJointCustomObject1"></source>

The Microsoft Outlook view control XML attributes are described in the following table.

Attribute

Values

name (for <source> element)

A valid object type.

id (for <view> element)

The view identifier from the views.xml file.
- Extend the Account form handler in the forms.js file with the following JavaScript code:
register_view_control_with_button(ctx, "Custom Object 1", "co1_view", null, {"delete_action": "remove_association", "btn_remove_association": ctx.form.btn_remove_co1});

Oracle CRM On Demand Desktop Customization Guide, Version 5.2 Revision A		Copyright © 2018, Oracle and/or its affiliates. All rights reserved. Legal Notices.