Advanced Find is a fantastic tool. I train and train all of my end users on it as much as possible in the hopes that they will see the potential they have at their finger tips. You can always tell who is really getting it and who isn't too based on the questions they ask. Unfortunately some of the hardest questions to answer are the ones that are for the coolest queries. For instance, I've had users ask for a list of all Vendor's (custom entity) that don't have an activity associated to them in the last X number of months. This requires Advanced Find to query data that isn't there. Unfortunately this is the one major limitation of Advanced Find. It can only query existing data.
Don't lose hope yet though. Microsoft has provided some views for users like this out of the box. If you have ever seen the out of the box view for Accounts titled "Account: No Orders Last 6 Months" you'll notice that you cannot see nor create that query in Advanced Find. That's because the query is checking against data that isn't there, it's actually looking for null values. So, if Advanced Find can't do it then how did it get in there?
SavedQueries is the response that I give you. The SDK goes over them and explains them as giving you the ability to create your own saved queries that users will be able to use as views. There isn't much code on them in the SDK. I suppose that's because "technically" you can create a SavedQuery in 12 lines of code. The complex part about making a SavedQuery is figuring out the query to pull the data you want. I'll go over creating a SavedQuery and leave the data query to your own design.
The reason I say that you can "technically" create a SavedQuery in 12 lines of code is because that's how many lines you need to fill the SavedQuery object and then call the CreateRequest/Response methods to actually create the view. However, that assumes that you already have some query done and coded that you can pass into the SavedQuery. Allow me to explain a bit more with some code.
First off you need to create a new savedquery object:
savedquery sq = new savedquery();
Simple enough. Now, let's fill it's properties so that we can create it.
sq.name = "Name of View that will be seen by users";
sq.querytype = new CrmNumber();
//All custom SavedQueries must have a value of 0 (zero) for QueryType according to the SDK
sq.querytype.Value = 0;
//ReturnedTypeCode is the name not the objecttypecode
sq.returnedtypecode = EntityName.<entityName>.ToString();
Now, the last and most crucial part of the SavedQuery, the actual data query:
sq.fetchxml = queried.FetchXml;
Strangely enough the SavedQuery takes FetchXml...I thought that was out the door in MSCRM v3.0 but for some reason it is carried over even into MSCRM v4.0. Oh well, it is what it is. When I first saw this I thought I was going to have to pull out my MSCRM v1.2 SDK to remember how to build FetchXml that MSCRM will accept.
Luckily, however, I found two methods that saved me TONS of time:
QueryExpressionToFetchXml
FetchXmlToQueryExpression
Look those up in the SDK for code examples. But the short of the long is that once you have your RetrieveMultiple query all dialed in you just pass your query into the QueryExpressionToFetchXml method and it converts it for you. That's how I got my "queried.FetchXml" bit of code.
Once I had that all that was needed was to actually create it. And to do that I just follow the typical TargetCreate methodology. Then run the code and you'll see a new view in the object that you wrote your code for. You can then edit the sorting and columns but you'll notice that you cannot edit the filter, just like the "Accounts: No Orders Last 6 Months".
So, the great news is that essentially, if you can write it in SQL, you can write it in C# (or VB if that's what you like). And if you can write it in C#/VB then you can give the view to users in MSCRM. SQL's really a really powerful query tool, heck that's why it's Structure Query Language! So, saying that if you can do it in SQL means you can get it into MSCRM is a BIG deal for showing useful data/info/trends to users. Your other option is SRS, or some other fully custom web page. SRS is just a static list that isn't as easy to make the report able to have actions (update records) performed against it. And a totally custom web page will work as well, it's just typically a lot longer to turn around the dev and actual use of the page because there is now more things to build than just the query that was requested.
Have fun with this...I know I have been and remember, when in doubt, use that SDK, it's probably got something you haven't thought the system could do.
Happy coding!
David Fronk
Dynamic Methods Inc.
33 comments:
David thanks thats a great artical.
Can the fetchXML return columns from all sorts of related entities (not just the limited ones on V4)and have them display in the view as well?
Patrick,
What columns the FetchXML returns really doesn't have anything to do with what you will be able to display in your view. I know that the FetchXML should contain that information but for whatever reason (at least when I did this) the columns that I returned did not pass through to the view. I had to go and add the columns of the view through the GUI, and then I was locked into the structure and format of v4.0. On the upside, that's better than being limited to what 3.0 could do. But no matter how you push columns through to the view they will have to conform to the structure of v4.0.
Sorry it's probably not the answer you were looking for but I hope this helps.
David Fronk
Dynamic Methods Inc.
Thats a great article. Thanks for teaching us all your tricks. You are a scholar and a gentleman.
Hi David,
do you have a sample on how to use a SQL for a saved query?
You don't use a SQL script per se, at least you don't use the actual SQL script in your C# or VB code to generate the savedquery. My process for creating a savedquery is first right the select statement in SQL and make sure it gives me what I expect. Then I have to figure out how to translate that into a RetrieveMultiple call within C# to utilize the CRM API's. The translation is probalby most of the work when writing this out. So, to get all active accounts I would write a SQL statement like: select * from account. The I would write out a RetrieveMultiple call that would have a condition like, where accountid is not null. If you haven't written a RetrieveMultiple then check the SDK or some of the manuals provided by Microsoft. There are some great examples and they can explain it much better than I can in a comment.
But the point I wanted to bring up is that I'm not really using a SQL statement in my code, I just do that for my testing purposes. Then I translate it in my head and put it into C#/CRM API terminology.
David Fronk
Dynamic Methods Inc.
OK David,
but you can write SQL that you cannot traslate into RetrieveMultiple: for example
1. all accounts not in a marketing list
2.
all tasks of type phone within a category
union
all tasks of type activity within a category
.. or do I miss something?
R
R,
Actually the ability to write a SavedQuery for looking up records that have null values for what you want. Take for instance the out of the box view "Accounts with No Orders in the last 6 months". That is showing Accounts that do not have Orders associated to them within a given period of time. That would essentially be the same as all Accounts that are not a part of some Marketing List. If you were to write that out in SQL you would have to perform 2 subselect within your original select statement. That means you just need to perform a RetrieveMultiple within a RetrieveMultiple. One of the condition operators available to choose from is "NotIn" and that will be your key to set up the "Sub-RetrieveMultiple". Option 1 is definitely a possibility and, in my opinion, that's what makes the SavedQuery so cool.
The second item that you asked about, I may not be seeing the difficulty fully so I apologize if I over simplify. But to see:
all tasks of type phone within a category
union
all tasks of type activity within a category
I would just use a where statement in SQL to filter out what I want. All tasks where category = x and type = phone or activity. I am making the assumption that type is a dropdown and category is the text field that comes out of the box on all activities. To convert a SQL where into CRM RetrieveMultiple is just to apply multiple Conditions each with their own Filter (AND or OR).
It may get complex, but my understanding of the RetrieveMultiple is that it should be able to mimic anything you do in SQL. I could be wrong, but that's my understanding.
I hope this helps,
David Fronk
Dynamic Methods Inc.
Hi David,
I think there is a diffirent not documented method.
If you look a the customization of the account without activity view , you will find that a queryapi tag is used:
savedqueryid {CFBCD7AF-AEE5-4E45-8ECC-C040D4020581} /savedqueryid
queryapi CRMAccount.RetrieveAccountNoCampaignSent /queryapi
savedqueryidunique{5E4682B8-AF14-4ABF-A433-2E07FE26DF93}/savedqueryidunique.
As I said it's not documented, or you know someoone in MS that can explain it to us?
Best regards,
R
R,
The savedquery method is used to referenced saved queries that already exist in the system. For example, you found the RetrieveAccountNoCampaignSent saved query that exists on the Account object. Instead of having to rewrite your RetrieveMultiple code all the time, you could use your SavedQuery over and over again and have your code be applied to that. SavedQueries, as you stated, are referenced through their GUID so you will need to find that from the SQL tables. There is quite a bit of documentation in the CRM 4.0 SDK, maybe not exactly what you are looking for but there is documentation on TargetRetrieveSavedQuery, ValidateSavedQuery, TargetCreateSavedQuery, TargetUpdateSavedQuery, etc.
Hopefully this helps some.
David Fronk
Dynamic Methods Inc.
Hi David,
Is there any way to create "SavedQuery" from the code?
If you are referring to the SDK when you say "from the code" then yes you can. You create the logic then run the code once and have the code create the SavedQuery so that it can be used over and over from within the views of the object the view was created.
David Fronk
Dynamic Methods Inc.
Hi David,
I want to edit existing view in CRM 4.0 programmatically, but didnt find any solution..Is there any way to do that?
Thanks in advance.
- Krutika
Krutika,
You will need to use the TargetUpdateSavedQuery call. If you get the SavedQuery's GUID and then pass in the new query parameters and update the SavedQuery you should be all set. Please note that you may not be able to do this to queries that are not marked as "Customizable", they may have locked down out of the box queries so that you can't mess with them. I haven't done it but this should work for you.
David Fronk
Dynamic Methods Inc.
As it related to creating complex views, can you incorporate views that link data to an external database - meaning not all of the data resides in the ms crm database ?
Creating Views that contain data from sources other than MSCRM will not be supported and most likely not possible. I haven't tried it personally, so don't let me stop you but my reasoning is that the API's push the data through the expected columns of a given object, so if those columns aren't in CRM (columns from a separate data source) the API's won't know how to handle the data and will break. You would have to create your own custom page and everything to display the data.
David Fronk
Dynamic Methods Inc.
Hello, I've been struggling with something you included in one of your comments...
"That means you just need to perform a RetrieveMultiple within a RetrieveMultiple. One of the condition operators available to choose from is "NotIn" and that will be your key to set up the "Sub-RetrieveMultiple"."
I've been searching the web for hours and I can't find an example of how to "perform a RetrieveMultiple within a RetrieveMultiple". Everything I read indicates that when you use ConditionOperator.NotIn, you have to hardcode a list of values.
Can you give sample code on how to create a "Sub-RetrieveMultiple"?
albert,
Doing a "sub-retrievemultiple" is very similar to a sub-select in SQL, if you are familiar with those. But in CRM, C# code, you would have a method that retrieves something and returns an array of id's. Then you use that array as your list to query against.
For example here is some code that gets all Opportunities that do not have a custom entity called "Project" associated to it:
public string[] getProjects()
{
CrmAuthenticationToken token = new CrmAuthenticationToken();
token.AuthenticationType = AuthenticationType.AD;
token.OrganizationName = _organization;
CrmService crmService = new CrmService();
crmService.Url = String.Format(
"http://{0}:{1}/MSCRMServices/2007/CrmService.asmx",
_hostname, _port, "AD");
crmService.CrmAuthenticationTokenValue = token;
//crmService.Credentials = System.Net.CredentialCache.DefaultCredentials;
crmService.Credentials = new System.Net.NetworkCredential(sUserName, sPassword, sDomain);
ColumnSet cols = new ColumnSet();
cols.Attributes = new string[] { "dm_projectsid"
,"dm_opportunityprojectid"
};
ConditionExpression condition = new ConditionExpression();
condition.AttributeName = "dm_opportunityprojectid";
condition.Operator = ConditionOperator.NotNull;
FilterExpression filter = new FilterExpression();
filter.FilterOperator = LogicalOperator.And;
filter.Conditions = new ConditionExpression[] { condition };
QueryExpression query = new QueryExpression();
query.EntityName = EntityName.dm_projects.ToString();
query.ColumnSet = cols;
query.Criteria = filter;
BusinessEntityCollection retrieved = crmService.RetrieveMultiple(query);
int Num = retrieved.BusinessEntities.Length;
//WriteToFile("Num = " + Num);
string[] projects = new string[Num];
dm_projects[] p = new dm_projects[Num];
int i = 0;
while (i < Num)
{
p[i] = (dm_projects)retrieved.BusinessEntities[i];
projects[i] = p[i].dm_opportunityprojectid.Value.ToString();
i++;
}
return projects;
}
protected void Submit_Click(object sender, EventArgs e)
{
CrmAuthenticationToken token = new CrmAuthenticationToken();
token.AuthenticationType = AuthenticationType.AD;
token.OrganizationName = _organization;
CrmService crmService = new CrmService();
crmService.Url = String.Format(
"http://{0}:{1}/MSCRMServices/2007/CrmService.asmx",
_hostname, _port, "AD");
crmService.CrmAuthenticationTokenValue = token;
crmService.Credentials = System.Net.CredentialCache.DefaultCredentials;
ColumnSet cols = new ColumnSet();
cols.Attributes = new string[] { "opportunityid"
,"description"
,"estimatedclosedate"
,"estimatedvalue"
,"dm_servicerevenue"
,"dm_softwarerevenue"
,"name"
,"dm_primarycontactid"
,"customerid"
,"salesstagecode"
,"statecode"
};
// Create the ConditionExpression
ConditionExpression[] condition1 = new ConditionExpression[3];
condition1[0] = new ConditionExpression();
condition1[0].AttributeName = "statecode";
condition1[0].Operator = ConditionOperator.Equal;
condition1[0].Values = new object[1];
condition1[0].Values[0] = 0;
condition1[1] = new ConditionExpression();
condition1[1].AttributeName = "opportunityid";
condition1[1].Operator = ConditionOperator.NotIn;
condition1[1].Values = getProjects();
FilterExpression filter = new FilterExpression();
filter.FilterOperator = LogicalOperator.And;
filter.Conditions = condition1; //new ConditionExpression[] {condition1};
QueryExpression query = new QueryExpression();
query.EntityName = EntityName.opportunity.ToString();
query.ColumnSet = cols;
query.Criteria = filter;
QueryExpressionToFetchXmlRequest expression = new QueryExpressionToFetchXmlRequest();
expression.Query = query;
QueryExpressionToFetchXmlResponse queried = (QueryExpressionToFetchXmlResponse)crmService.Execute(expression);
savedquery sq = new savedquery();
sq.fetchxml = queried.FetchXml;
sq.name = "Opportunities without Projects";
sq.querytype = new CrmNumber();
sq.querytype.Value = 0;
sq.returnedtypecode = EntityName.opportunity.ToString();
TargetCreateSavedQuery target = new TargetCreateSavedQuery();
target.SavedQuery = sq;
CreateRequest request = new CreateRequest();
request.Target = target;
CreateResponse response = (CreateResponse)crmService.Execute(request);
}
Hopefully that helps a bit.
David Fronk
Dynamic Methods Inc.
In the customizations.xml savedqueryidunique fields are not used at all. Look in Microsoft.Crm.Tools.ImportExportPublish.ImportSavedQueryHandler.ImportItem()
with using Reflector.
They are exported only because the query operation outputs them when Microsoft.Crm.Tools.ImportExportPublish.SavedQueryHandler.ExportItem(XmlDocument importDocument) runs.
Adam
Adam,
That's a good point, thanks for mentioning that. I was focusing mostly on creating the query and allowing users to manually modify the query via the UI once the Saved Query was created. I'm always open to new and better ways to do things.
Thanks for your comments.
David Fronk
Dynamic Methods Inc.
hi David,
I want all the Accounts who have not been contacted for x days by appointments... can we achieve this by savedquery?
You may have to hard code your x days, but it is possible. It will be a fairly complex logic but it should be possible. My suggestion would be to get all Accounts that have been Contacted in X days (and whatever other criteria you may have) and then select all Accounts NOT IN that list. That will give you everyone you want to target and contact.
Hope that helps.
David Fronk
Dynamic Methods Inc.
¿Why you dont make the queryes directly in FetchXML before retrievemultiple methods?.
You definitely could use FetchXML directly before the retrievemultiple queries since that's what the retrievemultiple query wants. But for those who don't have an FecthXML builder, or who can't write FetchXML out on their own, using the SDK queries and then converting it works as well.
It's definitely possible, for my purposes it was just easier to show the as code within the SDK calls.
Hopefully this helps clarify.
David Fronk
Dynamic Methods Inc.
If your using a retrievemultiple inside another retrievemultiple, arn't you hardcoding the list of the contacts with no orders in the last 6 months?
Since the fetchXML generated will use the 'notin' operator, the initial list created by the outter query will not use the inner retreivemultiple query, but rather a hardcoded list generated by the inner query, which is fine at the time, but any time the query needs to be re-run, the plugin which built it will need to be run to update the hardcoded list...
I've tried constructing the fetchXML using the fetchXML builder, but it is not possible, so also I'm wondering what the final fetchXML would look like for this type of query. Surely doing this method of creating a QueryExpression and converting to fetchXML is not the best. Once you've converted it once, you then have the fetchXML, and all you need to do is update the userquery table with the new fetch statement and voila.
Actually the query is run at runtime each time the view is accessed. The retrievemultiple within the retrievemultiple is all built into the query itself, and thus the FetchXML, and when the query is accessed it runs against the latest data in the database. If it didn't do this then you are correct, it would essentially be worthless since it would always show the same list.
Hope that helps,
David Fronk
Dynamic Methods Inc.
Ok.. then this does indeed look like an excellent method! Any chance you can post the fetchXML that is generated on this blog? I'm just interested to see what nested fetchXML statements look like since I didn't think they were possible.
As an alternative, you could add a maxdate field to account, then populate this with a 1 off update in SQL. A workflow/javascript can be setup to update the parent (account) record with the max date each time a child record is entered. This allows users to then just do a normal advanced find on a max field...
I must say your way does seem a fair bit more elegant, but I came up with this as another work around, since I didn't think the fetch statements could be nested and all run at run-time.
Owen.
First of all thanks all for a good article.
David, I've tried a query similar to the one you outline at 11/15/2008 7:47 AM but I have to say that, from what I've seen, Anonymous appears to be right - the entities that are excluded using the inner query are simply hardcoded in the final FetchXML string and therefore the resulting query does not update itself when any data is changed. If this does not have be the case what am I missing?
Thanks again
Stuart,
I just tested my custom created query that shows Opportunities that do not have a Project associated to it. I found 268, created a new Opportunity with no relationship to a Project and the count went up to 269. I then went and found the newly created Opportunity within that view. As long as you build one query (RetrieveMultiple) that is dynamic that becomes the subquery (ID list for the parent query) that runs dynamically you should be just fine.
I wish there was a better way to show this, but the concept is that if you use the API's and build dynamic RetrieveMultiple calls you shouldn't have any problems.
David Fronk
Dynamic Methods Inc.
Posting that fetchXML might be a bit dicey. But one thing you could do would be to find the query entry in SQL (Accounts with no Orders in 6 months) and copy the fetchXML column into notepad and read it from there. It should look extremely close to what my query would look like.
David Fronk
Dynamic Methods Inc.
So, once I make one of these everyone can see it? Is there a way to make it visible only to certain users? Can I delete it after making it?
These kinds of views are system views, so everyone can see it. In CRM 3.0 you can limit who can see it but in 4.0 they removed that ability, so the only way to work around that is through unsupported SQL hacks. The view will be visible in the list of views with the rest of the system views and you will be able to modify everything (just like the other system views) except for the query. That will still have to be done via code. And yes, you can delete from the UI after making it.
David Fronk
Dynamic Methods Inc.
Hi David,
I followed your code to create a view to show Contacts without appplications. But i don't think it is dynamic. Because the subquery result is saved in fetchxml of the new view, such as {75D36B3F-3CE5-DE11-9007-000C29554EBB}{75D36B3F-3CE5-DE11-9007-000C29554EBB}. How does this change after data changes in CRM?
Thanks
Andrew
Andrew,
Whenever you click on a view it runs the code that contains the query. The database may save the fetchxml from the last time that it was viewed, but it gets rerun everytime you click on/look at that view. I've tested the code that I've posted and verified that the view is in fact dynamic.
David Fronk
Dynamic Methods Inc.
Post a Comment