SharePoint

Automate Accounts for Azure AD

Azure AD’s B2B capability is a really powerful way to leverage identities from outside of an organization, but is it the right solution for seasonal, temporary, or white listed employees?  Maybe, maybe not, and if not then the creation of cloud only accounts may require a time consuming (possibly manual) request > approval > provision process.

Recently I had a customer that asked how we could automate an account provisioning processes that allow for a request, an approval workflow, automated account provisioning, association of the account with a ‘manager’, an automated actions if the ‘manager’ departed, and time boxing of the account.  In order to minimize development and utilize as much Out of the Box as I could I turned to Flow.

Start with SharePoint

So this is the benefit of experience: I actually started with Flow and discovered the template for Flow, SharePoint, and Azure AD.  Because I started with Flow I didn’t think about what data I wanted to capture first, I just wanted to get accounts creating and would add fields as I needed them.  This lead to some issues, probably because I’m impatient, between adding field and having those available in Flow.  Therefore, I recommend YOU think about the information you need to capture from a user, build your SharePoint list and then proceed.

I decided that I would create a new site for tracking requests and host my request list in this location.  In a real world environment this would allow an organization to have a single account request location which I viewed as valuable.

I created a list as shown below (Title will be used as the last name)

SP List

All fields are Single Line of Text except for Review Status which is a Choice field with Pending, Approved, Rejected as the options with Pending as the Default value.

Create your workflow with Flow

I am by no means a Flow expert, thanks to this demo I learned a little bit, but I really needed a simple place to start.  Fortunately, if you go to Flow select Templates and Search for Azure AD the second template is Create Azure AD User from SharePoint List.

Flow Templates

Once the flow is generated you need to update the first action with your SharePoint site Url and list name.

Flow Item Created

You can skip the second action as this will generate a random password for the account.

Next, you need to update the Create User step based on the fields you created in your list.  You can also use Expressions to customize the values you want to use when creating the user.  For example I use the following to create a username:

concat(triggerbody()['FirstName'], '.', triggerbody()['Title'], '@mydomain.com')

Flow Create User You will also notice that I’ve clicked on the Show advanced options and updated the Business Phone, Department, Job Title, Mobile Phone, Office Location, and Preferred Language.

Account creation will fail if Preferred Language does not meet the specific format.  Business Phone can be an empty array, but cannot accept a null value.
eg. [] – ok
[null] – failure

Next, update the Update item action to set the current item’s ReviewStatus value to Approved.  You will also notice the IsComplete field with a value of true, this field needs to be added to your SharePoint list or else the Update item action will fail.

Flow Update Item.png

Finally, update the Send an email action to utilize the values captured from the list.

Flow Send Email.png

Now you should be able to test you Flow by creating an item in the SharePoint list and observe the execution of your flow, and if there are errors then you can perform troubleshooting and resubmit.

Flow Runs

Add the Review

Now that the creation process is working update the flow to include the actual review phase and condition handling. Add the Start and wait for an approval (v2) action to your flow AFTER the Initialize variable step and configure it as shown.

The Initialize Variable cannot happen within the Condition portion of the workflow, so you may as well initialize this immediately after the flow starts.

Flow Wait for Approval

Next, add a Condition action to your flow.  Update the Condition to use the Outcome of the Start and wait for an approval outcome to be equal to ‘Approve’.

Flow Condition

Finally, move (yes drag and drop does work) the Create User, Update Item, and Send an email actions into the If yes segment of the workflow. You should also add a Send an email to the If no segment of the workflow and send the user a notification that their request has been rejected.

Flow Condition Branches

I recommend testing again to make sure your approval process works as expected, and be sure to test both the Approve and Reject.

Collect Requests with Forms

Now that our flow works we need to set up a way for people to submit requests to be reviewed and approved/rejected.  Microsoft Forms is a simple way to create the request form you need and allow it to be shared outside of your organization.

Creating a Form is really easy so I won’t provide the full details, but create a new Form that captures the same information that the SharePoint list stores.  Don’t include the workflow type fields like Approval status and IsComplete field of course.  Here is an example of the Form I created.

Form Example

As you can see I provided friendly names for each of the user input fields and marked everything as required.

Now you need to allow this Form to be accessed by anyone with the link.  To do this click on the Share button in the upper right of the browser window and select the Anyone with the link can respond.  This will allow you to copy the URL and send it to any external participants.

