How to create a custom ID for lists and libraries

In this example I demonstrate how to create a simple solution that allows users to submit a request to a list, then a workflow fires that updates the request ID field with our custom ID number.

ID numbers, reference numbers, ticket numbers…this is something that regularly gets asked to be a part of any SharePoint solution or request based system. My first thought when this is required is “easy, we can just use the SharePoint item ID column and use that”. However, creating a simple calculated column that leverages the in-built ID column is not as easy as it seems.

My first attempt at creating a custom ID column involved creating a new calculated column, and appending some text before the ID and then inserting the ID column into the formula, like this:

="REQ-00"&[ID]

The problem with this approach is that when new items are added, the ID appears to “slip” resulting in the custom ID column having no ID number being pulled from the SharePoint ID.

Custom ID column – modern SharePoint

1. Pre-requisites

Before you begin you will naturally need to create either a list or library in SharePoint, and the relevant apps checked as part of your O365 license.

2. The setup

  • Create a new column, with the type Number – I called this ‘solIncrementNum
  • Create a new column, with the type Calculated – I called this ‘solReqNum‘, later renamed ‘Request Number’
    • In the formula field, add the following: ="SOL-00"&[SolIncrementNum]
    • For the Data Type, select Single line of text
Setting the formula for the solReqNum column in list settings

NOTE: for the Request Number formula if you want to prefix your custom ID with something else just replace what’s between the ” “ in the formula field above.

3. Build the Flow

Flow action: when a new item is created

  • Create a new flow from the template “when a new item is created, complete a custom action”
  • Give your Flow a name, I called mine “Populate Solution Request Number”
  • In the “when a new item is added” step, make sure the site address and list name are the same as the list you built the custom ID column for earlier
Step 1 of the Populate Solution Request Flow

Flow: update item

  • Press + New step, start typing “update item”, select the update item action from the selection
  • Select the site in question, then copy and paste the List Name from the previous action
  • Make sure this action has the following fields set:
    • Id: ID
    • Title: Title
    • solIncrementNum: ID

NOTE: make sure that when you set these fields, that the values you use are coming from the “when a new item is created” action.

Step 2 of the Populate Solution Request Flow

Now when new items are created within the list or library, the flow will fire and create a new request number.

Modern SharePoint list with Flow that populates Request Number

How to set the default value of a lookup field

(this post was written using a SharePoint 2010 environment)

If you are using lookup columns in your SharePoint environment, one thing you may want to do is set the column that appears first, or is the default value in the lookup list when a document is uploaded into a library.

Now this solution is not a no code solution, but it requires very little code and is really straight forward to read and understand…

Step one – prerequisites

My setup to achieve this contained the following:

  • A custom list with each lookup value as a seperate list item (name stored in Title column)
  • A document library with a lookup column looking at the title in the custom list

Step two – configure the edit form

  • Open the list which is using the lookup column, make a note of the display name of the column

NOTE
This caught me out while configuring, I naturally took the field name of the column from the URL string as opposed to the column name displayed in the edit form, make sure you use the latter as the field name won’t work.

  • Navigate to the lookup list where the lookup column is pointed to
  • Open the item in this list that you wish to be the default value
  • Open an item in a new window, in the address bar look for ID= and make a note of the number value as this is used for later
  • Navigate to the libraries edit form (just add /Forms/EditForm.aspx after the name of your list or library)
  • Press Site Actions > Edit Page
  • Add a web part > add a content editor web part to the page
  • Click inside the content editor web part > Under Editing Tools > Format Text press the HTML, Edit HTML Source button

Step three – add the code

  • In the HTML Source window, paste in the following:
<script src="https://code.jquery.com/jquery-1.11.0.min.js"> </script>

<script type="text/javascript">

$(document).ready(function() {

$("select[Title='lookup']").val('1');

});

</script>
  • You will need to change the [Title=’lookup’] to be the name of the lookup column as described earlier
  • You will also need to edit the number after .val to be the ID number of the lookup list item we made a note of earlier
  • Under the Page Tab > Press Stop Editing, make sure to save your changes

Test your changes!

Now when you upload a new document, your lookup column will default to the value we have specified!



How to group by date chronologically for library views

(this post was written using a SharePoint 2010 environment)

So here’s the scenario, we have a document library that contains agendas, minutes, reports and other files related to meetings. The document library has a custom column called ‘Meeting Date’, a date column that is mandatory for all files that are uploaded.

