Get The CRM 2011 Timezone Settings For Current User With JavaScript

Sometimes it's useful to know the current user's timezone settings, for instance DateTime results in ODATA will be UTC so you may need to know how that should appear to the user. There are a bunch of blogs/user group posts about working out the summertime offset which doesn't make sense in my testing, if anybody can explain it and give examples I'd be grateful. Also a word of caution I've also noticed that if the Windows and CRM timezone settings are different it can also cause you some fun and games. Anyways enough of that here's the JavaScript to get the user's timezone settings:

GetUserTimeZoneDetails = function ()
{
 
 var ODataPath = Xrm.Page.context.getServerUrl() + "/XRMServices/2011/OrganizationData.svc";
 var filter = "/UserSettingsSet(guid'" + Xrm.Page.context.getUserId() + "')";
 
 var userSettingReq = new XMLHttpRequest();
 userSettingReq.open('GET', ODataPath + filter, false);
 userSettingReq.setRequestHeader("Accept", "application/json");
 userSettingReq.setRequestHeader("Content-Type", "application/json; charset=utf-8");
 userSettingReq.send(null);

 var userSetting = this.JSON.parse(userSettingReq.responseText).d;
 var timeZoneBias = 0;
 try
 {
  timeZoneBias = userSetting.TimeZoneBias;
 }
 catch (e)
 {
  timeZoneBias = 0;
 }

 var timeZoneDayLightBias = 0;

 try
 {
  timeZoneDayLightBias = userSetting.TimeZoneDaylightBias;
 }
 catch (e)
 {
  timeZoneDayLightBias = 0;
 }
 
 return [timeZoneBias, timeZoneDayLightBias];
}

The function returns an array containing two elements the timezone bias and timezone daylight bias. The timezonebias is the local time zone adjustment for the user. System calculated based on the time zone selected. The timezonedaylightbias is the local time zone daylight adjustment for the user. System calculated based on the time zone selected. The SDK has details about other user settings that can be retrieved in the same way as above, see User Settings MetaData

Using The CRM 2011 SOAP Endpoint

When we retrieve data from CRM 2011 through JavaScript more often than not we use the REST endpoints but there are times when you may decide that the SOAP endpoint is going to be a better tool for the job. This article isn't going to dictate which endpoint you use, there is an article on MSDN which outlines the benefits of each: Use Web Service Data in Web Resources (REST and SOAP Endpoint).

If you want to get at CRM data through the SOAP endpoint then here some steps that will get you to where you want to be:

1. The SDK has a code sample called SOAPLogger, after downloading the SDK navigate to samplecode\cs\client\soaplogger\ and open the project in Visual Studio

2. We now need to add some code that we'll use to generate the SOAP envelope. In this sample it will retrieve a contact record returning the first name, last name, and email address. Open up SOAPLogger.cs and add the following to the Run Method:

Guid contactId = new Guid("[A Valid GUID goes here]");

RetrieveRequest retrieveContact = new RetrieveRequest();
retrieveContact.ColumnSet = new ColumnSet(new string[] { "firstname", "lastname", "emailaddress1" });
retrieveContact.Target = new EntityReference("contact", contactId);

slos.Execute(retrieveContact);

3. Go ahead and run the project, a command prompt appears and you'll need to provide the connection information to the CRM server you want to connect to. After the code has executed you're prompted to "Press (Enter) to exit."

4. A text file has been created in the execution folder called "output.txt", open this with notepad.

5. We are interested in the HTTP REQUEST section, specifically the soap envelope, which we'll copy (see highlighted text in screenshot):

6. Now we're ready to write some JavaScript, the request variable is going to contain the SOAP envelope that we copied in the previous step:

var serverUrl = "[CRM Server Url]";

var xmlhttp = new XMLHttpRequest();
xmlhttp.open("POST", serverUrl() + "/XRMServices/2011/Organization.svc/web", false);
xmlhttp.setRequestHeader("Accept", "application/xml, text/xml, */*");
xmlhttp.setRequestHeader("Content-Type", "text/xml; charset=utf-8");
xmlhttp.setRequestHeader("SOAPAction", "http://schemas.microsoft.com/xrm/2011/Contracts/Services/IOrganizationService/Execute");

var request = "";
request += '';
request += '  ';
request += '    ';
request += '      ';
request += '        ';
request += '          ';
request += '            Target';
request += '            ';
request += '              e183560d-2680-e211-bc33-005056ba9f90';
request += '              contact';
request += '              ';
request += '            ';
request += '          ';
request += '          ';
request += '            ColumnSet';
request += '            ';
request += '              false';
request += '              ';
request += '                firstname';
request += '                lastname';
request += '                emailaddress1';
request += '              ';
request += '            ';
request += '          ';
request += '        ';
request += '        ';
request += '        Retrieve';
request += '      ';
request += '    ';
request += '  ';
request += '';

