Posted on

How to Alternate Request Assignment to Responsible Persons for various categories

animals birds feathers flamingos

I had a unique challenge recently to alternate assignment of requests (tracked in a SharePoint list) to individuals as they were created. User #1 would get requests 1, 3, 5, etc. and User #2 would get requests 2, 4, 6, etc. To make it more interesting, different pairs of experts might be assigned to different request topics, and sometimes a single individual would be responsible for a particular topic. The overall idea, though, was a somewhat evenly dispersed workload given quickly and efficiently to those who were responsible for those request areas.

One (less than ideal) option to accomplish this is to use a Get Items (SharePoint) step on the requests list with a Top Count of 1 and Order By OData query (Created desc) for the most recently created request, see who the assignee is, and then assign to the other person in that category’s assignee field (when applicable). But I needed something that was better-performing, sophisticated, simpler in design, and not dependent on any historical items in case they were deleted, re-assigned, etc.

So, to determine the assignment as requests randomly trickle in over time, I decided to have Power Automate determine whether a new request’s ID number (auto-generated by SharePoint) is odd or even. Odd requests would go to the first assignee of a subcategory, and even would go to the last. If there was only one assignee in a category, they’re both the first and the last so it still works.

A general good practice when building flows is to make sure you don’t put a particular assignee’s identity explicitly in a variable, task, or email step. For example, you wouldn’t want to put user #1’s email directly in an Outlook email step’s To line. This makes sure that if an employee leaves the company, that the flow won’t need a revision. Instead, I created a list in the team’s SharePoint site with request categories, sub-categories, and responsible persons. My flow refers to this list each time it needs to make an assignment, making sure it has up-to-date contacts for each subcategory.

Example of a request categories list | Click to enlarge

The managers of the process keep this updated with no more than two assignees per subcategory. This way, when new employees join, the list gets an update for what subcategories they’ll be handling, and the flow continues without a hiccup.

What you’ll need to build this solution

Overall, for this process to work, you’ll need:

  • A list of request categories with these columns:
    • Category (choice)
    • Subcategory (text or choice)
    • Assignees/Responsible (person with multi-select enabled)
  • A list of requests (submitted via a SharePoint form, Microsoft Forms form via Power Automate, or Power Apps app via Power Automate)
    • I’d recommend Power Apps if you have the time and resources – this way your choices (categories and subcategories) in the form can be dynamic (only have to be managed in one place) and reference your categories list without granting access to the main request list, or even the SharePoint site, itself
    • Consider adding additional fields for your assignees like Notes, Status, Priority, etc. for them to add additional context, reminders, and status updates as they work the requests
  • The Power Automate flow for assignment determination

How to build a flow that assigns tasks evenly and by category

The following video will walk you through the necessary components to build this flow in Power Automate. The result will dynamically assign tasks to responsible persons based on categories they’re responsible for, as well as alternating tasks to help distribute workloads more evenly within each category.

Ideas to enhance your categories list with additional fields for different assignment scenarios

Consider taking this concept further by adding additional columns to your categories list. Things like:

  • Sequential assignment (Yes/No) – Assign to the first person in the multi-select person field and if they don’t mark it complete within 2-3 days, add the second person as an assignee.
  • Assign to all (Yes/No) – Maybe some subcategories should have all assignees involved in the resolution. A simple checkbox would have the flow assign to both (when applicable).
  • Request specific assignment (Yes/No) – Not everything can be randomly or evenly assigned. You may wish to choose on a case-by-case basis. Set your flow up to send an adaptive card to the process manager(s) asking for selection of an individual for each request that comes in with this Yes/No checked.
Posted on

How to remove the attachments field from a SharePoint list’s new item form

If you’ve ever tried to create a new item on a SharePoint list, you may have noticed that the form always includes an Attachments field that can’t be hidden like other columns. If your process doesn’t require attachments or you’d like to prevent users from uploading documents to the list, you’re likely wondering how to get past this obstacle.

End of a SharePoint form with the Attachments field | Click to enlarge

Note
Be aware that if users previously utilized the Attachments field, this action would delete all attachments they’ve already uploaded on all list items.