We are required to create a view that is grouped by meeting date, sorted chronologically. Let’s give it a go!

Step one – prerequisites

My setup to achieve this was as follows:

  • A document library
  • An additional date column ‘Meeting Date’

Step two – configure the library

  • Navigate to the document library
  • Create a new calculated column called ‘Month of Meeting’
  • In the Formula section, add the following:
=IF([Meeting Date]="","Empty",("0"&MONTH([Meeting Date])&"/"&RIGHT(YEAR([Meeting Date]),4)))

NOTE
This formula basically states that if Meeting Date is blank, show Empty, if the meeting date has a value format it as MONTH (0 in front of the month number) / YEAR (yyyy).

  • Make sure the data type returned is ‘single line of text’
  • Press OK

Test your changes!

Now we have created a calculated column, we can test our changes by adding the column to a view and checking the Month of Meeting column has values.

Step two – create the view

Now we have verified our formula is working, we can start building out our view based on the requirements.

  • Navigate to the document library
  • Under Library Tools > Library > Create View
  • Scroll down to Group By, then group by Month of Meeting in ascending order
  • Press OK

NOTE
In the Group By section, you can set whether the groups are open or closed by default by choosing either collapsed (closed), or expanded (open).

You can also change the default number of groups to display from 30, but beware of slower page loading if you set it too high.

Now our view is configured we can take a look at it in all its chronological glory!

If you want to find out more about using group views, take a look at another post I wrote on ways around the 10 item limit in choice columns.

Problems creating list or library views based on created date

The situation

Data retention and deletion…I’m sure this is a something that anyone involved in Office 365, SharePoint on information management in general gets fed up of saying since the recent GDPR legislation!

Recently we have been rationalising and cleaning up our data in preparation for moving to Office 365. We are starting with SharePoint as the first target repository or silo of content.

The general consensus is to delete files and folders over 7 years old unless there is a pre-existing data retention policy to adhere to. So the next task is to identify those files that fall within our threshold, and ultimately delete.

Luckily, we have Tree Size Pro and ShareGate so I was able to relatively easily identify the files in question (there were a lot!).

The setup

As our SharePoint environment is a) rather full; and b) rather old, I made the decision to incrementally delete files rather than en-masse to mitigate risk, targeting the lists/libraries containing the most out of date content. I started by creating a view in the first library – library A with the following parameters:

  • Standard library view
  • Filtered by Created Date if less than or equal to 01/01/2011
  • Folders or Flat: Show items inside folders
    Show this view: In all folders

(all other settings are left default)

Results this returned looked good, I could see folders and files in this view that matched the criteria – brilliant! Based on my previous statement I decided to delete in batches out of working hours, again to mitigate risk. I deleted first from library A, then from the first stage and finally from the second stage recycle bin all in this fashion.

The problem

I had permanently deleted around 50% of the total volume of content to be deleted from library A when we started to receive reports of current files being ‘missing’ from library A…not a good day.

After these reports were investigated they were indeed true. It turns out that when folders are included within a library view, folders that match the filter will be shown in the view, regardless of whether the files inside match.

We tested the view exluding folders and all the files returned matched the filter criteria. The same results were demonstrated from a SharGate report of the same nature. The report of all files over 7 years old brought back folders over 7 years old, but they also contained files that were newer.

Conclusion

At present, we are not entirely sure as to why these filters are not able to drill down past a top-level folder. It appears to be difficult to specify via view settings to only show files within folders, including the folder itself that matches the criteria.

We have decided to omitt folders from our reports and views going forward and to solely focus on files as this is the most reliable way we can delete files.

Bonus: for those of you with ShareGate, heres an example of my report we created to bring back all files over 7 years old, excluding folders. I ran this report across the entire intranet application over a weekend and it worked a treat 🙂

SG-report

Fix pages with no publishing options in SharePoint

(This post was written using a SharePoint 2010 environment)

So you’ve got a SharePoint site, it all looks good (well, as good as it can!) but you notice that the Publish tab isn’t available in the ribbon.

First things first you check the site settings to see if SharePoint Server Publishing is turned on.

SP2010-publishing-feature.png