Form Share

Tie this all together

The final part is to pull our Form submission into our SharePoint list, and again we go back to Flow for this and use an existing Template.

Form Flow

After creating the new Flow from the Template you need to customize the When a new response is submitted Action and select the form you just created.

Form Flow New Response

In the Apply to each action update the Get response details and select the form you created.

Form Get response details.png

Finally, update the Create item by selecting the Site Address and List Name, then expand the Advanced Options so that all the fields from your list display.

Form Create Item

Save your flow, and go test your solution from Flow to Account Creation.

Wrapping Up

You should now be able to share your Form with people outside of your organization, have them submit the form, record the entry in SharePoint and have the Approval process kick off and the account creation be performed.

There are lots of Flow templates and clearly the Approval process doesn’t specifically require SharePoint to store the item, so there are probably hundreds of ways to approach this problem.  However, I like this method because I can see the data move from Forms to SharePoint to Azure AD and creating tracking and report solutions are easy.

How to Fix the SharePoint Modified Date to use Date and Time

If you’ve started using SharePoint 2013 you probably noticed that Microsoft changed the way they present the Modified value.  In SharePoint 2010 the Modified field showed a Date/Time stamp, but in SharePoint 2013 the Date/Time is replaced with something like “3 minutes ago”.  Personally I like this, but some users may not and the Date/Time stamp may be really important especially for Legal Departments or other Audited applications.

Fortunately there is a very simple fix, using the Client Side Rendering to override SharePoint’s default rendering of the Modified field.

The script is simple enough:

(function() {
var overrideCtx = {};
overrideCtx.Templates = {};

overrideCtx.Templates.Fields = {
‘Modified’: {
View: function(ctx) {
//var utcDate = new Date(ctx.CurrentItem.Modified);

return “<span>” + ctx.CurrentItem.Modified + “</span>”;
}
}
};
SPClientTemplates.TemplateManager.RegisterTemplateOverrides(overrideCtx);
});

As I’ve discussed in other blogs(here, here, and here) on CSR we use our self executing function and within it we create the Override Context (overrideCtx) object along with it’s Templates and Fields properties.  Within Fields we specify which field, Modified, and what form, View, we are managing the rendering for.  The nice aspect of this is that we can override the rendering on one or many forms and rely on SharePoint to handle the rendering for the forms we don’t want to override.

In the function for our form we simply return an HTML formatted string that should be displayed.  Using the browser developer tools you can view the argument passed to our View function as well as the CurrentItem’s properties and determine what value to display.  In the case of the ‘Last Modified’ Date/Time stamp the property we want to use is Modified.

Finally, in order to make SharePoint use our override template we pass our Override Context object to the RegisterTemplateOverrides and then add a reference to our JavaScript file to the Display Form’s JSLink property.

Client Side Rendering of Form Fields

I’ve been working on a site migration from SharePoint 2010 to SharePoint 2013 and a lot of what we did in 2010 was to customize forms the user interacts with.  One of the requirements we had was to ensure a Project Name and Project Alias field did not contain the same information.  In SharePoint 2010 this had been done by adding some JavaScript to the page, grabbing each control by HTML Element ID, and then comparing their values.  When we migrated these fields all got new HTML Element IDs so our validation logic was broken.

Obviously, we could go and update the JavaScript to use the new IDs, but if we migrated to another environment or performed and upgrade in the future we would probably have an issue again.  Instead SharePoint 2013 added Client Side Rendering which allows us to control the form’s rendering using JavaScript which would should allow our solution to be more portable and enable the field validation.

I have written a couple of other blogs about the Client Side Rendering of a View and Rendering of Specific Field(s).  While those were useful I found I was still missing a lot of information about how we could add the validation, what was needed to make sure values provided in the form were saved, and how to make the controls interact.

Before we begin a word of caution:  Once a Field Template is provided the template must provide the UI Controls for the user to interact with, a method to retrieve the value specified by the user, a method of displaying the previously specified value, and any validation INCLUDING if the field is required or not.  The example below will include elements of all of this, but your implementation may differ based on your scenario.

First, remember that we need to write good JavaScript that does not pollute the global JS space, so remember to wrap everything in your self executing function.

(function() {
//overrideContext code below
})();

