Relationship Mapping (CRM 4.0)

/in

You might have noticed, how certain fields are automatically populated sometimes, whenever a new form is opened from the CRM UI.
Try creating a new Case from the Contract form. The customer and contract fields are automatically populated on the new Case form. Similar to various other entities too.
CRM provides an excellent functionality but often quite ignored by automatically mapping attributes from one entity to another within a 1: many relationships.

Open the CRM window and goto  Settings  -> Customization  ->  Contract  -> 1:N Relationships  -> contract_cases  -> Mappings

map

There are 2 fields already mapped between the contract and case. It is this mapping which defines the fields to be populated when a new Case form is opened from its Parent Form (Contract)
You can use the ‘New’ button to define additional mappings. Mapping is not only related to system attributes, you can define them for custom attributes as well.

Note: Mappings can be defined only for entities within a 1: many relationships

Mapping can be defined for most Entities: Account to Contact, Opportunity to Quote, Quote to Order, Order to Invoice, Contract to Case etc.
So if you try converting a Quote to an Order, you will notice that all fields of the Quote and QuoteDetail are automatically pushed into the SalesOrder and SalesOrderDetail

However consider a scenario, where you need custom fields on QuoteDetail to be transferred to the custom Fields available on the OrderDetail. If you have a look at the QuoteDetail relationships, you will find that no relationship exists between the QuoteDetail and OrderDetail. So how do you go about mapping the custom fields?

Well, CRM definitely has a relationship between the QuoteDetail and OrderDetail Entities, however for some reason unknown, its hidden from the UI.

Here’s an UNSUPPORTED technique to unearth these relationships. So for the above example, we need to transfer QuoteDetail custom attribute values to the  OrderDetail Entity.

Within SQL, run the below query:
Select * from entitymapbase where targetentityname = ‘orderdetail’
This query will return 3 items, pick the row with the ‘SourceEntityName’ column value of  “quotedetail”.
Copy the GUID value available in the EntityMapId column for this row.

Paste the GUID at the end of the below URL
http://servername:port/orgname/Tools/SystemCustomization/Relationships/Mappings/mappingList.aspx?mappingId=

That opens up the secret mapping page ……
You can use this to map your custom fields as well.

CRM Picklist and the StringMap Table (CRM 4.0)

/in
If you have a look at the CRM base tables (eg: AccountBase) in MSCRM, you would notice that all columns of Picklist datatype seem to store Integer values. So from where exactly does CRM get the actual text value to be displayed on the UI?

Well, this comes from a not so well known table called ‘StringMap’. MSCRM stores all related option values for any picklist in this table.

Here’s the Structure of the StringMap Table:
Column Name
Description
StringMapId
Primary ID of the record
ObjectTypeCode
Object Type Code of the entity
AttributeName
Schema Name of the picklist attribute
AttributeValue
Integer value of the picklist option. This value is actually stored in the base tables.
LangId
Language Code
OrganizationId
Organization ID
Value
text value displayed within the picklist on the CRM UI
DisplayOrder
Order of the values in the picklist on the CRM UI
VersionNumber
Last updated Timestamp (used during synchronization process)

Modifying the StringMap table to add new values is not recommended and can cause undesired results. In fact, CRM does not support modifying any of the CRM tables directly. All updates are to be made via the CRM UI or Web Services.

CRM seems to store picklist values in 2 tables:
1. StringMap
2. Metadataschema.AttributePicklistValue(This seems to be used as a temporary table)

Whenever an option value is added/edited/deleted for any picklist and saved from the UI, CRM updates theMETADATASCHEMA.AttributePicklistValue table. Upon publish of the Form the values are pushed into the StringMap table based on the values in the above table.
Hence directly updating the StringMap table via SQL with new values won’t suffice, since CRM will replace the values in DB with the new values when the Entity is published via the UI.
There are 2 possible ways to edit picklist values:
1. The Metadata Service (The only SUPPORTED way to add/edit/delete picklist values)
2. Modifying the Entity XML. (This requires editing the XML file for an entity)(UNSUPPORTED)Newly created values for any system picklist will have a value of 200000 or greater. This is to maintain backward compatibility during upgrades from CRM 3.0. This ensures that up to 199999 values for any picklist from CRM 3.0 will be successfully transferred during an upgrade to CRM 4.0
Note: Newly created values for any custom picklist would, however, start from 1.

Below is an example of both the options:

Option 1:

//Example for creating an option via the Metadata Service:
CrmLabel crmLabel = new CrmLabel();
LocLabel englishLabel = new LocLabel();
CrmNumber langCode = new CrmNumber();
// Set lang code as English
langCode.Value = 1033;
englishLabel.LanguageCode = langCode;
englishLabel.Label = “New Value”;
crmLabel.LocLabels = new LocLabel[] { englishLabel };
//Create the Insert Request
InsertOptionValueRequest insertRequest = new InsertOptionValueRequest();
//Set the Entity Name
insertRequest.EntityLogicalName = EntityName.contact.ToString();
//Set the Attribute Name
insertRequest.AttributeLogicalName = “customertypecode”;
insertRequest.Label = crmLabel;
insertRequest.Value = new CrmNumber();
insertRequest.Value.Value = 200000;
//Execute the Insert Request
InsertOptionValueResponse insertResponse = (InsertOptionValueResponse)metadataService.Execute(insertRequest);
//Example for deleting an option via the Metadata Service:
//Create the Delete Request
DeleteOptionValueRequest deleteRequest = new DeleteOptionValueRequest();
//Set the Entity Name
deleteRequest.EntityLogicalName = EntityName.contact.ToString();
//Set the Attribute Name
deleteRequest.AttributeLogicalName = “customertypecode”;
//Declare the Attribute Value
deleteRequest.Value = 200000;
//Execute the Delete Request
DeleteOptionValueResponse deleteResponse = (DeleteOptionValueResponse)metadataService.Execute(deleteRequest);