In this post, I’ll highlight the 5 simple steps involved in removing the attachments field for a list’s new item form.

How to remove the attachments field from a list’s new item form

  1. Click Settings | List Settings
Location of List settings | Click to enlarge
  1. Select Advanced settings
Location of Advanced settings in List Settings | Click to enlarge
  1. Set the Attachments radio button to Disabled
Advanced list settings showing the Attachments setting as Disabled | Click to enlarge
  1. Click OK
  1. You’ll receive a warning making sure you’re aware that this action deletes all attachments (if there are any). Click OK.
Warning when saving Attachments setting as Disabled | Click to enlarge

And that’s it! Now your forms will only show fields you’ve created and configured.

End of a SharePoint form without the Attachments field | Click to enlarge

If you’re not able to access or modify List settings, you may not have high enough permissions to modify the list. Check with your site owner to see if they can assist.

Here’s a video demonstration of the steps in this post:

When should I use attachments in lists?

Attachments are secondary to the list/form data itself and should be used for specific business processes, such as attaching receipts for reimbursement requests or perhaps photos or screenshots for facility or issue trackers. If the document is independently important, it should be stored in a document library.

Also keep in mind that attachments don’t have version history, cannot be renamed or reorganized without downloading/re-uploading, and they are not searchable in Microsoft 365. If you need to search for attachments, you should store them in a document library instead of a list – and you can always use a lookup column or hyperlink column to connect the list and library.

References and further reading

Posted on

Intro to the ‘Visualize the list’ Power BI feature for SharePoint lists (Recording from Baltimore SharePoint User Group)

I had the honor of presenting one of my favorite Microsoft 365 features for the Baltimore SharePoint User Group’s regular meeting last week. You may have seen this particular feature on your lists (individual or in SharePoint): the ability to visualize a list or library’s data using the Power BI service.

Visualize the list menu option for lists and libraries | Click to enlarge

This newer feature of convenient Power BI integration for lists gives you an automatic report based on your list’s data. We explored how to create a report, customize it, and save it back to the list for future one-click access.

Automatically generated report | Click to enlarge

Even if you haven’t used Power BI (yet), this presentation was made for all experience levels and will provide a proper introduction. In this one-hour session, I specifically covered:

  • How to create an automatic report/dashboard from your list in just a few clicks
  • How to edit and publish the auto-generated report for others to use
  • Site lists vs my lists
  • Limitations of the feature
  • Licensing requirements

Watch the recording on YouTube, or embedded below.

Additional resources

Slides used in the presentationDownload
Posted on

How to do conditional view formatting in SharePoint Online (Video)

Creating conditional view formatting for your list and library views helps your colleagues find what’s important and more easily consume the information presented to them in the list or library. Learn how to configure conditional view formatting in this lesson.

This video is part of my FREE 30+ lesson self-paced online training course called Collaboration in Microsoft 365 (OneDrive, SharePoint, and Teams). Enroll today at https://www.NateTheTrainer.com for the full learning experience including lesson discussions, quizzes, exams, and a completion certificate.

You can also watch the entire course as a YouTube playlist as well (just without the course discussions, quizzes, exam, and certificate). Be sure to subscribe to support my channel and for easy access to future content.

Posted on

How to use columns and views in SharePoint Online lists and libraries (Video)

Views are a great way to create multiple “reports” or ways of viewing the data contained in a SharePoint list or library. For example, you may want one view for managers, and another view of the same data targeted towards everyday users. In this lesson, you’ll learn about columns and how they’re used to create specific views.

This video is part of my FREE 30+ lesson self-paced online training course called Collaboration in Microsoft 365 (OneDrive, SharePoint, and Teams). Enroll today at https://www.NateTheTrainer.com for the full learning experience including lesson discussions, quizzes, exams, and a completion certificate.

You can also watch the entire course as a YouTube playlist as well (just without the course discussions, quizzes, exam, and certificate). Be sure to subscribe to support my channel and for easy access to future content.

Posted on

How to create and work in lists in SharePoint Online (Video)