Second, we are going to specify an override on the Edit Form for our specific fields, you may also want to do this on a new form but in our case the Project Alias was not available on the new form so we didn’t worry about it there.  Remember once we provide the override for the Field we are fully responsible for the type of input(s)/controls the user will have to interact, retrieving the value for SharePoint, and performing any ‘Required’ field validation along with any other custom validation we may want.  In order to provide the override in the proper format we must create our OverrideContext object with the proper properties.

var overrideCtx = {};
overrideCtx.Templates = {};

overrideCtx.Templates.Fields = {
‘ProjectAlias’ : {
EditForm: function(currentCtx) {
}
},
‘ProjectName’: {
EditForm: function(currentCtx) {
}
}
};

SPClientTemplates.TemplateManager.RegisterTemplateOverrides(overrideCtx);

At this point we have now specified that we want to override the ProjectAlias and ProjectName fields when the Edit Form is rendered.  However, since we have not returned any HTML formatted controls from either function if we placed all this in a JavaScript file and added it as the JS Link on the Edit form what would actually happen is the ProjectAlias and ProjectName input control wouldn’t exist.

Focusing on the ProjectAlias Field we can add the following to return an HTML TextBox for the user to make use of.

var ctx = SPClientTemplates.Utility.GetFormContextForCurrentField(ctxCurrent);
var controlId = ctx.fieldName+ “_davidmcwee_control”;

//Get Field Value, Value Validation, and Validation Error handling code will all get added here

return String.format(‘<input type=”text” id=”{0}” name=”{0}” />’, controlId);

Now we have a simple text box that will render because the function provided to EditForm is expected to return HTML that can be directly embedded in the page for rendering.  Also we are controlling the ID and Name attributes of our HTML Element so our validation later will be much easier.  While we have the control being rendered, if you actually try to provide a value nothing will get saved because we haven’t specified how to get the value from the control.  So before we return our HTML string we need to register our “getter” method so SharePoint knows how to get the value for our overridden field.

ctx.registerGetValueCallback(ctx.fieldName, function() {
return $get(controlId).value;
});

