Create lookup field with additional columns

When we use lookup fields in SharePoint, we are able to configure the field to show additional fields from the list that is used as the target of the lookup as shown in the following image:

Technically SharePoint will create two fields for this lookup column (or more, depending how many additional columns are selected). When we want to create such a field using the Office 365 PnP PowerShell extensions, we can use the Add-PnPFieldFromXml cmdlet. For each field, we need one xml-definition as shown in the following example:

Main field

<Field 
	Type="Lookup" 
	ID="{890a17aa-8062-4e25-a5f5-d14357b8a704}" 
	Name="MyColor" 
	DisplayName="MyColor" 
	StaticName="MyColor" 
	List="{6a49e685-22d1-4f2f-bca3-eb01efd69613}" 
	WebId="81297a18-0e89-4c75-90f1-91981ee15d5d" 
	SourceID="{81297a18-0e89-4c75-90f1-91981ee15d5d}" 
	ShowField="Title" 
	UnlimitedLengthInDocumentLibrary="FALSE" 
	Required="FALSE" 
	EnforceUniqueValues="FALSE" 
	Group="Tester" 
/>

Secondary field

<Field 
	Type="Lookup" 
	ID="{b2ae0377-9b77-44c2-8b0f-765dda207fbc}" 
	Name="MyColor_x003A_Value" 
	DisplayName="MyColor:Value" 
	StaticName="MyColor_x003a_Value" 
	List="{6a49e685-22d1-4f2f-bca3-eb01efd69613}" 
	WebId="81297a18-0e89-4c75-90f1-91981ee15d5d" 
	SourceID="{81297a18-0e89-4c75-90f1-91981ee15d5d}" 
	FieldRef="890a17aa-8062-4e25-a5f5-d14357b8a704" 
	ShowField="Value" 
	ReadOnly="TRUE" 
	UnlimitedLengthInDocumentLibrary="FALSE" 
	Group="Tester" 
/>

There is a relationship between the attributes in the xml-definitions, as shown in the following image (ignore and do not use the attribute “Version”, as you can see it in the image):

To create the fields, we can use the following script:


function AddFieldToWeb([string]$Path)
{
	$content = Get-Content $Path

	$string = [string]$content

	Add-PnPFieldFromXml -FieldXml $string 
}

AddFieldToWeb .\Fields\MyColor.xml
AddFieldToWeb .\Fields\MyColor.Value.xml

Remember, the xml-definition includes the guid of the list and the web. So it’s necessary to patch these xml-definitions, before they could be used in a deployment scenario.

Advertisements

Create fields from XML for SharePoint Online

With the Office 365 PnP PowerShell extensions we have a very simple way to create new columns with the cmdlet Add-PnPFieldFromXml. The xml to use with this cmdlet could be taken from the Field-Element schema: https://msdn.microsoft.com/de-de/library/office/aa979575.aspx. So, to add new fields we just need to build the xml for the new column. Next some examples, tested as site columns:

Text field

<Field 
	Type="Text" 
	DisplayName="Document Title English"
	Description="Document Title in English"
	Required="TRUE" 
	EnforceUniqueValues="FALSE" 
	Indexed="FALSE" 
	MaxLength="255" 
	Group="Demo" 
	ID="{161ef8f6-e73c-4c56-8a5f-c6a8900f2fc8}"
	StaticName="TitleEN" 
	Name="TitleEN">
</Field>

Multitext field

<Field 
	ID="{B76B58EC-0549-4f00-9575-2FD28BD55010}" 
	Name="DemoDescription" 
	DisplayName="Description" 
	Description="A summary of the asset" 
	StaticName="DemoDescription" 
	Group="Demo" 
	Type="Note" 
	NumLines="5" 
	UnlimitedLengthInDocumentLibrary="TRUE" 
	Hidden="FALSE" 
	ShowInEditForm="TRUE" 
	SourceID="http://schemas.microsoft.com/sharepoint/v3" 
