Sometimes you need to know how User ID works “under the hood.”

We see ‘systemuserid’ all over the database identifying a user … but how does it decide which systemuserid to use?

CRM appears to figure out which user is logging in by querying the MSCRM_CONFIG database systemuserauthentication table looking for a value in the authinfo column that matches the objectsid for the user from activedirectory (SID, *not* GUID).

You can get the user SID (and GUID) from active directory by registering a dll from the windows server 2003 resource toolkit ( http://download.microsoft.com/download/8/e/c/8ec3a7d8-05b4-440a-a71e-ca3ee25fe057/rktools.exe ) on a 32-bit OS (I don't know how to make it work on 64 bit; docs say 64 bit is not supported) - install toolkit, copy acctinfo.dll to c:\windows\system32, "regsvr32 acctinfo.dll" , then browse to user in AD ( start/run/dsa.msc ) and look at the 'additional account info' tab (doesn't show up when user is found using find, unfortunately).

Then you can dig up the entry for the sid in systemuserauthentication, which gives you a userid, which you can find in systemuserorganization, which gives you a crmuserid, which you can go find as systemuser in the [orgname]_mscrm database (which is NOT the same as systemuser in the mscrm_config database).

If you have a problem with a mismapping between the two databases (for example, somebody mistakenly doing a backup of one but not the other), there are two easy ways to fix the problem:

The first thing to try is to edit the user the in the normal CRM interface and change the AD login name to a not-yet-used-by-CRM value, save the entity, then change it back to the value you want to be used.  This often cleans up a broken mapping.

The next thing to try is to use the crm deployment manager to export and import the organization; during the import there is a user mapping step where you can have CRM set up these relationships properly for you.

Finally, filtered views have their own method of identifying the user – they use [fn_FindUserGuid] which checks for a GUID on context_info(), and if not found, they query SystemUserBase where DomainName = SUSER_SNAME() .

I recommend against trying to operate on this data at the raw sql level, system user id is simply referenced in too many places to reliably get everything.  You would end up with orphaned records at best, for example due to all the ‘createdby’ and ‘modifiedby’ references on every entity.  So ‘select’ is fine to learn things, but ‘delete’ or ‘update’ at your own risk and preferably not even then.  Still, if you have to, the AD SID is where to start.

Customers sometimes need a good way to bulk edit data in CRM. 

One solution is to export the data, adjust it outside of CRM in a tool like Excel, then re-import it.

You can get an ms crm import tool from http://mscrmimport.codeplex.com/ (read about it at  http://blogs.msdn.com/b/crm/archive/2008/01/25/data-manipulation-tool.aspx )

A more powerful and user friendly Excel editing tool is EBAX: http://www.mscrmexcel.com/

In general I think EBAX is a good compromise of raw editing-in-Excel power and CRM's use-our-web-services restrictions, so I recommend it particularly to clients who have a few users who need some powerful or bulk access. 

The only consideration I’m aware of for a bulk editing tool in a deployment is that it’s important to train both the power users and developers that fields which were disabled via Javascript aren’t really disabled – users should avoid editing a custom calculated total field, and developers should avoid expecting a user to be unable to edit a custom calculated total field simply because it was disabled on the web form.

Customers often want to know why they aren’t supposed to update their data via raw SQL and are instead directed to use the CRM web forms or the SDK.

These are the reasons I am aware of:

1) Various important parts of CRM rely on C# code that will only run properly if everything is accessed through the web services (plugins and workflows)

2) Microsoft wants to be able to change the inner workings in future upgrades, and if everyone developed relying on particular columns being in particular tables upgrades would become impossible.

3) It's a complicated system and it's easy to miss a detail, such as the isdeleted column or state codes or timezone conversions.  These details can be abstracted away for read only access when querying against the filtered views, which automatically removed columns with the deleted flag and convert utc timezones to local timezone and check that the user querying has permission to view the record, etc.  But you can't readily do such abstraction for updating.

4) There is a security model in place that gives each user permissions to do certain things based on roles granted to them, and if you rely on working as a priviledged user in the database you might accidentally circumvent that. 

5) Some calculated fields like totals are 'not valid for update', but this is only enforced at the web services level (for example, totals can’t be directly updated because any value you might try to submit will be overwritten by a plugin calculating it).

6) There is an added level of complexity at the raw database level - base vs extensionbase tables, for example - that is more difficult to work with.

