SharePoint 2016 Copy Shortcut Context Menu

 Computer, Sharepoint, Software  Comments Off on SharePoint 2016 Copy Shortcut Context Menu
Dec 082018
 
Share

After an upgrade from SP2013 to SP2016, the ability to right-click on a document and copying a shortcut was removed. This is because the IE right-click menu has been replaced by a context sensitive menu that does not include an option for copying a shortcut to the document. The following code will put that back…

// Add "Copy Shortcut" option to document context menu
$(document).ready( function () {
// Use setInterval to run this every 100ms
setInterval(
function () {
// Check if a "Copy shortcut" button is already displayed
var n = $('#copyShortcutMenuItem').length;
var m = $('.ms-core-menu-list').length;
if (n == 0 && m > 0) {
var firstMenuItem = $('li.ms-core-menu-item:first-of-type');
var hlink = firstMenuItem.parent().parent().parent().find('div:first-of-type').attr('Url');
if (hlink.startsWith('/')) {
hlink = "https://" + window.location.host + hlink; 
}
// Build the HTML of the new list item
var newHtml = '
 	<li id="copyShortcutMenuItem" class="ms-core-menu-item" type="option" text="Copy Shortcut"><a title="Copy a link to this document to the clipboard" class="ms-core-menu-link" href="#" onclick="CopyShortcutClick(\'' + hlink + '\');">Copy Shortcut</a></li>
';
// Insert the list item
$(newHtml).insertBefore(firstMenuItem);
}
}, 100
);
});
// Set the clipboard and hide the menu
function CopyShortcutClick(t) {
window.clipboardData.setData('Text', t);
$('.ms-core-menu-list').parent().hide();
}

Save this as CopyShortCut.js and put it in your root Site Assets library. Also download the latest JQUERY. Add the following to the <Head> of your master page.

<script type="text/javascript" src="/SiteAssets/jquery-3.1.1.min.js"></script>
<script type="text/javascript" src="/SiteAssets/CopyShortCut.js"></script>
 Posted by at 7:51 am

Access 2013 Crosstab Query with Dynamic Column Headers

 Computer, Sharepoint, Software  Comments Off on Access 2013 Crosstab Query with Dynamic Column Headers
Mar 212017
 
Share

In my scenario, I have a SharePoint 2013 list with Task Name, Assignee, Task Start and Task End. I needed to query the list and parse the data into a pivot table with all dates between the task dates as the column headers. The following steps were pieced together from multiple posts around the Web.

There are easier ways to do this with SQL or ReportBuilder, but I had to use Access in this case. After exporting the list into Access…

First create a Table called “Dual” with two columns: ID, DummyTxt
Populate one row with anything.

Create a Query (qDual) against Dual that will create 9 rows.
Query SELECT 0 AS a
FROM dual
UNION ALL
SELECT 1
FROM dual
UNION ALL
SELECT 2
FROM dual
UNION ALL
SELECT 3
FROM dual
UNION ALL
SELECT 4
FROM dual
UNION ALL
SELECT 5
FROM dual
UNION ALL
SELECT 6
FROM dual
UNION ALL
SELECT 7
FROM dual
UNION ALL
SELECT 8
FROM dual
UNION ALL SELECT 9
FROM dual;

Create a Query (qDates).
SELECT top 90 date() + (a.a + (10 * b.a) + (100 * c.a)) AS MyDate
FROM (SELECT *
FROM qDual) AS a, (SELECT *
FROM qDual) AS b, (SELECT *
FROM qDual) AS c;

This creates 90 days of dates from Today. I am limiting to 90 days out. If you have too many days, the later crosstab query will complain about having too many crosstab headers.

Create Crosstab Query.
TRANSFORM [Staffing].Name
SELECT [Staffing].Name
FROM [Staffing], [qDates]
WHERE [qDates].MyDate between [Staffing].[Task Start] and [Staffing].[Task End]
GROUP BY [Staffing].Name
PIVOT [qDates].MyDate

You can use another column (eg. Task Name) for the Transform. When you run the query you should have something like this.

The first column is today’s date. And though it’s not in the screenshot, the last column is the MAX Task End. In this pseudo-GANTT table, you can see that I am tasked from today and everyday forward. Aquaman has a task starting on 3/22 and forward and Superman has a task starting 4/1 and forward.