community Alteryx IO MyAlteryx
alteryx Community
Search
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Close
Free Trial

Engine Works

Under the hood of Alteryx: tips, tricks and how-tos.

Update SharePoint Drop Down Lists Using Alteryx Tools

FreeRangeDingo
ACE Emeritus
ACE Emeritus
‎04-13-2022 04:30 PM

Over the last few months, I've been working with our IT group to automate the new hire process (by which IT sets up a new person with hardware and software). The old process kicked off with (GASP!) an Excel spreadsheet sent to hiring managers. As you might imagine, that Excel form and process was slow and fraught with error. So, it's getting a makeover using SharePoint, Power Automate, and Alteryx. This post will explain how I update SharePoint drop down lists using Alteryx tools. Read on to learn more.

 

Setup & Configuration

 

Okay, the first link in this chain is setup and configuration. You might need to download the SharePoint tools if they are not already part of your environment. You can find them here. This page also has some great information.

 

Make sure to review the Alteryx Documentation on these tools because there are different versions, and the functionality is not the same from version to version. I am using the most updated version.

 

Use Case

 

Now that basic setup is out of the way, let's talk about what I'm trying to do here. Ultimately, we are replacing an Excel spreadsheet (being used as a form) with a SharePoint list (being used as a form). The old spreadsheet included information such as the Hiring Manager's name and a "Mirror". The mirror is someone who is currently employed with the company. IT configures the new hire's access and AD group setup based on the mirror. Basically, Jane already works here. Mary starts next week. Make Mary's access just like Jane's.

 

So, our SharePoint form, has a drop down input for Hiring Manager and a drop down input for the Mirror. The drop down is populated by the contents of a SharePoint list that we update with Alteryx and PowerAutomate. This is an improvement over free form text. Here's what this part of the form looks like with the drop down.

 

FreeRangeDingo_0-1649891123892.png

 

Now, let me show you how we use Alteryx tools to make this happen.

 

Workflow Components

 

First, when properly installed, you can find the SharePoint tools in the Connectors pallete. I had to hunt for them.

 

FreeRangeDingo_1-1649891123908.png

 

  1. SharePoint List Input - Connects to the SharePoint list.
  2. Select - The actual list has many columns but I only need 2 of them, so I remove unused columns.
  3. Input Data - Connects to a SQL database where I pull the names and emails of every employee and contractor.
  4. Select - Renames columns and removes unused columns. As a matter of practice, I always rename columns in a Select right after the input.
  5. Multi-Field Formula - Use the Trim function to remove whitespace from all text fields. Check out this post for an explanation of tools I use in all workflows (including the next one, Auto Field).
  6. Auto Field - Make each column as small as possible.
  7. Join - Join the two data sets. What falls out of the left side of the join gets removed from the list. What falls out of the right side of the join gets appended to the list. We don't do anything with matching records.
  8. Select (top) - Remove columns that will not be part of the email.
  9. Email - Send an email to an inbox that PowerAutomate monitors. The email will kick off a PowerAutomate flow to remove names from the list. For each name that needs to be removed, Alteryx sends one email.
  10. Select (bottom) - Rename columns to match the SharePoint list columns.
  11. SharePoint List Output - Append new names to the SharePoint list.

And just so it's crystal clear, I want to point out that I am using Alteryx tools to add to the SharePoint list and what is not shown is a separate PowerAutomate flow that removes from the SharePoint list.

 

Below, you can see how I configured the SharePoint List Input and Output tools. The tools specify the list and view to pull in/modify.

 

FreeRangeDingo_2-1649891123912.png

 

The SharePoint List Output tool also specifies the output method. I have chosen Append Existing. Keep scrolling to see all the available options.

 

FreeRangeDingo_3-1649891123916.png

 

FreeRangeDingo_4-1649891123917.png

 

The workflow runs on a nightly schedule and the drop down values are fresh.

 

Warnings

 

Of course, this build was not without a few hiccups and trial and error. You'll want to watch out for these 3 gremlins.

 

  1. First, if the SharePoint list changes (ex. changed the name of a list or list contents), you might need to restart Alteryx to see the changes. You might even just need to wait a bit.
  2. Second, my first attempt used the Delete List and Append output option. This was problematic because when the flow would run, Alteryx deleted the Hires list (duh). Therefore, any list populated by the contents of Hires would be nulled out. It does make sense (even if it annoys me), but it forced me to add to the list with Alteryx and remove from the list with Power Automate.
  3. Third, SharePoint will default the first column in any list to the name "Title". If you change this name to something like "Full Name", the list contents will be null when you run it in Alteryx. No idea why. The fix is to not change Title.

FreeRangeDingo_5-1649891123922.png

 

Conclusion

 

Now you know how to update SharePoint drop down lists using Alteryx tools.

 

Other Sweet Alteryx Content