xmlhttp.send(request);

7. xmlhttp.responseXML.xml is going to contain the response XML from the web service call. To make like easier I've written a method that you can pass the XML to, along with the field name, which will return the value (e.g. GetResponse(xmlhttp.responseXML.xml, "firstname"):

GetResponse = function (responseXML, attributename)
{
    var returnValue = "";

    try
    {
  var xmlDoc = new ActiveXObject("Microsoft.XMLDOM");
        xmlDoc.async = "false";
        xmlDoc.loadXML(responseXML);
        
        var x = xmlDoc.getElementsByTagName("a:KeyValuePairOfstringanyType");

        for (i = 0; i < x.length; i++)
        {
            if (x[i].childNodes[0].text == attributename)
            {
                returnValue = x[i].childNodes[1].text;
                break;
            }
        }
    }
    catch (e)
    {
        returnValue = "";
    }
    return returnValue;
}

SQL Joins

SQL Joins

I'm always at a loss when asked to explain the differences between the different types of (SQL) joins. I know what I need to use but in my muddled mind can't ever explain why. This post should help to explain the differences for those that don't know and help remind those that do.

Joins are either inner or outer, outer can be full outer, left outer, or right outer. Let’s start with a couple of tables to help visualise what each join will do.

Table A User Id Name 1 Adrian Williams 2 Steve Sidwell 3 James Harper 4 Jamie Cureton 5 Nicky Forster 6 Barry Hunter 7 Jimmy Quinn

Table B User Id Login 3 Jharper 4 Jcureton 5 nforster 6 Bhunter 7 Jquinn 8 Scoppell 9 brodgers

Assume that all queries are asked to return user id (from Table A), name, and login.

Each of the joins will return the following

Left Outer Join

The left outer join will return all records from Table A and values from Table B where they exist, otherwise a null is returned.

TSQL:

select

      tablea.userid as userid,

      name,

      login

from

      TableA

left outer join

      TableB on TableA.userid = TableB.userid

Results:

User Id	Name	Login
1	Adrian Williams	NULL
2	Steve Sidwell	NULL
3	James Harper	jharper
4	Jamie Cureton	jcureton
5	Nicky Forster	nforster
6	Barry Hunter	bhunter
7	Jimmy Quinn	jquinn

Right Outer Join

The right outer join will return all records from Table B and values from Table A where they exist, otherwise a null is returned.

TSQL:

select

      tablea.userid as userid,

      name,

      login

from

      TableA

right outer join

      TableB on TableA.userid = TableB.userid

Results:

User Id	Name	Login
3	James Harper	jharper
4	Jamie Cureton	jcureton
5	Nicky Forster	nforster
6	Barry Hunter	bhunter
7	Jimmy Quinn	jquinn
NULL	NULL	scoppell
NULL	NULL	brodgers

Full Outer Join

The full outer join will return all records from Table A and Table B, where there isn’t a corresponding record in null will be returned.

TSQL:

select

      tablea.userid as userid,

      name,

      login

from

      TableA

Full outer join

      TableB on TableA.userid = TableB.userid

Results:

User Id	Name	Login
1	Adrian Williams	NULL
2	Steve Sidwell	NULL
3	James Harper	jharper
4	Jamie Cureton	jcureton
5	Nicky Forster	nforster
6	Barry Hunter	bhunter
7	Jimmy Quinn	jquinn
NULL	NULL	scoppell
NULL	NULL	brodgers

Inner Join

The inner join will only return data from rows where there is a match in both Table A and Table B.

TSQL:

select

      tablea.userid as userid,

      name,

      login

from

      TableA

inner join

      TableB on TableA.userid = TableB.userid

Results:

User Id	Name	Login
3	James Harper	jharper
4	Jamie Cureton	jcureton
5	Nicky Forster	nforster
6	Barry Hunter	bhunter
7	Jimmy Quinn	jquinn

Checking Which User Account is Accessing a File

It's often useful to find out which account is accessing a file, e.g. a CRM Plugin in writing to a log file and you need to determine which Windows account is trying to access the file (in this example it will be the user that the CRM Asynchronous service is running under).

Here's a quick how-to to easily find out this information:

