I was previously wondering where I could find the list of "sets" that are used to fetch data using the ODATA endpoint. It seems the answer was rediculously easy.
To make a short story even shorter, what you need to do is to surf to the ODATA endpoint and the CRM will present the complete list to you.
Rickard Norström
Developer at CRM-Konsulterna
www.crmkonsulterna.se
måndag 22 oktober 2012
fredag 19 oktober 2012
Sales Pipeline report
The whole concept Sales Pipeline was a bit fuzzy for me not very long ago, especially in CRM. There are no obvious places that the sales pipeline stages get set when you look at the Opportunity form. Unfortunately it gets worse when you start looking at the Sales Pipeline Report
I found it quite strange that the sales pipeline stage was a string field on the opportunity form, well not ON the form per se but among the fields in the entity, that is supposed to be set by a workflow for example. I didn't see the logic with this field. Why would you want a string field for set names, I found it more reasonable to have a multiple choice field where the step names would be pre determined. It would be obvious that it doesn't matter at all.
The funny thing with this field is that in CRM 4.0, the only thing it really does is showing the pipeline stage on the lower right corner of the opportunity form, that's it. It has no other relevance to anything at all. In CRM 2011 that field is the default horizontal category of the Sales Pipeline chart, which makes this even more confusing since the Sales Pipeline report is available in CRM 2011 too.
Now, what is the "problem" with the Sales Pipeline report? The problem is that the Sales Pipeline report doesn't use the stepname field at all. The Sales Pipeline report pipeline stepnames is taken from the phase names of the workflow that has run on the opportunity. Further, the sales process that you filter the report on is the name of the workflow that has run on the opportunity. But it gets even a bit more complicated, to count the workflow as a sales process, the workflow needs to have phases in it.
I will try to make this a bit more comprehensive. The Sales Pipeline report has a filter option that is called "Sales process". This is actually any workflow that has phases in it and is run on any opportunity. Further, the pipeline step names are the names of these phases in the workflow. That doesn't seem too complicated.
There is of course another thing that makes this a bit of a problem. The sales pipeline report doesn't sort out opportunities, it sorts out the workflows that has been active. This means that if you have run a workflow several times on an opportunity, for example if the workflows exits and you restart it manually. When you do this a new instance of that workflow is stored in the CRM database, which is completely understandable since you want to be able to see what workflows has been active on an object.
The Sales Pipeline report sees this and in the scope of the report every workflow that has been active is a valid record that should be shown on the report. The result is that you have an opportunity that has a calculated value of €100000 and you have run the sales workflow five times on that opportunity for whatever reason, the shown value on the report is €500000, which will make the opportunity value of the company very good. It will however mess up the total result of the company since even if they win the opportunity the value will only be a fifth of the calculated value of what have been reported in the pipeline.
This is not much of a problem if you are having a sales process that doesn't fork or almost always runs in a straight line and never has to restarts. It does however mess up your reports if you would have a split sales process workflow or if you restart the sales workflow for some reason, in our case we needed to do this to be able to change the sales person responsible of the opportunity so that the new responsible would get tasks created.
I started to sketch up a solution where old workflows would be deleted which would keep the Sales Pipeline report correct but it would delete the workflows so the history would be lost. The final solution to this problem was to set up a paralell workflow which only would keep track of the sales pipeline steps which kept the sales process in a neat single straight flow based on the stepname field giving it the correct phase in the pipeline report.
You can probably edit the pipeline report to match something else than the phase name of the workflow but the data sets for the report is in no way simple so I considered it to consume too much time for this problem.
Rickard Norström
Developer at CRM-Konsulterna
www.crmkonsulterna.se
I found it quite strange that the sales pipeline stage was a string field on the opportunity form, well not ON the form per se but among the fields in the entity, that is supposed to be set by a workflow for example. I didn't see the logic with this field. Why would you want a string field for set names, I found it more reasonable to have a multiple choice field where the step names would be pre determined. It would be obvious that it doesn't matter at all.
The funny thing with this field is that in CRM 4.0, the only thing it really does is showing the pipeline stage on the lower right corner of the opportunity form, that's it. It has no other relevance to anything at all. In CRM 2011 that field is the default horizontal category of the Sales Pipeline chart, which makes this even more confusing since the Sales Pipeline report is available in CRM 2011 too.
Now, what is the "problem" with the Sales Pipeline report? The problem is that the Sales Pipeline report doesn't use the stepname field at all. The Sales Pipeline report pipeline stepnames is taken from the phase names of the workflow that has run on the opportunity. Further, the sales process that you filter the report on is the name of the workflow that has run on the opportunity. But it gets even a bit more complicated, to count the workflow as a sales process, the workflow needs to have phases in it.
I will try to make this a bit more comprehensive. The Sales Pipeline report has a filter option that is called "Sales process". This is actually any workflow that has phases in it and is run on any opportunity. Further, the pipeline step names are the names of these phases in the workflow. That doesn't seem too complicated.
There is of course another thing that makes this a bit of a problem. The sales pipeline report doesn't sort out opportunities, it sorts out the workflows that has been active. This means that if you have run a workflow several times on an opportunity, for example if the workflows exits and you restart it manually. When you do this a new instance of that workflow is stored in the CRM database, which is completely understandable since you want to be able to see what workflows has been active on an object.
The Sales Pipeline report sees this and in the scope of the report every workflow that has been active is a valid record that should be shown on the report. The result is that you have an opportunity that has a calculated value of €100000 and you have run the sales workflow five times on that opportunity for whatever reason, the shown value on the report is €500000, which will make the opportunity value of the company very good. It will however mess up the total result of the company since even if they win the opportunity the value will only be a fifth of the calculated value of what have been reported in the pipeline.
This is not much of a problem if you are having a sales process that doesn't fork or almost always runs in a straight line and never has to restarts. It does however mess up your reports if you would have a split sales process workflow or if you restart the sales workflow for some reason, in our case we needed to do this to be able to change the sales person responsible of the opportunity so that the new responsible would get tasks created.
I started to sketch up a solution where old workflows would be deleted which would keep the Sales Pipeline report correct but it would delete the workflows so the history would be lost. The final solution to this problem was to set up a paralell workflow which only would keep track of the sales pipeline steps which kept the sales process in a neat single straight flow based on the stepname field giving it the correct phase in the pipeline report.
You can probably edit the pipeline report to match something else than the phase name of the workflow but the data sets for the report is in no way simple so I considered it to consume too much time for this problem.
Rickard Norström
Developer at CRM-Konsulterna
www.crmkonsulterna.se
fredag 7 september 2012
More ODATA thoughts
When reading about Dynamics CRM 2011 and ODATA queries you quickly get the idea that it's made for Silverlight and JScripts working from the inside, however I found one very useful application of the ODATA queries.
I was browsing through the trace file of an installation to figure out what wasn't working properly and there are a lot of references to user ids that I wanted to know who they belonged to. This is where the ODATA query came in very handy. I simply, well if you read my previous post simply is perhaps a bit of an exaggeration, put together a query using the ODATA endpoint from the CRM. To query a user by id the query looks something like this: http://<server>:<port>/<org>/XRMServices/2011/OrganizationData.svc/SystemUserSet?$filter=SystemUserId eq (guid'<SystemUserId>')
In real life, it could look something like this: http://crmserver:5555/MyOrg/XRMServices/2011/OrganizationData.svc/SystemUserSet?$filter=SystemUserId eq (guid'1A3B1082-F208-E011-A188-00155D0A8303')
Hope this helps someone!
Rickard Norström
Developer at CRM-Konsulterna
www.crmkonsulterna.se
I was browsing through the trace file of an installation to figure out what wasn't working properly and there are a lot of references to user ids that I wanted to know who they belonged to. This is where the ODATA query came in very handy. I simply, well if you read my previous post simply is perhaps a bit of an exaggeration, put together a query using the ODATA endpoint from the CRM. To query a user by id the query looks something like this: http://<server>:<port>/<org>/XRMServices/2011/OrganizationData.svc/SystemUserSet?$filter=SystemUserId eq (guid'<SystemUserId>')
In real life, it could look something like this: http://crmserver:5555/MyOrg/XRMServices/2011/OrganizationData.svc/SystemUserSet?$filter=SystemUserId eq (guid'1A3B1082-F208-E011-A188-00155D0A8303')
Hope this helps someone!
Rickard Norström
Developer at CRM-Konsulterna
www.crmkonsulterna.se
torsdag 23 augusti 2012
JSON Querys
Fetching data is pretty much what a CRM system is about. Being able to do advanced searches and queries using Javascript is indeed a nice feature, it has however given me some grey hairs.
Prior to Dynamics CRM 2011 you were forced to write the fetch with a lot of XML. With the introduction of CRM 2011 Microsoft introduced the OData protocol to make it easier to fetch the data you wanted from the system.
Even though this change is much welcome it is not without hassle I have entered the world of OData and JSON in CRM. Given the amount of blogs about CRM, the SDK and the Technet I found it quite hard to find how to make a query with the OData protocol making a function that returned data or an object. Most references used the OData protocol to just update a field on the form. The problem I was facing needed a function that just returned a value, like most functions do.
First thing's first. How do you set up the query?
Looking around on the internet gives a lot of answers to this question. I found this blog: http://deepakexploring.wordpress.com/2011/10/20/odata-and-jscript-in-crm-2011/ by Deepak Kumar, which gave me a basic understanding how to set up the query
Looking att this code things started to make sense, The server URL is taken from the form's host and the context's organization. Further the oDataPath here is copied from the OData address from the developrs resources in CRM.
Deepak has chosen to have this example showing how to fetch the Email address of the primary contact. He has taken the primary contact ID from the form to use that as a keyword in the search, still pretty easy to understand.
Then he sets up the request, an XMLHttpRequest, and now things got a bit tricky for me since the similarities to this tutorial ended. The Odata variable declared after the XMLHttpRequest deserves some extra attention.
ContactSet is the declaration that we want to search for one or more contacts. So is there a list of what "sets" that are available? No, I haven't found one yet, if anyone has seen one, please let me know. The funny thing about these "sets" is that they are of course case sensitive. If you would enter "contactset" you would end up in a "500-error". I was searching for users so I started out thinking it would be UserSet since all querys I had found was written with a capital first letter and capital S in set.
As you might imagine, UserSet is wrong, so I tried SystemuserSet, which also was wrong. Considering I had quite the search string, it took some time for me to narrow down where the search went wrong and it started on square on. SystemUserSet was the correct term in this case, which probably is logical if you're used to the syntax which I unfortunately isn't.
After the entity we're searching for comes the goal of the search. If you leave out the $select part, you should get the entire set of fields, however if you want to narrow down your search stack the fields you're interested in separated by commas. You might notice that EMailAddress is written a bit odd. I would have guessed that Email is one word and would probably have gotten an error there as well, in my case I was looking for FullName and the SystemUserId.
Next is the $filter, which is the search criteria, what are you narrowing your search with. If you want one hit, a guid is the main choice since it will give just one result. However, if you don't know the guid of the record you have to use other fields. Notice that if you're searching by id, you need to mark that it's a guid. The different search patterns can be seen on http://msdn.microsoft.com/en-us/library/gg309461.aspx and you can combine search patterns with "and" or "or".
Moving down the code I found the next part with:
This tells us that it's a fetch, it's Odata and the "false" is telling us that the query is synchronous. Had it been a "true" at the end, it would have been an asynchronous retrieve. After that there are two rows setting the header which I haven't dug into much, I simply accepted them. And then there's a function declaration.
This got my attention, but there were other examples of that declaration being there but according to W3School, this was perfectly normal so I accepted it. Last there is a send message ending that code. But there's still the function call that is made by the onreadystatechange where the result is processed and in this case sent out as an alert.
Back to my problem at hand. Putting all this in a function, and having another function hadling the results didn't help me since I wanted something returned from the first function. Since I am very new to the OData protocol I really didn't know how to solve this and most tutorials looked like this, or with another approach using some ajax call.
Finally I found http://community.dynamics.com/product/crm/crmtechnical/b/crminogic/archive /2011/12/13/read-records-synchronously-using-json-in-crm-2011.aspx.
The answer was surprisingly easy, just put all the code from the callback function after the send request and then you can return you data to the calling function. How come there are very few tutorials with this solution.
Now I had my query and I had my results. I just needed to see what I had gotten back from the system. How many records are there? Started looking for answers on the internet. Again it seemed like I was doing somthing that not a lot of people are insterested in. This time it really was as simple as "retrieved.results.length", which I acctually tried when I thought I had the query correct but alas since I had gotten one of the search words wrong, the result was not an array of length zero as one might imagine but not instansiated since I got a 404-error from the search.
The debugging is really a mess. I found it easiest to do the query debugging in a browser and just try how the cases of the keywords should look like
To sum this quite long post in a few bullets
Rickard Norström
Developer at CRM-Konsulterna
www.crmkonsulterna.se
Prior to Dynamics CRM 2011 you were forced to write the fetch with a lot of XML. With the introduction of CRM 2011 Microsoft introduced the OData protocol to make it easier to fetch the data you wanted from the system.
Even though this change is much welcome it is not without hassle I have entered the world of OData and JSON in CRM. Given the amount of blogs about CRM, the SDK and the Technet I found it quite hard to find how to make a query with the OData protocol making a function that returned data or an object. Most references used the OData protocol to just update a field on the form. The problem I was facing needed a function that just returned a value, like most functions do.
First thing's first. How do you set up the query?
Looking around on the internet gives a lot of answers to this question. I found this blog: http://deepakexploring.wordpress.com/2011/10/20/odata-and-jscript-in-crm-2011/ by Deepak Kumar, which gave me a basic understanding how to set up the query
function OnLoad() {
var serverUrl = "http://" + window.location.host + "/" +
Xrm.Page.context.getOrgUniqueName();
var primarycontact = Xrm.Page.getAttribute("primarycontactid").getValue();
var primarycontactid = primarycontact[0].id;
// Creating the Odata Endpoint
var oDataPath = serverUrl + "/XRMServices/2011/OrganizationData.svc";
var retrieveReq = new XMLHttpRequest();
var Odata = oDataPath +
"/ContactSet?$select=EMailAddress1&$filter=ContactId eq guid'" +
primarycontactid + "'";
retrieveReq.open("GET", Odata, false);
retrieveReq.setRequestHeader("Accept", "application/json");
retrieveReq.setRequestHeader("Content-Type", "application/json; charset=utf-8");
retrieveReq.onreadystatechange = function () { retrieveReqCallBack(this); };
retrieveReq.send();
}
function retrieveReqCallBack(retrieveReq) {
if (retrieveReq.readyState == 4 /* complete */) {
var retrieved = this.parent.JSON.parse(retrieveReq.responseText).d;
var EmailAddress = retrieved.results[0].EMailAddress1;
alert(EmailAddress);
}
}
Looking att this code things started to make sense, The server URL is taken from the form's host and the context's organization. Further the oDataPath here is copied from the OData address from the developrs resources in CRM.
Deepak has chosen to have this example showing how to fetch the Email address of the primary contact. He has taken the primary contact ID from the form to use that as a keyword in the search, still pretty easy to understand.
Then he sets up the request, an XMLHttpRequest, and now things got a bit tricky for me since the similarities to this tutorial ended. The Odata variable declared after the XMLHttpRequest deserves some extra attention.
var Odata = oDataPath +
"/ContactSet?$select=EMailAddress1&$filter=ContactId eq guid'" +
primarycontactid + "'";
ContactSet is the declaration that we want to search for one or more contacts. So is there a list of what "sets" that are available? No, I haven't found one yet, if anyone has seen one, please let me know. The funny thing about these "sets" is that they are of course case sensitive. If you would enter "contactset" you would end up in a "500-error". I was searching for users so I started out thinking it would be UserSet since all querys I had found was written with a capital first letter and capital S in set.
As you might imagine, UserSet is wrong, so I tried SystemuserSet, which also was wrong. Considering I had quite the search string, it took some time for me to narrow down where the search went wrong and it started on square on. SystemUserSet was the correct term in this case, which probably is logical if you're used to the syntax which I unfortunately isn't.
After the entity we're searching for comes the goal of the search. If you leave out the $select part, you should get the entire set of fields, however if you want to narrow down your search stack the fields you're interested in separated by commas. You might notice that EMailAddress is written a bit odd. I would have guessed that Email is one word and would probably have gotten an error there as well, in my case I was looking for FullName and the SystemUserId.
Next is the $filter, which is the search criteria, what are you narrowing your search with. If you want one hit, a guid is the main choice since it will give just one result. However, if you don't know the guid of the record you have to use other fields. Notice that if you're searching by id, you need to mark that it's a guid. The different search patterns can be seen on http://msdn.microsoft.com/en-us/library/gg309461.aspx and you can combine search patterns with "and" or "or".
Moving down the code I found the next part with:
retrieveReq.open("GET", Odata, false);
This tells us that it's a fetch, it's Odata and the "false" is telling us that the query is synchronous. Had it been a "true" at the end, it would have been an asynchronous retrieve. After that there are two rows setting the header which I haven't dug into much, I simply accepted them. And then there's a function declaration.
This got my attention, but there were other examples of that declaration being there but according to W3School, this was perfectly normal so I accepted it. Last there is a send message ending that code. But there's still the function call that is made by the onreadystatechange where the result is processed and in this case sent out as an alert.
Back to my problem at hand. Putting all this in a function, and having another function hadling the results didn't help me since I wanted something returned from the first function. Since I am very new to the OData protocol I really didn't know how to solve this and most tutorials looked like this, or with another approach using some ajax call.
Finally I found http://community.dynamics.com/product/crm/crmtechnical/b/crminogic/archive /2011/12/13/read-records-synchronously-using-json-in-crm-2011.aspx.
The answer was surprisingly easy, just put all the code from the callback function after the send request and then you can return you data to the calling function. How come there are very few tutorials with this solution.
Now I had my query and I had my results. I just needed to see what I had gotten back from the system. How many records are there? Started looking for answers on the internet. Again it seemed like I was doing somthing that not a lot of people are insterested in. This time it really was as simple as "retrieved.results.length", which I acctually tried when I thought I had the query correct but alas since I had gotten one of the search words wrong, the result was not an array of length zero as one might imagine but not instansiated since I got a 404-error from the search.
The debugging is really a mess. I found it easiest to do the query debugging in a browser and just try how the cases of the keywords should look like
To sum this quite long post in a few bullets
- Use your browser to test the query
- Build the query part by part, testing all the way, it's very easy to get it wrong
- You don't have to push the results into a fuction of it's own, I know it sounds silly but I got the impression you had to do it that way.
- You can narrow down the results to just a few fields, which I would think improves performance
- If you find a complete list of "sets" from a vanilla CRM, please let me know :)
Rickard Norström
Developer at CRM-Konsulterna
www.crmkonsulterna.se
onsdag 4 juli 2012
Upgrading Dynamics CRM 4 to CRM 2011 challenges
Upgrading software can always be a challenge.Running into unexpected problems with what you thought was supported can make things even worse.
I have recently been working with a Dynamics CRM upgrade from 4.0 to 2011. The CRM is, by my standards, a quite large implementation with clustered front ends and an organization database that is 19GB as a backup file. The project at hand is an in-place upgrade that has a test environment that is a rather good replica of the production environment, that by itself is not always the case.
The CRM was set up with an organization database that had a dash in the name. This had obviously been accepted at install so I didn't think much about it when I did the upgrade of the test environment. Being a large database I imagined the upgrade would take some time so I scheduled it to run over night. The next morning I had a system that was partially upgraded. The front-end server that I did the upgrade on was upgraded and was now a 2011 server. The config database was upgraded and was also of 2011 standard. The organization database however had not been upgraded and was halted with a somewhat strange error message. It said that it could not find the database XX_YY_MSCRM. That was something that was understandable since the database name was XX-YY_MSCRM.
I started looking for solutions on this which did not include changing the database name which it would seem, was the only option I could find. I would like to thank the users of Microsoft's CRM forum for helping me with this.
The solution to this problem was the following
1. Restore the Config database
2. Create a new organization in the Deployment manager
3. Disable the organization I wanted to rename.
4. Delete the organization, this will only remove it from the Config-database
5. In SQL Server Management Studio, rename the database. This step can be a bit tricky if there are connections to the database. I dropped the database an reconected it
6. Import the organization with Deployment Manager.
After these steps I was able to upgrade the system, having restored the front end server as well. The thing that struck me with this in place upgrade was that it only took a bit over one hour to complete while a migration upgrade of a CRM 4.0 organization haven't taken under 2 hours yet. We also upgraded a copy of this organization with the migration upgrade path to create a development platform and that upgrade took more than 4 hours to complete.
When I had succeeded in upgrading this CRM I was a bit curious about whether dashes were allowed in the organization name or not. Reading the implementation guide for CRM 4.0, it clearly states that the organization name can only contain letters, numbers and underscores. This made me wonder how the database organization name was allowed to contain the dash. The installation or import guid in Deployment Manager really should have stoped this from happening. I wonder why it didn't. The next thing I thought about was the error message where it looks as if the program interpreted the dash as an underscore which seems strange. Lastly I wonder why the installation program doesn't check the organization database prior to upgrading the system. What I was "stuck" with was an upgraded server with a config database and organization database that were incompatible. I might have been able to do an import of the CRM 4.0 database if I had renamed it but since I wanted to test the upgrade path of the production server I chose to restore everything.
Rickard Norström
Developer at CRM-Konsulterna
www.crmkonsulterna.se
I have recently been working with a Dynamics CRM upgrade from 4.0 to 2011. The CRM is, by my standards, a quite large implementation with clustered front ends and an organization database that is 19GB as a backup file. The project at hand is an in-place upgrade that has a test environment that is a rather good replica of the production environment, that by itself is not always the case.
The CRM was set up with an organization database that had a dash in the name. This had obviously been accepted at install so I didn't think much about it when I did the upgrade of the test environment. Being a large database I imagined the upgrade would take some time so I scheduled it to run over night. The next morning I had a system that was partially upgraded. The front-end server that I did the upgrade on was upgraded and was now a 2011 server. The config database was upgraded and was also of 2011 standard. The organization database however had not been upgraded and was halted with a somewhat strange error message. It said that it could not find the database XX_YY_MSCRM. That was something that was understandable since the database name was XX-YY_MSCRM.
I started looking for solutions on this which did not include changing the database name which it would seem, was the only option I could find. I would like to thank the users of Microsoft's CRM forum for helping me with this.
The solution to this problem was the following
1. Restore the Config database
2. Create a new organization in the Deployment manager
3. Disable the organization I wanted to rename.
4. Delete the organization, this will only remove it from the Config-database
5. In SQL Server Management Studio, rename the database. This step can be a bit tricky if there are connections to the database. I dropped the database an reconected it
6. Import the organization with Deployment Manager.
After these steps I was able to upgrade the system, having restored the front end server as well. The thing that struck me with this in place upgrade was that it only took a bit over one hour to complete while a migration upgrade of a CRM 4.0 organization haven't taken under 2 hours yet. We also upgraded a copy of this organization with the migration upgrade path to create a development platform and that upgrade took more than 4 hours to complete.
When I had succeeded in upgrading this CRM I was a bit curious about whether dashes were allowed in the organization name or not. Reading the implementation guide for CRM 4.0, it clearly states that the organization name can only contain letters, numbers and underscores. This made me wonder how the database organization name was allowed to contain the dash. The installation or import guid in Deployment Manager really should have stoped this from happening. I wonder why it didn't. The next thing I thought about was the error message where it looks as if the program interpreted the dash as an underscore which seems strange. Lastly I wonder why the installation program doesn't check the organization database prior to upgrading the system. What I was "stuck" with was an upgraded server with a config database and organization database that were incompatible. I might have been able to do an import of the CRM 4.0 database if I had renamed it but since I wanted to test the upgrade path of the production server I chose to restore everything.
Rickard Norström
Developer at CRM-Konsulterna
www.crmkonsulterna.se
tisdag 3 juli 2012
It begins...
So this is my blog where I will gather my experience working with Microsoft Dynamics CRM.
I have been working with this product the past two years and have found myself in various situations where other blogs have helped me.
Hopefully I will have the same impact on others some day where my problems, and hopefully solutions to these problems, will help some other person having similar or the same problem solve them quicker.
Rickard Norström
Developer at CRM-Konsulterna
www.crmkonsulterna.se
I have been working with this product the past two years and have found myself in various situations where other blogs have helped me.
Hopefully I will have the same impact on others some day where my problems, and hopefully solutions to these problems, will help some other person having similar or the same problem solve them quicker.
Rickard Norström
Developer at CRM-Konsulterna
www.crmkonsulterna.se
Prenumerera på:
Inlägg (Atom)