/>

Number field

<Field
	ID="{f9e31c80-7de8-4577-8df6-d7821aff4bce}"
	Type="Number"
	Name="DSStatus"
	DisplayName="DS Status"
	StaticName="DSStatus"
	Group="Demo"
	Description=""
	Hidden="false"
	ShowInNewForm="FALSE"
	ShowInEditForm="FALSE"
	Min="0"
	Max="2"
	Decimals="0">
	<Default>0</Default>
</Field>

Currency field

<Field 
	Type="Currency" 
	DisplayName="Price" 
	Required="FALSE" 
	EnforceUniqueValues="FALSE" 
	Indexed="FALSE" 
	Decimals="2" 
	LCID="1033" 
	ID="{57717312-4640-4408-93bb-1cb8eabb8f9a}" 
	SourceID="{269f1ad3-0434-4032-8677-99e06e3e6a01}" 
	StaticName="Price" 
	Name="Price" 
	CustomFormatter="" 
/>

Choice field

<Field 
	Type="Choice" 
	DisplayName="Process Group"
	Description="Process Group" 
	Required="TRUE" 
	EnforceUniqueValues="FALSE" 
	Indexed="FALSE" 
	Format="Dropdown" 
	FillInChoice="FALSE" 
	Group="Demo" 
	ID="{cdc426f9-4db3-4d03-936c-e9986ade7254}" 
	StaticName="ProcessGroup" 
	Name="ProcessGroup">
	<Default>Process group #1</Default>
	<CHOICES>
		<CHOICE>Process group #1</CHOICE>
		<CHOICE>Process group #2</CHOICE>
		<CHOICE>Process group #3</CHOICE>
		<CHOICE>Process group #4</CHOICE>
	</CHOICES>
</Field>

Yes/No field (Boolean)

<Field 
	Type="Boolean" 
	DisplayName="ReminderEmailSent" 
	Required="FALSE" 
	EnforceUniqueValues="FALSE" 
	Indexed="FALSE" 
	Hidden="TRUE"
	Group="Demo" 
	ID="{CE1411F7-7B47-4EE3-AD4B-E70AE5FC21F9}" 
	StaticName="ReminderEmailSent" 
	ShowInEditForm="FALSE"
	ShowInNewForm="FALSE"
	ShowInViewForms="FALSE"
	ShowInDisplayForm="FALSE"
	Name="ReminderEmailSent">
    <Default>FALSE</Default>
</Field>

DateTime field

<Field 
	Type="DateTime" 
	DisplayName="Approved" 
	Required="FALSE" 
	EnforceUniqueValues="FALSE" 
	Indexed="FALSE" 
	Format="DateTime" 
	Group="Demo" 
	FriendlyDisplayFormat="Disabled" 
	ID="{2eb9160b-7f84-4f35-9834-5253ee84c292}" 
	StaticName="Approved"
	ShowInEditForm="FALSE"
	ShowInNewForm="FALSE"
	Name="Approved">
</Field>

Person field

<Field 
  Type="User" 
  DisplayName="Approved by" 
  List="UserInfo" 
  Required="FALSE" 
  EnforceUniqueValues="FALSE" 
  ShowField="ImnName" 
  UserSelectionMode="PeopleOnly" 
  UserSelectionScope="0" 
  Group="Demo" 
  ID="{c4994fe6-24fe-458e-b06a-b9ed9fcb5c39}"
  StaticName="Approvedby"
  ShowInEditForm="FALSE"
  ShowInNewForm="FALSE"
  Name="Approvedby"> 
</Field>

Hyperlink field

<Field
	Type="URL" 
	DisplayName="Review" 
	Required="TRUE" 
	EnforceUniqueValues="FALSE" 
	Indexed="FALSE" 
	ShowInEditForm="FALSE"
	ShowInNewForm="FALSE"
	Format="Hyperlink" 
	Group="Demo" 
	ID="{b30b37aa-c6ea-4e9f-a848-7e251a7571b1}" 
	StaticName="Review" 
	Name="Review">