Lists are one of the core components of SharePoint. Learn how to create a list and work with it in this video.

This video is part of my FREE 30+ lesson self-paced online training course called Collaboration in Microsoft 365 (OneDrive, SharePoint, and Teams). Enroll today at https://www.NateTheTrainer.com for the full learning experience including lesson discussions, quizzes, exams, and a completion certificate.

You can also watch the entire course as a YouTube playlist as well (just without the course discussions, quizzes, exam, and certificate). Be sure to subscribe to support my channel and for easy access to future content.

Posted on

How to add a Version column to modern SharePoint list and library views

Versioning is enabled by default on all modern lists and libraries in Microsoft 365 and saves the last 500 major versions of an item/document . However, you won’t see a column displaying an item’s/document’s version number automatically in list/library views unless you explicitly add it.

Normally in modern lists and libraries you could show/hide columns that already exist by selecting the column header titled “+ Add column” then “Show/hide columns” as seen in the screenshot below.

However, when we do this, Version isn’t an option.

Solution #1: Edit the current view

We visit some classic SharePoint settings to find this column. Simply follow the steps below to add the “Version” column to your list or library view.

1. Go to the list or library and make sure it’s on the view to which you’re adding a column for Version

2. Select the view name (All Items or All Documents is default) then Edit current view

3. Scroll down to the bottom of the list of column names and select the checkbox next to Version.

4. Click OK at the top or bottom of the screen to save.

Note: If you want to rearrange the order of columns as well (perhaps placing Version between other columns) then be sure to change its corresponding number next to its row in the view settings as well.

Solution #2: Edit the view through List/Library settings

Rather than using Edit current view from the view itself, you can also get to the same view settings by going to Settings > List Settings or Settings > Library Settings.

Scroll down to the Views section and select the name of the view for which you’re adding the Version column then proceed with steps 3 and 4 from Solution #1.

And through either method, now we have a column for Version added to our view.

Posted on

Make full-width SharePoint hyperlink column clickable beyond just the link text

Photo by Pixabay from Pexels

This is such an obscure topic, but maybe it will help somebody curious out there. I recently had a request to alter a classic experience list with a single hyperlink column so that users could click in the white space of a cell and it takes them to that cell’s hyperlink value as if they’d actually clicked the link.

To illustrate what I mean, notice how the arrow pointer changes to a hand cursor like the whole cell is clickable. And when white (blue) space of the Google link is clicked, it takes us to Google anyway:

Click to enlarge

This was done with the tiniest bit of CSS added to the page inside a <style> tag. Note that this will affect all links in tables on the page to which it’s applied. So if you have more than one table on the page, this could cause issues. But in my case I just had the single-column list I was working with and this sufficed.

td a {
    display:inline-flex;
    width:100%;
}

Good luck!

Posted on

How to filter a SharePoint list or library using URL parameters

In this age of building bots and eliminating needless clicks and such to get employees the info they need faster and more directly, we need all the best practices we can find when creating solutions for users. This post will share a better practice when it comes to getting reliable, fast results from SharePoint lists and libraries that don’t rely on search index configuration or endless view sprawl.

Quick answer

If you’re just here for the quick answer, here it is. For string values, you can simply add this to the end of the list’s URL to filter it as specified.

?FilterField1=[IntFieldName]&FilterValue1=[FilterValue]
  • Spaces in field names and values: If your value includes spaces, replace them with %20.
  • [IntFieldName]: Internal field names (like Client in the example below is actually the original Title column) can be found in List Settings > Column Settings > Select Column > Check URL of column settings page (it contains the internal field name following &Field=).
Click to enlarge

If you want to learn about how this came up in my work and why I chose it as a solution, read on.

Case

Recently, I was involved in troubleshooting a bot that would search a SharePoint list using its built-in search box. However, the bot would be searching for items that were created minutes prior and the search index hadn’t updated yet so no results would be found and the bot would fail to complete.

So I suggested we filter instead of search.

Why filter?

Filtering lists works independently of the search index. Meaning I can create a new item in a SharePoint Server 2019 (or any SharePoint version) list right now and then filter the list and find that new item immediately. But if I search immediately, it won’t be found yet.