7) One wrong value can lead to system errors.  For example, deleting a row from the extensionbase but not the base table, or setting a picklist to an impossible value, can cause spurious errors when later trying to work with the information through the normal interface.

8) Schema changes in particular are tracked with various metadata that you really don't want to get into and you can break everything in very difficult to recover ways by manually changing column definitions.

Customers also want to know why they are supposed to use ‘Filtered’ views to query their CRM data directly from the database.

These are the reasons I’m aware of for using filtered views:

The filtered views do five things for you:

1) They are ‘fully supported’, meaning that they shouldn’t break on upgrades. 

2) Convert times from utc to user’s local timezone (UTC times are still available in the filtered views, but the default time column is changed to local time)

3) Filter out records flagged as to be deleted.

4) Filter out records the current user doesn't have permission to see.

5) Clean up and hide a bunch of implementation details, like mapping state codes from ints like 0 to strings like 'Active'.

The only drawback is that filtered views may slow down queries as they join in a lot of tables to present their data, and you will want to keep track of timezone conversion challenges (if a user entered a time in eastern daylight time, it’s stored in UTC/Greenwich Mean, then the querying user is in Australia… what day did the event happen on?)

The reason entities sometimes break after importing in CRM is that CRM only exports relationships between two custom entities if both entities are included in an export. I assume this is to avoid breaking on import into a system that doesn’t have the associated custom entity.  You can avoid the problem if you are able to export all related entities together, but sometimes you can’t do that because one is not ready to be deployed for example.

I saw the problem show up when trying to export all forms with a Javascript export tool (both so I could search our javascript easily with Agent Ransack and so I could check in any recent changes to keep a history):

“"\n 0x80040216\n An unexpected error occurred.\n Platform\n"”

Trace log showed:

System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.NullReferenceException: Object reference not set to an instance of an object.

at Microsoft.Crm.ObjectModel.OrganizationUIService.LabelLoaderPublished.LoadMetadataLabel(Int32 entityType, String attributeName, ExecutionContext context)

Exporting all customizations manually via the export UI resulted in the same exception as was in trace log:

At that point I did a manual binary search of exporting entities to narrow down which entity was the problem.  (It might also show up in trace log; in my case, no such luck.)

Going back to customize entities and looking at the form for the offending entity, there were empty spaces where links to two particular custom entities should be.

Goal now is to delete them from the form and the entity so I can recreate them. http://support.microsoft.com/kb/947096 has the steps:

Open up the problem attributes and note their names.

Create temporary attributes with the same names. This turns the previously broken references on the form into valid references to a text attribute.

Then delete the attributes from the form and any views & publish. This gets references to the attributes off the list of published attributes.

Then delete the attributes and save and publish again to remove any final traces of them.

Then I can manually recreate the relationships and re-add them to the form and views.

This is a trick I learned at Convergence this year that I really like.  For those of you that weren’t there, I figured I’d document this neat little enhancement to CRM and Outlook.

The end goal here is to allow CRM users to sync up arbitrary contacts to their Outlook, regardless of who may own the Contact.  This can be very useful when you have multiple people work one deal, or when personal and business contacts might overlap.

The first thing to do is create an Entity called Outlook Contact.  Make sure you make it User owned, and the only place it should reside is the Workplace.  Also, it does not need Notes or Activities.  It’s a very simple Entity, and the only thing you’re going to add to it is an N:1 lookup to Contact (make it Parental, there’s no reason to keep the Outlook Contact Entity if the Contact itself is deleted).  Then simplify the form to the point that the only things on it are the Owner and the Outlook Contact to Sync.  Make sure nothing else is Searchable, then publish.  (Don’t forget to set permissions on all roles for Outlook Contact!  By default, roles should be set as User across the board)

Next, we need to make the Workflow.  Make it against a Contact, and make it On Demand.  Like the image below:

The actual creation of the Outlook Contact Entity is very simple, as pictured below:

Once the Workflow is complete and you have Published it, go to your Outlook Client and find the “Modify Local Data Groups” button like in the image below (this part is a per-user setting, so every person that wants to be able to leverage this trick will have to follow these steps):

Once in there, you will see a Data Group called “My Contacts”.  I turned it off in my settings, but that’s a personal preference.  If you aren’t familiar, the My Contacts Data Group is the one that syncs all Contacts you own in CRM.

You then will want to make a new Data Group and call it “Outlook Contacts Synced”.  Set it up to look like the image below and Save it.