</Field>

Calculated field

<Field
	ID="{111b0f4c-3135-4a8a-9d7d-d53daaf1931a}"
	Type="Calculated"
	ResultType="Number"
	Name="WorkflowStatus"
	DisplayName="Workflow Status"
	StaticName="WorkflowStatus"
	Group="Demo"
	Description=""
	Hidden="false"
	JSLink="https://ajax.googleapis.com/ajax/libs/jquery/1.11.2/jquery.min.js|~site/Scripts/Fields.JSLink.js">
	<FieldRefs>
		<FieldRef Name="IMStatus" />
		<FieldRef Name="ISStatus" />
		<FieldRef Name="ITStatus" />
		<FieldRef Name="FBStatus" />
		<FieldRef Name="DSStatus" />
	</FieldRefs>
	<Formula>=[IMStatus]+[ISStatus]+[ITStatus]+[FBStatus]+[DSStatus]</Formula>
</Field>

Lookup field

<Field 
	ID="{8e99027f-501b-4902-8ee4-97020d04df74}"
	DisplayName="Company"
	Name="DemoCompany"
	StaticName="DemoCompany"
	Group="Demo"
	Type="Lookup"
	Required="FALSE"
	EnforceUniqueValues="FALSE"
	List="Companies"
	ShowField="Title" 
/>

When defining a lookup field from xml, you must replace the listname (“Companies” in the example) by the guid of the list, before the xml could be used.

To create taxonomy fields, use the cmdlet Add-PnPTaxonomyField instead of the Add-PnPField cmdlet.

When you are not sure, which attributes you need in the xml, simply create the field in a library using the web interface, then execute the Get-PnPField cmdlet as shown in this example:

That’s it, really simple.

Display Site Classification in SharePoint Sites

Today I was thinking about a simple solution to display a classification on a SharePoint site. There is a solution available in MSDN, but for me it was to complex. My solution uses some JavaScript injection that was taken from an PnP sample. This sample in combination with a property in the root folder of a library would be an easy solution.