If you get to this point and your still no further forward it’s likely that your site wasn’t set up as a publishing site, but if you follow the steps below and your pages will be able to be published in no time.

  1. Open the site in question, then go to Site Actions > View All Site Content
  2. Open the SitePages library
  3. Under Library Tools > Library, select Library Settings

    SP2010-librarysettings
  4. Under General Settings > Versioning Settings, turn on Create major and minor (draft) versions > press OK
    SP2010-versioning

  5. Go back to the original page, you will now see the publishing tab has appeared!

Ways around the 10 item number order limit in choice columns

I was recently updating a view in a SharePoint List, the view was set up to use metadata fields to sort and group the content…lovely stuff. What I was required to do was to implement a choice field with a numerical order within it (i.e. 1. First step, 2. Second step, 3. Third step).

With sort order in List/Library views, it works with either alphabetical or numerical options ascending or descending. What I found was with choice fields operating as the number order, once you hit 10 the numbering system went out the window!

What you end up with is something like this:

1) First choice
11) Eleventh choice
12) Twelfth choice
2) Second choice
3) Third choice

and so on…

By default, SharePoint interperates the choice field as alphabetical so the way I got around this was to just use:

a)
b)
c)

This gets around any issues with numerical values over 10 or having to create lookup lists or anything else 🙂

How to find open with explorer in modern SharePoint libraries

Explorer view in classic SharePoint sites has been a widely used bypass for users actually interacting with SharePoint libraries for a number of years.

Now in some cases that’s for good reason, from being able to upload multiple files easily in older versions of SharePoint to a familiar navigation of nested folder structure.

With modern SharePoint libraries, the old school ribbon has gone the way of the dodo…and so it seemed had open with explorer.

But fear not! If you still use IE you can still use the trusty open with explorer

How to open with explorer

  • Go to the library that you wish to open with explorer
  • On the right-hand side, press the drop-down icon next to all documents
    all-documents2.jpg
  • Press View in File Explorer
    file-explorer

Some weird stuff will then happen, where a classic 2013 version of your SharePoint site/library will open in a new tab and for me I got a message at the bottom of the browser window to allow popups from Microsoft then got another, more serious popup like this (multiple times):

IE-security

I pressed allow to all of these then voila! we have file explorer!

PLEASE NOTE:

File explorer only works for Internet Explorer, I tested in IE11 and it categorically doesn’t work in Firefox, Chrome, Edge or Edge Dev (beta).

Creating a new item from SharePoint Workflow – trailing space issue

I wanted to post this as I was banging my head against a brick wall for hours recently with this issue. Full credit goes to this stack overflow thread that described pretty much exactly what I was experiencing:

However, my situation was a little different so thought I would post this in case it helps someone else in future!

The details

SharePoint 2010 environment using a standard SharePoint Designer workflow. Straight-forward workflow that runs once a list item is created, creates a document set in a separate library, then updates the list item with a link to the document set (plus some other item metadata). All this is wrapped up in an impersonation step that uses a site collection admin user account.

Why an impersonation step?

An impersonation step is being used because when you create a new list item using a workflow, that new item is created by the System Account. So on any new item created by a workflow, “Start workflow on create” will not work because system account is not allowed to start a workflow.

The problem

Intermittently the workflow would fail on creating the document set. The workflow status showed “The workflow could not create the list item. Make sure the list exists and the user has permissions to add items to the list.”. The outcome is “Unknown Error”.

After speaking with users this didn’t seem to affect everyone all of the time. It only affected some people occasionally but it was affecting one user way more than others.

This user was creating the list item in exactly the same way as others in the team. I overserved the user creating the list item and couldn’t see any problems with how it was being done. Yet each time we conducted a test copying field information from an existing item the workflow continued to error.

The solution

In my case, when I started to look at the list items where the workflow had failed, I noticed that the Name field for each list item contained a trailing space at the end of the text. I asked the user who this issue consistently affected to try creating new list items several times, copying the Name as normal but this time removing the trailing space and we no longer had issues!

Moral of the story…check for trailing spaces and beware!

Fun with PowerApps part 4: Creating a custom PowerApp

This is the final part in my fun with PowerApps series, in this post i’ll go through the steps involved in creating a new PowerApp from scratch and highlight some of the difference with using a template or generating a PowerApp from a SharePoint list.

You can read all the other parts in this series below:

Part 1: setting up the default gateway
Part 2: creating a data connection
Part 3: my first powerapp

Creating a PowerApp from scratch

So far we have generated our very first powerapp from a SharePoint list or library, but now we want to take it a step further and create a brand new PowerApp from the beginning to enable us to create a desktop experience.