Once that is all set up, go to the CRM Contacts list and run the Workflow on any Contacts you want in your Outlook Contacts list.  This will work even on Contacts you don’t own, as long as you have Append To permissions on all Contacts.  To see immediate results, click “Synchronize to CRM” in the CRM menu in Outlook!  Otherwise, they will show up on the normal Outlook/CRM sync schedule.

That’s it; you now have any CRM Contact you want in Outlook (and by extension, your mobile device)!  To remove the Contact, just Delete the Outlook Contact Entity in CRM, and it will be removed on next Sync.

CRM decides which user account to use by finding the logged in user’s ObjectSID in the MSCRM database systemuserauthentication table.

You can get your ObjectSID out of Active Directory ( dsa.msc ) if you have a 32 bit server by registering a dll from the windows server 2003 resource toolkit ( http://download.microsoft.com/download/8/e/c/8ec3a7d8-05b4-440a-a71e-ca3ee25fe057/rktools.exe ) (I don't know how to make it work on 64 bit; docs say 64 bit is not supported) - install toolkit, copy acctinfo.dll to c:\windows\system32 (copy step may not be necessary), "regsvr32 acctinfo.dll".  Then browse to user in AD ( start/run/dsa.msc ) and look at the 'additional account info' tab (doesn't show up when user is found using find, unfortunately).

Now that you have an ObjectSID, that can be found in the authinfo column of systemuserauthentication , which gives you a userid.  You can use that userid to query systemuser and get a default organization id; You can use that organizationid and userid to query systemuserorganizations , which gives you a crmuserid.  Finally this crmuserid guid is the systemuser id for your actual organization’s CRM database.  Note that the two different systemuser tables are unrelated and use different guids; one is for the crm config db, and one is for your organization crm db.

When using a custom workflow dll in CRM, make sure to register it with the same GUIDs on production as you do on your dev CRM server.  You can do this easily by using export/import from the plugin registration tool.

When you make a workflow, anything that uses a step from the custom workflow will end up referring to it by GUID.  So if the guids don’t match for the dll, you can’t import/export workflows between the environments. And it’s not an easy fix by editing guids in the customization export file, because workflows are serialized inside this file.

If you find out about the problem early, you can unregister and reregister the dll with the right GUID.  If you find out about the problem late, after someone has already developed workflows separately in both environments, you may need to delete anything that uses data from the custom dll and recreate those workflow steps.

It shows up as an error message of “This workflow contains errors and cannot be published”, and “This workflow includes an invalid reference”.  (The invalid reference message is a generic there-is-a-guid-that-doesnt-match error, it could also be referring to a record like a system user.)

For those of you like me that have been slavering at the chance to get your hands on CRM5 to see everything they've done with it, CTP3 has been announced!  This isn't a public preview yet (we're still months out, there), but it's a start!

When I get a copy, I'll post about some of the best features from a back-end perspective.  I'm especially interested in the application isolation they've been touting for a while now.  To get a taste of what's been announced so far, hit up this announcement from PDC last year.

So I've been looking at a good way to get maps of locations direct from Dynamics CRM.  There are lots of neat solutions out there that embed a Google or Live map directly into a tab, but I didn't want the extra tab.  I wanted something simpler.  So I made this.  It's the simplest solution I could think of that effectively solve the issue.  It literally took more time to think of than to implement.

First,  go to customize the Entity that you want to have the link on.  I chose Account and Address for my implementation, but any entity with an address will do.  So first create an Attribute called Google Maps and put it on the form.  Make sure you give the Attribute a format of URL and increase the maximum length to 500 characters.  This will make it clickable form the UI and ensure you don't cut off the end of the address.

Now go to the Form and go to Form Properties. Open the OnLoad event and paste this JavaScript in:

crmForm.all.new_googlemaps.DataValue = "http://maps.google.com/maps?q=" + crmForm.all.line1.DataValue + "+" + crmForm.all.city.DataValue + "+" + crmForm.all.stateorprovince.DataValue + "+" + crmForm.all.postalcode.DataValue;

A few important things here:   "http://maps.google.com/maps?q=" is the beginning of the Google Maps query string. You need it.  The attributes after that, such as line1, city, etc. are specific to the Address entity.  If you want to do this in a different Entity, you'll have to find out what the specific name of the address, city, state and zip code fields are. also, these instructions are US-oriented.  For international addresses, you'll have to add whatever fields are relevant to get Google Maps to give you correct addresses.

In the end, you'll have a clickable field that will open up a map to the address in CRM in a new browser window. Very convenient, and also compatible with mobile CRM solutions!