My Old Blog

In case people are just stumbling on to this site, I still have some good content on my old blog:

http://datacogs.com/datablogs

DevExpress Treeview, SQL Server Hierarchical Data and .NET MVC 3

I’m just going to get my code snippets in here for now and write explanations later:

Firstly SQL Stored Proc:

–usp_OrderedFunctionalLocationHierarchy ‘SRO’

ALTER PROCEDURE [dbo].[usp_OrderedFunctionalLocationHierarchy](@Seed varchar(50), @depth int=null)

AS

;WITH MachineCTE(FunctionalLocation, SuperiorFunctionalLocation,Description, Manufacturer, Depth, SortCol)

AS

SELECT FunctionalLocation, SuperiorFunctionalLocation,Description, Manufacturer, 0,CAST(FunctionalLocation ASvarbinary(max))

WHERE FunctionalLocation = @Seed

UNION ALL

SELECT E.FunctionalLocation, E.SuperiorFunctionalLocation, E.Description, E.Manufacturer, M.Depth+1,

CAST(SortCol +CAST(E.FunctionalLocation ASbinary(4))ASvarbinary(max))

FROM dbo.Machine AS E

JOIN MachineCTE AS M

ON E.SuperiorFunctionalLocation = M.FunctionalLocation

)

SELECT

FunctionalLocation,

SuperiorFunctionalLocation,Description, Manufacturer, Depth

–,SortCol

FROM MachineCTE

WHERE Depth = @depth OR @depth isnull

ORDERBY SortCol

——–

View:

 

@Html.DevExpress().TreeView(

 

     settings =>

 

     {

 

         settings.Name = “EquipmentTree”;

 

         settings.CallbackRouteValues = new { Controller = “EngineeringRequests”, Action = “EquipmentTreeView” };

 

         settings.Images.NodeImage.Width = 13;

 

         settings.Styles.NodeImage.Paddings.PaddingTop = 3;

 

         settings.SyncSelectionMode = DevExpress.Web.ASPxClasses.SyncSelectionMode.CurrentPath;

 

         settings.AllowSelectNode = true;

 

 

 

         settings.VirtualModeCreateChildren = (source, e) =>

 

          {

 

              List<TreeViewVirtualNode> children = new List<TreeViewVirtualNode>();

 

              children = BMAEngineeringRequest.CacheHelper.GetMachineHierarchy(e.NodeName);

 

              e.Children = children;

 

          };

 

     }).GetHtml()

 

 

 

/////////

 

Helper Function:

 

public static List<TreeViewVirtualNode> GetMachineHierarchy(string topNodeText)

        {

            string seed = string.IsNullOrEmpty(topNodeText) ? “SRO” : topNodeText;

            BMAFormsEntities db = new BMAFormsEntities();

            List<GetEquipmentHierarchy_Result> equipmentList = db.GetEquipmentHierarchy(seed, 1).ToList();

            if (equipmentList.Count > 0)

            {

                List<TreeViewVirtualNode> nodeList = new List<TreeViewVirtualNode>();

                foreach (GetEquipmentHierarchy_Result result in equipmentList)

                {

                    nodeList.Add(new TreeViewVirtualNode(result.FunctionalLocation, result.FunctionalLocation + ” ” + result.Description));

                }

                return nodeList;

            }

            else

                return null;

          

 

        }

 

 

XPath to Count Preceding Siblings with the Same Name (InfoPath 2010)

I just spent hours trying to get this to work. 

I wanted to increment a counter in a repeating table based on matching existing records

count(../preceding-sibling::*[my:referralAction = current()])

InfoPath Forms Services 2010 – Open Form in Dialog and Close Window on Submit

My challenge for today – figure out how to open an InfoPath Forms Services form in a dialog browser window and then close that window once the form is submitted. Sounds easy? Well, not quite.

The first thing is to create a connection to submit the form, and then make sure you select the option to close the form on submit:

 

Then publish to your SharePoint library as you would normally.

Next use SharePoint designer to edit the AllItems.aspx page for the form library…

 

1. In the PlaceHolderAdditionalPageHead, add some javascript to override the default new item link provided in the STSNavigate method.
   I got the main idea from here, http://social.msdn.microsoft.com/Forums/en/sharepointinfopath/thread/2cb1750b-4e22-4acc-b43e-6baeb5d11c1d, where you will find a good explanation.
   However, I didn’t just want to pop up a window, I wanted a to open the form in a modal dialog, so a little more digging led me here – http://www.infopathdev.com/forums/p/20182/69992.aspx, and so my code looks something like this:
   
   