To create the bespoke PowerApp we will need to begin in the PowerApps studio, the steps below walk through creating the bespoke PowerApp:

  • Navigate to https://web.powerapps.com/
  • In the Home screen, select Start from blank – make sure the desktop view is selected – Press Make this app

The PowerApps studio will give you some useful tips on how to create forms or galleries which are definitely worth checking out if you haven’t already!

  • On the canvas, press Connect to data
  • In the Data window, select the data connection we created earlier

  • Enter the URL of the SharePoint site you want to access
  • Once entered, you will see all the available lists and libraries you can use as the primary data source, select the one you wish to use

Conclusion

And there you are your custom PowerApp is all set up and ready to go! Of course this is just the very beginning and you now have the fun bits to come like adding all of the elements that will make up your PowerApp but from my experience I think there are a few approaches to achieve this.

My idea was to create and open up the template helpdesk PowerApp side-by-side with my custom one to get a better understanding of how to re-produce things like the totals across the top and some of the finer details that make up the PowerApp. I think doing it this way is much easier than trying to pick apart the templates as you are starting with your own data source rather than the static ones provided within the templates.

As well as the desktop and mobile experiences you now have the ability to create list forms using PowerApps too, so now there is even more fun to be had with PowerApps!

Source: https://docs.microsoft.com/en-us/powerapps/customize-list-form

Thanks for reading 🙂

Conditionally show or hide fields within a custom list form based on user permissions

InfoPath, I really like InfoPath. I like the interface and how easy it makes editing custom list forms in SharePoint (especially if you want to make snazzy looking forms in SharePoint 2010). However, custom actions do not like InfoPath, not one bit.

The problem

Here’s the situation, we have a heavily customised custom SharePoint 2010 list which was leveraging InfoPath based forms. We were getting reports of latency when trying to open the InfoPath forms and the decision was made to revert back to the default ones.

Also, another requirement was to have a two edit forms for this list, one set as default with several fields omitted for end users – another one for administrators of the list. Once the edit forms were created I embarked on my journey of creating a custom action that would open the administrator’s edit form from the ribbon, utilising rights masks to make it only appear for those with adequate permissions.

I quickly found that this wasn’t going to work. I tried several times to create the custom action but it just wouldn’t appear. Even after reverting the list back to the default forms from InfoPath through list settings and deleting the InfoPath forms from server the custom action wouldn’t show. I found some useful conversations about this issue below:

After I realised the custom action approach wasn’t going to work, I made the decision to go down the route of having one custom edit form that would conditionally show or hide fields based on their permissions – the answer I found was within a XSLT conditional if test!

The solution

Here are the steps taken to hide a field based on a user’s permissions within a custom edit form:

  • Open the list you want to edit in SharePoint Designer
    SharePoint Designer homepage
  • In the Forms section, open your custom edit form
  • Switch to Advanced Mode
    advanced mode sharepoint 2010
  • Use the design view and select the field you wish to hide
    split view sharepoint 2010
  • In the code view, add the following code snippet above and below your field:

<xsl:if test="ddwrt:IfHasRights(2048)">
</xsl:if>

  • The end result should look something like this:
  • Save the changes

If this has worked, you should now be able to test the edit form as a user with the correct permissions and see the field, then verify that for a user without the relevant permissions it’s hidden.

Within the if test, the number corresponds to a permissions mask that assigns a particular value (i.e. 2048 = Manage Lists).

Here is a list of all the values and permissions masks:

PermissionPermission Mask
ViewListItems1
AddListItems2
EditListItems4
DeleteListItems8
ApproveItems16
OpenItems32
ViewVersions64
DeleteVersions128
CancelCheckout256
PersonalViews512
ManageLists2048
ViewFormPages4096
Open65536
ViewPages131072
AddAndCustomizePages262144
AppleThemeBorder524288
ApplyStyleSheets1048576
ViewUsageData2097152
CreateSSCSite4194314
ManageSubwebs8388608
CreateGroups16777216
ManagePermissions33554432
BrowseDirectories67108864
BrowseUserInfo134217728
AddDelPrivateWebParts268435456
UpdatePersonalWebParts536870912
ManageWeb1073741824
UseRemoteAPIs137438953472
ManageAlerts274877906944
CreateAlerts549755813888
EditMyUserInfo1099511627776
EnumeratePermissions4611686018427387904
FullMask9223372036854775807