In our CRM environment users have a 1:N relation to an available hours entity which represents their actually available hours for the week. I'm looking for a way in c# to retrieve all the available hours for users that are of a specific team for the current week.
I'm new to developing in CRM and I've looked around but there seems to be a lot of ways to do this and im not sure which is best suited.
The language is C# and the version of CRM is MS CRM 4.0
I'll cover this is 3 bits: general approach, code itself and then some notes on the code (the code is commented to draw attention to certain things, though but some of them will bear further explanation outside the code).
As you've seen, there are a couple of ways to do things but for external applications interacting with CRM via the web service it comes down to 3 main options:
DynamicEntity in your web service callsIf your system is super-simple you can normally get away with (1) but I would recommend either of (2) or (3). Using (2) means that you only really have to remember a handful of web service methods and its good if you ever move into plugins or workflow assemblies as the concepts carry across reasonably well. (3) is good if you know FetchXML and can form the appropriate query.
I normally approach these things using (2) as it's commonly found, it's a nice middle of the road approach and, like I said, your code will be reasonably easy to translate to a plugin or workflow assembly. FetchXML carries across quite well but I was never good at forming the queries - I'll cover some techniques for this later but lets go with (2).
Also, if you use DynamicEntity you shouldn't need to refresh your web references because of how you work with it and its array of Property objects (basically you get flexibility at the expense of strong typing) as you'll see in the code. If you go with (1) you get strong typing against your custom entities but you'll have to keep refreshing your WebReference depending on the cadence of changes people make to your entities.
This is in a little console application where I've added a WebReference to the CRM service and done some calls to simulate your scenario. The code should carry across to other apps like web apps. I have tried to comment it so it is probably worth a read through before moving to the next section.
(NB. I don't claim this is the world's best code, but it does seem work and should get you started)
(NB2. I made the mistake of calling my namespace for the web reference CrmService - please don't make the same mistake as me....)
static void Main(string[] args)
{
CrmService.CrmService svc = new CrmService.CrmService();
svc.CrmAuthenticationTokenValue = GetToken();
svc.UseDefaultCredentials = true;
#region 1 - Retrieve users in team
RetrieveMembersTeamRequest teamMembersReq = new RetrieveMembersTeamRequest()
{
EntityId = new Guid("D56E0E83-2198-E211-9900-080027BBBE99"), //You'll need the team GUID
ReturnDynamicEntities = true
};
ColumnSet teamMembersReqColumnSet = new ColumnSet();
teamMembersReqColumnSet.Attributes = new string[] { "systemuserid", "domainname" };
teamMembersReq.MemberColumnSet = teamMembersReqColumnSet; //Don't use: teamMembersReq.MemberColumnSet = new AllColumns()
List<Guid> userIdList = new List<Guid>();
RetrieveMembersTeamResponse teamMembersResp = svc.Execute(teamMembersReq) as RetrieveMembersTeamResponse;
if (teamMembersResp != null)
{
BusinessEntity[] usersInTeamAsBusinessEntity = teamMembersResp.BusinessEntityCollection.BusinessEntities;
List<DynamicEntity> usersInTeamAsDynEntity = usersInTeamAsBusinessEntity.Select(be => be as DynamicEntity).ToList(); //BusinessEntity not too useful, cast to DynamicEntity
foreach (DynamicEntity de in usersInTeamAsDynEntity)
{
Property userIdProp = de.Properties.Where(p => p.Name == "systemuserid").FirstOrDefault();
Property domainNameProp = de.Properties.Where(p => p.Name == "domainname").FirstOrDefault();
if (userIdProp != null)
{
KeyProperty userIdKeyProp = userIdProp as KeyProperty; //Because it is the unique identifier of the entity
userIdList.Add(userIdKeyProp.Value.Value); //Chuck in a list for use later
Console.Write("Key: " + userIdKeyProp.Value.Value.ToString());
}
if (domainNameProp != null)
{
StringProperty domainNameStringProp = domainNameProp as StringProperty; //Because its data type is varchar
Console.Write("| Domain Name: " + domainNameStringProp.Value);
}
Console.WriteLine();
}
}
#endregion
/*
* For this example I have created a dummy entity called new_availablehours that is in a 1:N relationship with use (i.e. 1 user, many new_available hours).
* The test attributes are :
* - the relationship attribute is called new_userid...this obviously links across to the GUID from systemuser
* - there is an int data type attribute called new_hours
* - there is a datetime attribute called new_availabilityday
*/
#region Retrieve From 1:N
RetrieveMultipleRequest req = new RetrieveMultipleRequest();
req.ReturnDynamicEntities = true; //Because we love DynamicEntity
//QueryExpression says what entity to retrieve from, what columns we want back and what criteria we use for selection
QueryExpression qe = new QueryExpression();
qe.EntityName = "new_availablehours"; //the entity on the many side of the 1:N which we want to get data from
qe.ColumnSet = new AllColumns(); //Don't do this in real life, limit it like we did when retrieving team members
/*
* In this case we have 1 x Filter Expression which combines multiple Condition Operators
* Condition Operators are evaluated together using the FilterExpression object's FilterOperator property (which is either AND or OR)
*
* So if we use AND all conditions need to be true and if we use OR then at least one of the conditions provided needs to be true
*
*/
FilterExpression fe = new FilterExpression();
fe.FilterOperator = LogicalOperator.And;
ConditionExpression userCondition = new ConditionExpression();
userCondition.AttributeName = "new_userid"; //The attribute of qe.EntityName which we want to test against
userCondition.Operator = ConditionOperator.In; //Because we got a list of users previously, the appropriate check is to get records where new_userid is in the list of valid ones we generated earlier
userCondition.Values = userIdList.Select(s => s.ToString()).ToArray(); //Flip the GUID's to strings (seems that CRM likes that) then set them as the values we want to evaulate
//OK - so now we have this userCondition where valid records have their new_userid value in a collection of ID's we specify
ConditionExpression dateWeekBound = new ConditionExpression();
dateWeekBound.AttributeName = "new_availabilityday";
dateWeekBound.Operator = ConditionOperator.ThisWeek; //ConditionOperator has a whole bunch of convenience operators to deal with dates (e.g. this week, last X days etc) - check them out as they are very handy
/*
* As an aside, if we didn't want to use the convenience operator (or if none was available) we would have to create a ConditionExpression like:
*
* ConditionExpression dateLowerBound = new ConditionExpression();
* dateLowerBound.AttributeName = "new_availabilityday";
* dateLowerBound.Operator = ConditionOperator.OnOrAfter;
* dateLowerBound.Values = new object[] { <Your DateTime object here> };
*
* And a corresponding one for the upper bound using ConditionOperator.OnOrBefore
*
* Another alternative is to use ConditionOperator.Between. This is flexible for any sort of data, but the format of the Values array will be something like:
* ce.Values = new object[] { <lower bound>, <upper bound> };
*/
fe.Conditions = new ConditionExpression[] { userCondition, dateWeekBound }; //Add the conditions to the filter
qe.Criteria = fe; //Tell the query what our filters are
req.Query = qe; //Tell the request the query we want to use
RetrieveMultipleResponse resp = svc.Execute(req) as RetrieveMultipleResponse;
if (resp != null)
{
BusinessEntity[] rawResults = resp.BusinessEntityCollection.BusinessEntities;
List<DynamicEntity> castedResults = rawResults.Select(r => r as DynamicEntity).ToList();
foreach (DynamicEntity result in castedResults)
{
Property user = result.Properties.Where(p => p.Name == "new_userid").FirstOrDefault();
Property hours = result.Properties.Where(p => p.Name == "new_hours").FirstOrDefault();
if (user != null)
{
LookupProperty relationshipProperty = user as LookupProperty; //Important - the relationship attribute casts to a LookupProperty
Console.Write(relationshipProperty.Value.Value.ToString() + ", ");
}
if (hours != null)
{
CrmNumberProperty hoursAsCrmNumber = hours as CrmNumberProperty; //We also have CrmFloatProperty, CrmDecimalProperty etc if the attribute was of those data types
Console.Write(hoursAsCrmNumber.Value.Value);
}
Console.WriteLine();
}
}
#endregion
Console.ReadLine();
}
static CrmAuthenticationToken GetToken()
{
CrmAuthenticationToken token = new CrmAuthenticationToken();
token.AuthenticationType = 0; //Active Directory
token.OrganizationName = "DevCRM";
return token;
}
I'm not going to do a blow-by-blow, but home in on the key points:
Execute() method where we pass it a request object and get back a response object. The requests will all be objects of class <Operation>Request and responses will be objects of class <Operation>Response.DynamicEntity - the <Operation>Request objects will typically expose a property called ReturnDynamicEntities which you should be setting to true<Operation>Request objects have a ColumnSet property where you can specify what attributes you want returned. It is typically bad practice to specify AllColumns() and instead you should be explicit about what data you want returned. Attributes need to match their names in CRM (so of the form <prefix>_<field name>) and all in lower caseRetrieveMultipleRequest and RetrieveMultipleResponse methods (if you only want one record then you can just use RetrieveRequest and RetrieveResponse...but you need to know the GUID of what you are looking for to feed into the RetreiveRequest object).RetrieveMultipleRequest we feed it a query (QueryExpression) which says what entity(EntityName) we want to get multiple of, the attributes(ColumnSet) of that entity we want to return and the filter(Criteria) which is used to select the actual records we wantQueryExpression, FilterExpression and ConditionExpression. An important thing to know is what operators you have available for you in ConditionExpression - I have tried to call some out in the code, but once again the SDK is your best friend to know what is availableFilterExpression and ConditionExpressionRetrieveMultipleResponse contains an array of BusinessEntity. The BusinessEntity by itself is pretty useless so we cast that to a list of DynamicEntity - LINQ is really your friend here and for quite a bit of messing with CRM stuff, LINQ comes in handyde.Properties.Where(p => p.Name == "systemuserid").FirstOrDefault(); and then check if it is NULL. This is because if in CRM an attribute of a record is NULL it won't be returned from the service call - so just because you request an attribute in the ColumnSet don't make the automatic assumption it is there (unless you have it set as mandatory in CRM - then probably OK)...test it and your app will be a whole lot less brittle.Property class itself has limited value - to really work with a property you have to cast it to the thing it actually is. I keep harping on about it but the SDK will tell you what the types are but it starts to feel natural after a while, e.g. the GUID for the record is in a KeyProperty, ints are in CrmNumberProperty, floats are in CrmFloatProperty, strings are StringProperty etc. Note the weak typing (which I mentioned previously) where we have to get properties by name, cast to the proper value etcSoapException and the info you typically want will be in the Detail property - very important to remember this or you'll look at the exception and think it's not telling you a whole bunchFetchXML is really powerful but really fiddly. If you get good at it though then you'll get a lot of good mileage - a tool like this is useful. Also, a handy trick - if you can construct what you want (or an example of what you want) as an advanced find through the CRM UI then you can use this trick to get the FetchXML it used....you'll probably need to tweak the GUIDs and the like but it gives you a building block if you want to use FetchXML in your code as most of the query is written for you.