Request a topic or
contact an Arke consultant
404-812-3123
Microsoft Dynamics CRM 4.0

Arke Systems Blog

Useful technical and business information straight from Arke.

About the author

Author Name is someone.
E-mail me Send mail

Recent comments

Archive

Authors

Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© Copyright 2018

Microsoft Dynamics CRM and Editing data in Excel

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.


Posted by David Eison on Tuesday, June 1, 2010 1:22 PM
Permalink | Comments (0) | Post RSSRSS comment feed

Microsoft Dynamics 4.0 and SQL Updates

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.


Posted by David Eison on Tuesday, June 1, 2010 1:15 PM
Permalink | Comments (0) | Post RSSRSS comment feed

Microsoft Dynamics CRM 4.0 and SQL selects

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?)


Posted by David Eison on Tuesday, June 1, 2010 1:13 PM
Permalink | Comments (0) | Post RSSRSS comment feed

Dynamics CRM 4: Fixing broken entities

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:

Problem XML

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.

Problem Form

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.


Posted by David Eison on Thursday, May 27, 2010 3:47 PM
Permalink | Comments (0) | Post RSSRSS comment feed

The new CRM 4 SDK is out

Just an FYI to anyone not aware, the new CRM 4 SDK has been released.

This one includes full support for LINQ, more support for development in CRM Online, and other goodies. There's also a whole new microsoft.xrm folder with samples and walkthroughs!

The Microsoft Dynamics SDK documentation website hasn't been updated yet, but I'm sure it will soon.


Posted by Wayne Walton on Thursday, May 6, 2010 4:40 PM
Permalink | Comments (0) | Post RSSRSS comment feed

Syncing Arbitrary Contacts from CRM to Outlook

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.

 


Posted by Wayne Walton on Thursday, April 29, 2010 5:26 PM
Permalink | Comments (0) | Post RSSRSS comment feed

Raw users in CRM

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.


Posted by David Eison on Thursday, April 22, 2010 4:11 PM
Permalink | Comments (0) | Post RSSRSS comment feed

CRM Custom Workflows and “This workflow includes an invalid reference”

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.)


Posted by David Eison on Saturday, April 17, 2010 1:37 AM
Permalink | Comments (0) | Post RSSRSS comment feed

Dynamics CRM 5 CTP3 announced!

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.


Posted by Wayne Walton on Thursday, March 25, 2010 9:54 PM
Permalink | Comments (0) | Post RSSRSS comment feed

Microsoft Dynamics CRM 4.0 Update Rollup 4 released

Why is seems like just yestarday we were posting about Update Rollup 3, and here comes Rollup 4!

 

You can find the KB article here: http://support.microsoft.com/kb/968176 

The actual files are here: http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=0ddf8e83-5d9c-4fe7-9ae6-f2713a024071#filelist  

Don't forget the updated help files!

One quick addendum, make sure you clear your Internet Explorer cache after installing on both the server and the client side.


Posted by Wayne Walton on Monday, May 11, 2009 2:16 PM
Permalink | Comments (0) | Post RSSRSS comment feed