$get is a SharePoint provided function which return the HTML DOM object and is similar to the jQuery $(“#” + controlId).  I’ve used only the SharePoint functionality here to prevent confusion with other JS libraries.

Important thing to note:  The first argument to the register function is the Field’s Name, if you use any other value your function will still get called, but the value you return will not get applied to the field it should be associated with.  I originally used the controlId as the first argument but found the values were never saved, it took a lot of searching and trial and error to finally realize the issue.

Now that we can retrieve and save our control’s value we can implement validation.  In order to provide validation we need to create a Validator object that implements the Validate(…) function.

//Added the Field Validator just below the declaration of overrideCtx.Templates = {};
fieldValidator = function() {
fieldValidator.prototype.Validate = function(value) {
var isError = false;
var errorMessage = “”;

if($get(“ProjectAlias_davidmcwee_control”).value == $get(“ProjectName_davidmcwee_control”).value)
{
isError = true;
errorMessage = (“ProjectName and Project Alias are not allowed to be the same.”);
}

return new SPClientForms.ClientValidation.ValidationResult(isError, errorMessage);
};
};

In the above code you can see we did hard code the HTML IDs, while this was done in our dev code we did move to saving each of the Control IDs as properties so they could be retrieved later and the code would be more generic.  However, the idea is that we will get the two controls value’s and compare them, if they match we will set isError to true and provide a message back to the user.

Now we need to add our Validator to the Form’s list of Validators in association with our field and we also need to handle a validation error.

var validators = new SPClientForms.ClientValidation.ValidatorSet();
validators.RegisterValidator(new fieldValidator());
ctx.registerClientValidator(ctx.fieldName, validators);

These lines handle creating a validator set, so you can have multiple validators on a single field, adding the validator we created above to the set and registering the validator with the field.

ctx.registerValidationErrorCallback(ctx.fieldName, function(errorResult) {
SPFormControl_AppendValidationErrorMessage(controlId, errorResult);
});

These lines handle the Validation Callback, but this function is not always called.  This function is invoked when the input fails validation or if the previous attempt to save failed validation.  The nice thing is that all we really need to do, in this case, is pass along the results as well as the HTML Element ID to the SPFormControl_AppendValidationErrorMessage function.  You could consider adding other logic here to clear, update, or add suggestions to the user for possible “good” values if validation fails.  The errorResult object provided two properties errorMessage which is the message you specified, and validationError which is the Boolean value you specified in your Validation object.

Once all of this is added, you now have a functioning form override with field dependencies.  Here is the entire JavaScript file in PDF format for you to view.

Hero Blog Post of the Day

I was working on a project today where I need to dynamically create a MS Word Document, save it to a SharePoint library, and then present the document to the user for editing.  My first attempt was to create an IFrame that would redirect to the document once it was created, but this would only open the document in ‘Read Only’ Mode.

Enter the Hero Blog Post

After a quick search I found this blog post.  DotNetNinja found a javascript function editDocumentWithProgID2 which performs this exact operation and is part of the SharePoint 2010 Core.js file.

Simple!

More About Managed Metadata

I recently had more fun with setting Managed Metadata values in SharePoint 2010.  This time as part of an event receiver during the Item Adding, Item Updating, and Item Updated events.  (There are interesting event ordering challenges which lead to using all of these events, but that is not the focus of this blog.)  The challenge I faced was copying values from a Document Set down to documents that were being created/uploaded to the list.  Normally, using Shared Properties would negate this but because we wanted use to edit the Managed Metadata Values in the Document Information Panel we could not use shared values because the Document Set would immediately overwrite any changes made to the document.  Instead we created an Event Receiver to manually handle the updates from the Document to the Document Set, and from the Document Set to the child documents.

The issue arose that copying the Document Set values to the Document didn’t work.  In the logging we could see our Set function get called and return success, but the values never seemed to stick.  I was using code I’ve previously blogged about:

SPField mmdField = spListItem.Fields.GetFieldByInternalName(“MyMMDField”);
string validMMDString  = mmdField.GetValidatedString(spListItem[“MyMMDField”]);
newSpListItem[“MyMMDField”] = validMMDString;

Since logging was unable to provide enough information I began testing using PowerShell.  Which looked something like:

$field = $listItem.Fields.GetFieldByInternalName(“MyMMDField”)
$validString = $field.GetValidatedString($listItem[“MyMMDField”])
$newListItem[“MyMMDField”] = $validString
$newListItem[“MyMMDField”].ToString()
>> ouput the same as the $validString
$newListItem.Update()
$newListItem[“MyMMDField”].ToString()
>> [Empty]

Interesting, before the Update() my value is there but after I loose the value.  After looking around a bit I ran across this blog, and although it was about using the web services it provides a key piece of information: Managed Metadata Fields have a Lookup Field AND a Note field.  I began exploring the various fields, again through PowerShell, and was able to identify the Note field associated with my Managed Metadata Column.  This value looks almost identical to what is in the Managed Metadata Field but does not include the WSSID.

Example:

Taxonomy Field:
123;#My Term|00000000-0000-0000-0000-000000000000;#124;#My Other Term|00000000-0000-0000-0000-000000000001

Note Field:
My Term|00000000-0000-0000-0000-000000000000;My Other Term|00000000-0000-0000-0000-000000000001

At first it appeared at the Managed Metadata Note Column’s Internal name was the Managed Metadata Column’s Column GUID with the ‘-‘s removed.  That worked great for 2 of my 3 fields, but one field didn’t use that same pattern.  Using PowerShell I printed the Managed Metadata Field Definition to the console and discovered a key property: TextField.  This field is a GUID that stores the (MSDN Article Here) Note Field’s GUID and allows you to get the Manage Metadata’s associated Note field.

Finally, once I began setting both the Managed Metadata Field AND the Note field all of my Managed Metadata values would successfully populate.

Final Code
Note: I did not include checks to ensure cast of the field to Taxonomy Field was successful and I’m also assuming that spListItem and newSPListItem share the same columns.

SPField mmdField = spListItem.Fields.GetFieldByInternalName(“MyMMDField”);
TaxonomyField taxField = mmdField as TaxonomyField;

string validMMDString  = mmdField.GetValidatedString(spListItem[“MyMMDField”]);
string mmdNoteString = spListItem[taxField.TextField].ToString();

newSpListItem[“MyMMDField”] = validMMDString;
newSpListItem[taxField.TextField] = mmdNoteString;
newSpListItem.Update();

Setting Managed Metadata

I recently had an issue trying to set the Managed Metadata field value on a new list item I was creating in C# code.  In the control where the user could select the managed metadata value I was using the OTB Taxonomy Control which was properly bound to my Managed Metadata Field.  From that I would then retrieve the ‘Value’ (Label|Guid) of the Term selected and call the TaxonomyFieldValue PopulateFromLabelGuidPair.  This would successfully retrieve a TaxonomyFieldValue and I would this use this as follows:

TaxonomyFieldValue mmdFieldValue = new TaxonomyFieldValue(string.Empty);
mmdFieldValue.PopulateFromLabelGuidPair(mmdFieldControl.value);
item[field.Id] = mmdFieldValue;

No exceptions were thrown, but when reviewing the list item the Managed Metadata Value was not present. I added the following lines to determine what value was actually in my mmdFieldValue variable being used to set the item’s field:

string validatedString = field.GetValidatedString(mmdFieldValue);
string validatedString2 = field.GetValidatedString(mmdFieldValue.ToString());
string justString = mmdFieldValue.ToString();

What I noticed was the validatedString2 was usually an Empty string, while the validatedString was populated, but included a leading integer. The justString usually had a value of [Label]|[GUID], as expected.

With some experimenting what I found was the validatedString, no matter what value I selected for the field always had the SAME leading integer ‘0’. This was the key to resolving the issue, and what I found was the TaxonomyFieldValue object has three properties: Label, GUID, and WssID.  The WssID was the key, it’s value was also always 0 so I tried changing it to a -1 and suddenly all my Taxonomy Values worked.

Final Code:

TaxonomyFieldValue mmdFieldValue = new TaxonomyFieldValue(string.Empty);
mmdFieldValue.PopulateFromLabelGuidPair(mmdFieldControl.value);
mmdFieldValue.WssId = -1;
item[field.Id] = mmdFieldValue;

Fixing IE11 Enter Key Problem with Search

I was recently working for a client who began having issues with the Windows 8.1 baseline and their search center.  If you are familiar with Windows 8.1 then you are aware that it comes with IE 11 which has a new User Agent string that can cause some issues with SharePoint (ref: IE11 Broke SharePoint 2010).  My task was to figure out why when a user entered a search term/key word and hit enter the page simply refreshed.

I tried several ideas, including attaching to the form submit action, to no success so I began searching the web for any other possibilities I had not though of.  Interestingly, I began running across several similar issues with Firefox from back in the SharePoint 2007 days.  After doing a little reading I began experimenting using IE11’s developer tools and found it has a similar issue to Firefox from years ago.  Here is the problem:

SharePoint’s Keyword Input has a function that does the following check

if(event1.which == 10 || event1.which == 13) { …<submit the form>; return false; }

The problem is that IE 11, when the IE-8 Compatibility Meta Tag is present on the page, returns undefined for the event1.which and therefor our submit function is never called.  Several people had this same issue with Firefox and there were lots of examples on how to resolve, but most required you hard coded the <submit the form> function name, which may or may not be consistent from site to site or even page to page.

I wanted something a bit more dynamic, and what I realized was if I grabbed the Submit function from the actual search button I could just call it directly.  In addition, rather than adding another chained function to the keypress DOM event, which could be bypassed if the event was canceled, I wanted to override the current function with my own definition.

Here is the resulting code I have used which works in IE 11, Firefox 27.0.1, and Chrome 33.0.1750.154:

function ensureReturn()
{
var jsFunction = $(“input[name=’InputKeywords’]”).attr(“onkeypress”);
var jsSplit = jsFunction.split(‘ ‘);
var funcName = jsSplit[jsSplit.length-1].split(‘(‘)[0];

jsFunction = $(“a[title=’Search’]”).attr(“href”);
jsSplit = jsFunction.split(‘:’);
var submitFuncName = jsSplit[jsSplit.length-1];

var newFunc = funcName + ” = function(event) { if(event.keyCode == 10 || event.keyCode == 13) { ” + submitFuncName + “; try { event.cancelBubble = true; } catch(e){} return false; } };”
//newFunc should be something like:
//function SFBE88B03_OSBEK(event1) { if(event1.keyCode == 10 || event1.keyCode == 13) { SFBE88B03_Submit(); try { event1.cancelBubble = true; } catch(e){} return false; } }
eval(newFunc);
}
_spBodyOnLoadFunctionNames.push(“ensureReturn”);

Once that was placed in a JavaScript file and included on the search and results pages the enter key began working again.