1. Download Process Monitor from Technet, unzip it, and run Procmon.exe
2. Select File and uncheck Capture Events
3. Select Edit --> Clear Display
3. Select Filter --> Filter
4. Under "Display entries matching these conditions:" select the following:

• Path
• is
• [path and filename of the file to monitor]

5. Click Add

6. Click OK

7. Select File and check Capture Events

8. Run the code that will attempt to access the file

9. Go back to Process Monitor and select File and uncheck Capture Events

10. In the events list there will be rows showing the filename, double click on one these

11. From the Event Properties window select the Process tab

12.The user that attempted to access the file is shown.

Entities Not Appearing in Settings After Import

In CRM 4.0 and now 2011 I've had an issue when I import customisations/solutions which have entities that are setup to appear in the Settings area. The problem is they just don't appear!

This has been one of those niggles that I've always been aware of but never done anything about, put simply it's always been quicker to just tick the settings box, save, and publish. Well this morning something has changed and I did a bit of digging.

Apparently you need to include the SiteMap in your export, even if it's not been changed, and hey presto the entities appear in settings as they should.

CRM 4 Entity Type Codes

The following table gives the entity type codes for each of the vanilla entities in Dynamics CRM 4:

Entity name	Value
Account	1
AccountLeads	16
ActivityMimeAttachment	1001
ActivityParty	135
ActivityPartyRollupByAccount	4603
ActivityPartyRollupByContact	4604
ActivityPointer	4200
Annotation	5
AnnualFiscalCalendar	2000
Appointment	4201
AttributeMap	4601
BulkImport	4407
BulkOperation	4406
BulkOperationLog	4405
BusinessUnit	10
BusinessUnitMap	6
BusinessUnitNewsArticle	132
Calendar	4003
CalendarRule	4004
Campaign	4400
CampaignActivity	4402
CampaignActivityItem	4404
CampaignItem	4403
CampaignResponse	4401
Commitment	4215
Competitor	123
CompetitorAddress	1004
CompetitorProduct	1006
CompetitorSalesLiterature	26
ConstraintBasedGroup	4007
Contact	2
ContactInvoices	17
ContactLeads	22
ContactOrders	19
ContactQuotes	18
Contract	1010
ContractDetail	1011
ContractTemplate	2011
CustomerAddress	1071
CustomerOpportunityRole	4503
CustomerRelationship	4502
Discount	1013
DiscountType	1080
DocumentIndex	126
Email	4202
EntityMap	4600
Equipment	4000
Fax	4204
FilterTemplate	30
FixedMonthlyFiscalCalendar	2004
ImportConfig	4408
Incident	112
IncidentResolution	4206
IntegrationStatus	3000
InternalAddress	1003
Invoice	1090
InvoiceDetail	1091
KbArticle	127
KbArticleComment	1082
KbArticleTemplate	1016
Lead	4
LeadAddress	1017
LeadCompetitors	24
LeadProduct	27
Letter	4207
License	2027
List	4300
ListMember	4301
MonthlyFiscalCalendar	2003
Opportunity	3
OpportunityClose	4208
OpportunityCompetitors	25
OpportunityProduct	1083
OrderClose	4209
Organization	1019
OrganizationMap	7
OrganizationUI	1021
PhoneCall	4210
PluginType	4602
PriceLevel	1022
PrincipalObjectAccess	11
Privilege	1023
PrivilegeObjectTypeCodes	31
Product	1024
ProductAssociation	1025
ProductPriceLevel	1026
ProductSalesLiterature	21
ProductSubstitute	1028
QuarterlyFiscalCalendar	2002
Queue	2020
QueueItem	2029
Quote	1084
QuoteClose	4211
QuoteDetail	1085
RelationshipRole	4500
RelationshipRoleMap	4501
Resource	4002
ResourceGroup	4005
ResourceSpec	4006
Role	1036
RolePrivileges	12
RoleTemplate	1037
RoleTemplatePrivileges	28
SalesLiterature	1038
SalesLiteratureItem	1070
SalesOrder	1088
SalesOrderDetail	1089
SavedQuery	1039
SemiAnnualFiscalCalendar	2001
Service	4001
ServiceAppointment	4214
ServiceContractContacts	20
Site	4009
StatusMap	1075
StringMap	1043
Subject	129
Subscription	29
SystemUser	8
SystemUserLicenses	13
SystemUserPrincipals	14
SystemUserRoles	15
Task	4212
Team	9
TeamMembership	23
Template	2010
Territory	2013
UnresolvedAddress	2012
UoM	1055
UoMSchedule	1056
UserFiscalCalendar	1086
UserQuery	4230
UserSettings	150