Wednesday, March 15, 2017

Get Child Count in Parent List SharePoint Lookup Field


I came across a requirement where I required the number of comments
 against a post in a child list (lstComments) maintained in the Parent list (lstPost).

Below is a very efficient way to achieve this


 using (SPSite site = new SPSite(webUrl))
            {
                using (SPWeb web = site.OpenWeb())
                {
                    SPList lstPost = web.Lists.TryGetList("Post");
                    SPList lstComments = web.Lists.TryGetList("Comments");

                    if (lstPost != null &&  lstComments != null )
                    {
                        SPField id= lstPost .Fields.TryGetFieldByStaticName("ID");
                        if (null != id)
                        {
                            // Create a PostID lookup field on the lstComments list.  
                            string PostIdName = lstComments .Fields.AddLookup("PostID", lstPost .ID, true);
                            SPFieldLookup PostIdField =
                                (SPFieldLookup)lstComments .Fields.GetFieldByInternalName(PostIdName );
                            PostIdField .LookupField = id.InternalName;
                            PostIdField .Update();
                            AddToDefaultView(lstComments , PostIdName );

                            // Create a CommentCount Lookup field to maintain child count in the lstPost list
                            string commentCountName = lstPost .Fields.AddLookup("CommentsCount", lstComments .ID, false);
                            SPFieldLookup commentCountField =
                                (SPFieldLookup)lstPost .Fields.GetFieldByInternalName(commentCountName );
                            commentCountField .LookupField = id.InternalName;
                            commentCountField .CountRelated = true;
                            commentCountField .Update();
                            AddToDefaultView(lstPost , commentCountName );
                        }
                    }
                }

            }