The JavaScript file would look like this (in my case I called this file ShowSiteClassification.js.

var jQuery = "https://ajax.aspnetcdn.com/ajax/jQuery/jquery-2.0.2.min.js";

// Is MDS enabled?
if ("undefined" != typeof g_MinimalDownload && g_MinimalDownload && (window.location.pathname.toLowerCase()).endsWith("/_layouts/15/start.aspx") && "undefined" != typeof asyncDeltaManager) {
    // Register script for MDS if possible
    RegisterModuleInit("ShowSiteClassification.js", JavaScript_Embed); //MDS registration
    JavaScript_Embed(); //non MDS run
} else {
    JavaScript_Embed();
}

function JavaScript_Embed() {

    loadScript(jQuery, function () {
        $(document).ready(function () {
            // Execute status setter only after SP.JS has been loaded
            SP.SOD.executeOrDelayUntilScriptLoaded(function () { GetSiteClassification(); }, 'sp.js');
        });
    });
}

function GetSiteClassification() {
    var ctx = new SP.ClientContext.get_current();

    // var webProperties = ctx.get_web().get_allProperties();
    var webProperties = ctx.get_site().get_rootWeb().get_lists().getByTitle("Scripts").get_rootFolder().get_properties();
    ctx.load(webProperties);
    ctx.executeQueryAsync(Function.createDelegate(this, GetSiteClassificationSuccess), Function.createDelegate(this, GetSiteClassificationFailure));

    function GetSiteClassificationSuccess() {
        var allProps = webProperties.get_fieldValues();

        var customProp = "";

        //make sure the property is there before using it.
        if (webProperties.get_fieldValues().SiteClassification != undefined) {
            var customProp = webProperties.get_fieldValues().SiteClassification;
            var message = "The classification of this site is: " + customProp;

            switch (customProp) {
                case "Internal":
                    var strStatusID = SP.UI.Status.addStatus("Information : ", message, true);
                    SP.UI.Status.setStatusPriColor(strStatusID, "green");
                    break;

                case "Confidential":
                    var strStatusID = SP.UI.Status.addStatus("Information : ", message, true);
                    SP.UI.Status.setStatusPriColor(strStatusID, "yellow");
                    break;

                case "Secret":
                    var strStatusID = SP.UI.Status.addStatus("Information : ", message, true);
                    SP.UI.Status.setStatusPriColor(strStatusID, "red");
                    break;

                default:
                    break;
            }
        }
    }

    function GetSiteClassificationFailure() {
        var message = "There was a failure getting the web properties.";
        var strStatusID = SP.UI.Status.addStatus("Error : ", message, true);
        SP.UI.Status.setStatusPriColor(strStatusID, "red");
    }
}

function loadScript(url, callback) {
    var head = document.getElementsByTagName("head")[0];
    var script = document.createElement("script");
    script.src = url;

    // Attach handlers for all browsers
    var done = false;
    script.onload = script.onreadystatechange = function () {
        if (!done && (!this.readyState
					|| this.readyState == "loaded"
					|| this.readyState == "complete")) {
            done = true;

            // Continue your code
            callback();

            // Handle memory leak in IE
            script.onload = script.onreadystatechange = null;
            head.removeChild(script);
        }
    };

    head.appendChild(script);
}

To store this file, I created a new library in the root web of my site collection. In this library, I broke the permission inheritance and set read permissions for Everyone except External Users (could also be just Everyone). Then I added the script to the custom actions of the site:

Add-PnPJavaScriptLink -Name ShowSiteClassification -Url https://mytenant.sharepoint.com/sites/site-class/Scripts/ShowSiteClassification.js -Scope Site

Finally, I set the SiteClassification property in the root folder of the library I created.

Set-PnPPropertyBagValue -Key "SiteClassification" -Value "Confidential" -Folder /Scripts

With this solution, the classification is also visible in subwebs, even when the user does not have permissions to the root web.

Title Field in JSLink Scripts

Today I had the need to modify the output of the title field in a SharePoint list view. It’s a perfect task for JSLink. So, created the script, added the file to my SharePoint site and configured the view to use this JSLink file. A straight forward process.

But I was wondering, why my output was not modified. I always saw the value, as it would come without a JSLink modification. Did some debugging and my function was never called.

Thought a little about the title field, and stop. In views, we have three different options to display the title and in view definitions we use separate field names (fieldref).

  • Title (Title only, no link)
  • LinkTitle (Title with link and with “…” suffix to open a dropdownmenu)
  • LinkTitleNoMenu (Title with link to the document or file but no “…” suffix)

Because in my view I used the LinkTitle, but mapped my function in the JSLink to the Title field, it was clear that my function could not be called. Modified the JSLink file to use LinkTitle and everything worked as expected.

For the “lessons learned”: when creating a JSLink script for the Title field, consider all three field names.

Sending Mails with Utility.SendEmail() in SharePoint Online

In the client object model for SharePoint Online we have the Utility class. This class provides a function SendEmail() that (oh wonder) helps us to send emails. In my tests, I found that the recipient must be a member of the hidden “All People” group in the SharePoint site, from where we take the client context. So, to successfully send an email with this function, the code should look like this:


string userMail = "alexw@anytenant.onmicrosoft.com";

Web web = ctx.Web;
web.EnsureUser(userMail);
ctx.ExecuteQueryRetry();

EmailProperties mailProps = new EmailProperties();
mailProps.From = "noreply@anytenant.onmicrosoft.com";
mailProps.To = new string[] { userMail };
mailProps.Subject = "Email Tester";
mailProps.Body = @"<html><body><h1 style='color: red;'>Header</h1><br/><div>Hello world.</div></body></html>";

Utility.SendEmail(ctx, mailProps);
ctx.ExecuteQueryRetry();

Very important for the recipients: they must be a user in the current tenant. In my tests, it was not possible to send emails to external users, even when they have permissions in the SharePoint site of the client context.

Apply site policy automatically on site creation in SharePoint Online

For a site lifecycle scenario we need to set a site collection to read-only. In the (good) old world with SharePoint on-premise that was a simple task. Because we are working with Office 365 and SharePoint Online, it is a little bit difficult, because we want a very high grade on automation.

First of all, there is no PowerShell cmdlet or anything in the client object model that would enable us to directly set a site collection to read-only. But, we have something in SharePoint that is called Site Policy. Such a policy applied to the root web of a site collection could set the state of the site collection to read-only, when the site is closed. Could be a simple workaround, but creating a site policy by code does not seem to be possible in SharePoint Online. But on the other hand, applying a site policy to a site could be done with classes and methods from the client object model. Does that make sense? Not for me.

Looking behind the scenes of a site policy (and reading other articles from Microsoft), a site policy is nothing else than a content type that is created in the web. First idea was to create this content type by code. The client object model has classes and methods to do that, but the configuration of the site policy is stored in the XmlDocument member of the content type and this member could not be set using CSOM or the Rest endpoint. Thanks a lot for making this happen.

Ok, saying goodbye from creating the site policy by code, we still need to create the site policy (or its content type) automatically, when the site collection is created. Publishing content types is one of the main tasks of the Content Type Hub in SharePoint. Having SharePoint Online, the Content Type Hub is automatically made available, when the tenant is created at https://tenantname.sharepoint.com/sites/contenttypehub. So, let’s create the site policy in the site collection of the Content Type Hub.

This will create the content type “Mark Readonly Tester” in this site collection. The content type is hidden by default, so we are not able to do anything via the web ui.

Next create a new site collection in the tenant. Could be a simple Team Site. When this site collection is created, switch to Site Settings and the Site Policy page (found in Site Collection Administration). And what do we see? No site policy. Investigating with the SharePoint Client Browser (many thanks to Bram de Jager!), the content type of the site policy was not published to our new site collection. Perfect, next problem to solve.

I did some really dirty stuff with this content type, and I do not know, whether it is supported by Microsoft, but after making the changes with the following client side code (that makes use of the Office 365 PnP Core), the content type for the site policy was published to a newly created site collection.

Web web = ctx.Web;
ContentType ct = web.GetContentTypeByName(contentTypeName);
FieldCollection fields = ct.Fields;
FieldLinkCollection fieldLinks = ct.FieldLinks;

ctx.Load(web);
ctx.Load(ct);
ctx.Load(fields);
ctx.Load(fieldLinks);
ctx.ExecuteQueryRetry();

ct.Hidden = false;
ct.Group = contentTypeGroup;

foreach (Field field in fields)
{
    field.Hidden = false;
    field.Update();
}

foreach (FieldLink fieldLink in fieldLinks)
{
    fieldLink.Hidden = false;
}

ct.Update(true);

web.AddFieldToContentTypeByName(contentTypeName, new Guid("{9ebcd900-9d05-46c8-8f4d-e46e87328844}")); // field: Categories

web.Update();
ctx.ExecuteQueryRetry();

When a new site collection is created, we can see the site policy in the site settings of the site collection.

When we switch to the Site Closure and Delection page in the Site Administration of the site settings, we can select the site policy.

And when this policy is selected and the site will be closed, the read-only setting will do what it should.

The same result we can get by running this short piece of code that we will now can use in our site lifecycle scenario to set a site collection to read-only.

Web web = ctx.Web;
web.ApplySitePolicy("Mark Readonly Tester");
web.Update();
ctx.ExecuteQueryRetry();

ProjectPolicy.CloseProject(ctx, web);
ctx.ExecuteQueryRetry();

Once again, actually I do not know, whether this is a supported scenario or not, but it will solve the problem to deploy a site policy to a new site collection.