Count Related Field

How many times we have cursed SharePoint for not able to get the item count natively without writing code or using workflow. There is an easier way which I stumbled up recently but may be some of you have already figured this out earlier. I wanted to document this so this can be useful for someone like me who is always interested in code.

Recently when I was working on a project where there is absolutely no server side code, pretty much everything was done at the client side. There was a need to get item count per category. This is simple on the list because we could just do a group by and we would know how many items in a particular category, but we wanted to use those number to draw chart. The group by numbers will not be useful and we cannot use char web part (yes I know chart part is gone in 2013 but this project is still in 2010).

How do we do this? We had a lists of categories in a list which is used as lookup for the categories column. We initially thought of writing a client side script that would count items and add it to a field. But this needs to be triggered every time a new item is added to the list. I was trying to create a new field and accidentally I chose lookup field and I noticed that there is a field called count related in the options when i chose the looked up list a source. When I created a field I could immediately see item count for all the items in the lookedup list. Form now on this becomes easy to use this chart part or anything you want even in code that would need to do count.

Figure shows how to create Count Related field

Create new List column Lookup

Item count corresponding to categories

Please leave your feedback in comments.

CAML to Query User field

Sharepoint as a database is more of a Flat structure database. Querying is often times the most time consuming operation in especially when running against millions of List data. Microsoft has done a lot to increase the query time, still there are some limitations in sharepoint. Hence it is necessary to optimize the CAML as much as possible when querying against large Lists or document Libraries.

Often times I get asked by team members about writing CAML against Lookup Field and User Field. I decided to write it down here as a reference for everybody who wants to use CAML to query Sharepoint user field for me as well because every time some one asks me I tend to forget and needs to go back to code and give them the query. So I thought this is a right form so every body can be benefited.


Code:

SPQuery query = new SPQuery();
query.Query = string.Format("<Where><Eq><FieldRef Name='Author' LookupId='TRUE' /><Value Type='Int'>{0}</Value></Eq></Where>", SPContext.Current.Web.CurrentUser.ID);
SPListItemCollection itemCollection = oList.GetItems(query);


I am using the sharepoint user field as sharepoint lookup field and performing the query based on user id. I prefer this method because there is no way this could go wrong as no 2 users can have same ID. This struck me when my team was implementing FBA and we created test user with same name. Interestingly the same query can be used for sharepoint Lookup field as well. If you notice the way Sharepoint stores the Look up field you can the format as

1;#lookupvalue

This same format is used for SPUser field as well. This set up is actually a blessing for us  "Developers"