<asp:content contentplaceholderid=”PlaceHolderAdditionalPageHead” runat=”server”>

    <SharePoint:RssLink runat=”server”/>

     <Script>

function STSNavigate(Url){

         var newUrl = STSPageUrlValidation(Url);

         openForm(newUrl, “Referral”, 1000, 1000);

}

function openForm(formurl, title, w, h) {

        var options = {

         url: formurl,

         title: title,

         allowMaximize: false,

         showClose: true,

         width: w,

         height: h,

         dialogReturnValueCallback: Function.createDelegate(null, FormCallback)

         };

        

         SP.UI.ModalDialog.showModalDialog(options);

        }

        

        function FormCallback(dialogResult, returnValue) {

         SP.UI.Status.removeAllStatus();

         javascript:location.reload(true);

        }

</Script>

</asp:content>

1. Now the only thing left to do is close the window on submit. As it is now, the form closes but the window stays open. This article gave a workable solution: http://johnliu.net/blog/2011/6/17/infopath-2010-close-the-browser-window.html. But I needed a good function to find and replace the Source part of the query string. So the final piece of the puzzle was this nice bit of javascript: http://clintoncherry.wordpress.com/2008/04/24/javascript-search-and-replace-query-string-values/. So the final code looks like this:

<asp:content contentplaceholderid=”PlaceHolderAdditionalPageHead” runat=”server”>

    <SharePoint:RssLink runat=”server”/>

     <Script>

function STSNavigate(Url){

         var newUrl = STSPageUrlValidation(replaceQueryString(Url, “Source”, “/SitePages/ClosePage.aspx”));

         openForm(newUrl, “Referral”, 1000, 1000);

//window.showModalDialog(STSPageUrlValidation(replaceQueryString(Url, “Source”, “/SitePages/ClosePage.aspx”)));

//window.location.reload();

}

 

function replaceQueryString(url,param,value) {

     var re = new RegExp(“([?|&])” + param + “=.*?(&|$)”,”i”);

     if (url.match(re))

     return url.replace(re,’$1′ + param + “=” + value + ‘$2′);

     else

     return url + ‘&’ + param + “=” + value;

        }

        

     function openForm(formurl, title, w, h) {

        var options = {

         url: formurl,

         title: title,

         allowMaximize: false,

         showClose: true,

         width: w,

         height: h,

         dialogReturnValueCallback: Function.createDelegate(null, FormCallback)

         };

        

         SP.UI.ModalDialog.showModalDialog(options);

        }

        

        function FormCallback(dialogResult, returnValue) {

         SP.UI.Status.removeAllStatus();

         javascript:location.reload(true);

        }

        
 

 

</Script>

</asp:content>

 

And the result looks something like this:

 

 

And then after submit, you’re back here, with the screen refreshed!

 

 

 

CAML Query – Just got caught out with a field name containing a space

If you make a mistake in your CAML query, you just get the obscure error “one or more fields are not installed properly”

Sometimes it can be something as simple as failing to close and <and> tag.  For me, I was querying on a field with a space in the name.

I should have known this!

http://www.delphi-ts.com/blogs/lozzi/post/2009/05/14/CAML-Gotchas-in-SharePoint.aspx

SharePoint 2010 – Dynamically Adding an Event Receiver

Another one I am always looking for:

http://ikarstein.wordpress.com/2011/06/22/walkthrough-add-list-event-receiver-dynamically-at-runtime-in-sharepoint-2010/

SharePoint GetUserProfileService.asmx Field List

I’m always looking for this, so I am posting so I can find it easily next time:

UserProfile_GUID
AccountName
FirstName
LastName
PreferredName
WorkPhone
Office
Department
Title
Manager
AboutMe
PersonalSpace
PictureURL
UserName
QuickLinks
WebSite
PublicSiteRedirect
SPS-Dotted-line
SPS-Peers
SPS-Responsibility
SPS-Skills
SPS-PastProjects
SPS-Interests
SPS-School
SPS-SipAddress
SPS-Birthday
SPS-MySiteUpgrade
SPS-DontSuggestList
SPS-ProxyAddresses
SPS-HireDate
SPS-LastColleagueAdded
SPS-OWAUrl
SPS-ResourceAccountName
SPS-MasterAccountName
Assistant
WorkEmail
CellPhone
Fax
HomePhone