Want to argue about continuous crawls as workaround? Send your thoughts to nothanks@natechamberlain.com.

Changes to the process

For this particular bot, we’d be replacing this process:

  1. Navigate to SharePoint list URL
  2. Activate list’s search box
  3. Enter/type search term (unique item identifier guaranteed to return one result)
  4. Hit enter/search

with this one:

  1. Create a variable for the search term
  2. Navigate to SharePoint list URL with added URL parameter including variable from step 1 (taking us directly to the single result we needed)

It not only simplifies the steps involved, but reduces the likelihood that changes to UI elements will break our bot along the way.

Potential use cases

  • Sending workflow emails with direct links to lists and libraries already filtered
  • Reducing the number of views on a list by having users bookmark URLs including the filter parameters the view would have
  • Improving bot reliability by eliminating some UI-dependent steps
  • Eliminating search indexing delays for users or bots searching for new items
  • Setting a hyperlink column on a list automatically to filter the list/library by that item’s vendor/client/topic/etc. for easy comparison/drilldown (see how to set a hyperlink column using Power Automate)

How to do it

First you need to identify the internal field name of your column on which you’ll be filtering. Then we simply add a bit of text to the end of the list’s URL.

  1. Go to Settings > List settings (or List/Library > List/Library Settings in classic views).
  2. Select the column to filter from under Columns.
  3. Check the URL for the text following &Field= for the internal field name. Note this somewhere as we’ll need it soon.
  4. Go back to your default list view ending in .aspx and add the following to the end of the URL, replacing [IntFieldName] with the field name you got in the previous step:
    ?FilterField1=[IntFieldName]
  5. Now add the following to the end as well, replacing [FieldValue] with what you’d like to use to filter the column referenced in steps 3-4.
    ?FilterValue1=[FieldValue]
  6. Your final URL may resemble the following:
    https://YOURORG.sharepoint.com/sites/YOURSITE/Lists/ListName/AllItems.aspx?FilterField1=NotGov&FilterValue1=1

Filtering yes/no or true/false values

If your filter isn’t working for yes/no columns, replace “no” with 0 and “yes” with 1 in your URL.

Multiple field filters

You can filter the list on multiple columns by adding &FilterField2=… in the same format as the first. See below for an example:

?FilterField1=[Field1]&FilterValue1=[Value1]&FilterField2=[Field2]&FilterValue2=[Value2]
Click to enlarge

Be sure to check out this documentation for more URL filtering ideas and uses.

Posted on

SharePoint column validation format difference between classic and modern experiences

The following column validation formula worked fine in SharePoint on-prem (2016 specifically, in my case), but returned an error when used in the exact same context in SharePoint Online’s modern UI:

EndDate<=Today()

The expected behavior, in SharePoint Server/on-prem OR SharePoint Online/O365, is that if someone enters a date beyond the current date, they’ll get an error message and cannot submit the form until it’s corrected and the validation formula resolves to TRUE.

Troubleshooting in SharePoint Online

I used this formula in SharePoint Server/on-prem, and it worked fine. Then I tried using the modern UI in SharePoint Online by using the column’s menu > Column settings > Edit.

But when you try to save the exact same formula (specifically from the modern experience side panel) you get the error “A formula has a syntax error.”

Then I decided to try the classic view of settings to compare on-prem and online as closely as possible. I went to Settings > List settings and selected my column.

And, as you already know, it WORKED when entered on this classic column settings page (in SharePoint Online still) instead of the modern column settings side panel accessed directly from the list view.

When I go back through the modern UI now that my formula saved successfully, I see what caused the problem. The modern UI requires that you begin the formula with an equals sign (=). When I created the formula through the classic column settings method, it automatically added the equals sign for me in the background.

Solution

So if you’re creating column validation formulas in the modern experience (or even in classic), just remember to add an equals sign (=) to the start of your formula.

Click to enlarge

The difference is simply which formats are accepted.

  • Classic: Start formulas with or without equals sign
  • Modern: Start formulas with equals sign