Option 2:
Export the required entity. (MSCRM-Settings-Customization-Export Entities) OR use the ExportXmlRequest and ExportXmlResponse if you want to perform the export via web services.
Open the file within an XML Editor.
Search for the attribute schemaname you want to update.
You should find something similar to the text given below
                <options>
<option value=”1″>
<labels>
<label description=”Buyer” languagecode=”1033″ />
</labels>
</option>
<option value=”2″>
                  <labels>
<label description=”Seller” languagecode=’1033″ />
</labels>
</option>
</options>
An additional option node would need to be created for every new option you want to add.
Eg :
<option value=”200000″>
<labels>
<label description=”Re-Seller” languagecode=”1033″ />
</labels>
</option>You can use the ImportXmlRequest/ ImportXmlResponse and PublishXmlRequest/ PublishXmlResponse classes to publish the xml back into CRM.

MYOB CRM Integration

/in
One-Way Integration between MSCRM 4.0 & MYOB Premier 11.0
There are products available such as ‘AccountLink’, which provides integration with these 2 applications.
However, these applications provide integration only between a pre-defined set of entities.
Eg: Quote in MSCRM goes to Quote in MYOB.
and although it was possible to customize the integration to a certain extent, it was not possible to change the predefined set of entities.
i.e: The application would not support integration from the ‘Quote’ Entity in CRM to ‘Orders’ in MYOB or ‘Service Activities’ in CRM to ‘Orders’ in MYOB.
Recently one of our clients had a requirement wherein they needed the Service Activities created in CRM to be generated as Orders in MYOB.
The existing products did not support this kind of integration and hence we created a custom integration engine which would fulfill the above requirement.
Another major advantage being, the windows application built does not use any 3rd party components/applications to carry out the integration, thereby providing more value for money.
Since MYOB does not provide an SDK to interact with, the first step was to identify the tables affected when an Order was created in MYOB.
Since MYOB has a flat file structure, one can use the “Link Table” option provided in Microsoft Access to view the Table structure in MYOB DB.
The most table’s in MYOB has an associated table prefixed with “Import” which can be used to import data into MYOB through an external application.
Eg :
The Order details in MYOB is stored in the table  called ‘Sales Service’
So we could use the ‘Import Sales Service’ table to push data into the ‘Sales Service’ table.
Once the required table is identified in MYOB, data can be moved into MYOB using normal SQL queries with the ODBC drivers provided by MYOB.

Since our requirement was a one-time activity every day, a windows service was written which would fetch data from MSCRM using MSCRM Web Services and create the appropriate SQL queries to push data into the MYOB tables.

Regards

Denny Aloor

CRM Technical Consultant, IOTAP

Including Custom JavaScript Files & Adding Events on Fly in MSCRM 3.0

/in
Including custom JavaScript files in MSCRM 3.0
While working with MS CRM, you will find a lot of client-side coding in JavaScript. MS CRM has exposed onLoad, onSave & onChanged event. Once you add javascript, you could save the form followed by publishing it. The whole process is time-consuming and again publishing entity calls for resetting IIS, which it may not be permissible.
 In a development scenario, we always use common methods, which are placed in common files. In most programming languages, there is a concept of “INCLUDE files”. Includes files are referenced at the start of your program & Interpreter or the Compiler will add that to your program.
I.e. Includes files are easy to maintain /modify.
How can we use it in MS CRM?
Say we need to calculate total amount in the below example, i.e. Add Freight amt and Total Tax.
Total Tax
Create the .js file:
Open Notepad and copy the following Javascript into it
function CalculateTotal()
{
    crmForm.all.TotalAmout.DataValue =  crmForm.all.TotalTax.DataValue  + crmForm.all.FreightAmount.DataValue  
}
Save this file as CommonFunction.js and place it in a directory below your MSCRM web directory called myscripts.
Reference your .js file:
In the OnLoad event of your Form, include the following code:
var script = document.createElement(’script’);
script.language = ‘javascript’;
script.src = ‘/myscripts/CommonFunction.js’;
document.getElementsByTagName(’head’)[0].appendChild(script);
Now you can call CalculateTotal () wherever you want;
Using Included Functions in the Form OnLoad Event:
It is possible to run into a timing issue because the include file has not yet finished appending to the CRM Form. This is really only a problem if you need to access a function from your include file from within the OnLoad event. 
To work around with this issue, we need to wait for the script’s state to change to “loaded” before any functions in the include file can be accessed:??
var script = document.createElement(’script’);
script.language = ‘javascript’;
script.src = ‘/myscripts/CommonFunction.js’;
document.getElementsByTagName(’head’)[0].appendChild(script);
var f = function()
{
if (event.srcElement.readyState == “loaded”)
CalculateTotal() // some function from MyFunctions.js
}
script.attachEvent(”onreadystatechange”, f);
 Points To Ponder:
This solution will not work if you have the CRM 3.0 Outlook Laptop Client deployed. This is because the Laptop client utilizes a local Web server and if physically disconnected from the main CRM web server, the JavaScript file will be unavailable and a script error on the page will result.
Few more javascript.
Adding events on the fly.
 
In Javascript, there are quite a few events for each field. Unfortunately, MS CRM only exposed onchange event of a field.
No problem with few lines of code you can attached event on the fly. The only place where you can add your code is onLoad() event of the entity and here is the way to do it:
crmForm.all.fieldname.eventname = function() {
//event handling code goes here
}
A commonly used event is onclick. Surpisingly it fires whenever you click the field:
crmForm.all.fieldname.onclick = function() {
alert(“fieldname clicked!”);
}
Accordingly, we can use more events as we require.