Spent way too long figuring this out today and thought I’d do an entry so that I’d remember it. To set the stage my team manages emergency situations and restoration of service for over 800 enterprise applications. As you might expect we have a SharePoint list of these applications with basic information about them.
One of the values is a unique enterprise-wide code that we’ll call the “AppCode”. One of the first things we do when a problem is reported is find that AppCode code for the application and search the system of record for detailed information about the application, the support, ownership, etc. This isn’t difficult but it does take precious seconds. Instead my plan was to create a link directly to the application profile in our list.
This turned out to be totally impossible but I didn’t realize that until after I had created the links and creating the links is all we’re worried about here. In this case AppCode is a column in our list and our URL is pretty simplistic:
http://www.aaa.com/AppInfo?AppCode=[AppCode]
You would think that this would be a perfect job for a calculated column, but SharePoint doesn’t allow you to calculate a hyperlink field (although there are programmatic ways around this). Instead I decided to export the list to Excel, generate the links there and then import the new column back into the list.
While links aren’t much easier to manage in Excel creating them is made significantly easier with the HYPERLINK function. Assuming that the AppCode is in column “A” (and assuming that we want the visible part of the link to be the AppCode) the formula to create our link would look like this:
=HYPERLINK(CONCATENATE("http://www.aaa.com/AppInfo?AppCode=", A2), A2)
Auto-filling this formula into a new column (note that the cell type cannot be “text”) will generate all of our links in a single sweep. We need to create a new column in our SharePoint list of type “Hyperlink or Picture” to hold our links.
(For those of you, like me, that can never remember exactly how to autofill since you only use Excel once or twice a year: select the cell with your formula in it. It will be highlit with a black band and small grab handle in the lower, right-hand corner. Grab that handle and drag and the selected cells will autofill. Also the autofill flyout menu will appear next to the drop point.)
Editing hyperlink fields in SharePoint is a pain. In the normal editor there are actually two fields, one for the link title and one for the link itself. Editing long lists in this way is a tedious nightmare. Luckily the datagrid view makes our current task much simpler. You might need to add your new column to the current view (or generate a new one) if it’s not being displayed but you must make sure that the view you’re using matches the view (order and number of items) of the one used to generate the Excel file.
Assuming all is ready simply select and copy all of the links in the Excel column, select the first cell of the column in the SharePoint DataGrid and paste. Your links will autofill into the column and work as expected. In my case now that the initial bulk load is complete I can easily keep up with the infrequent changes manually.
