Update SharePoint Drop Down Lists Using Alteryx Tools

Over the last few months, I’ve been working with our IT group to automate the new hire process (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.

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.

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.

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

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.

Conclusion

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

Other Sweet Alteryx Content

• Solve Query Timeout Issues in Alteryx
• Remove Leading Zeros in Alteryx
• Find and Add New Alteryx Tools