Data Shuttle - Update Dropdowns ALPHABETICALLY

Mike TV

Hi all. I use the Update Dropdowns feature of Data Shuttle quite a bit to help save my team time when filling out forms, etc. They can quickly become an absolute mess letting Data Shuttle manage them:

If you have a moment, can you please fill out the Product Feedback form to suggest that they alphabetize our drop-down lists after Data Shuttle adds to the choices?

https://app.smartsheet.com/b/form/739aa75f30ca43a8a22eb53e4da7d409?Origin=help&_ga=2.193833962.654044142.1684277500-130537087.1648859136

I can't believe their support team would add such a feature without the ability to auto-sort the list. Why? Just why would you add this without adding that? Just look at the hot mess my dropdown list becomes! What an embarrassment to roll out a form to an outside company and ask them to hunt through scrambled lists for what they're looking for...

Jacob Minnich

For anyone looking to do this, it can be done with MS365 for business through Excel and Power Automate and it's relatively easy to do. Set up your offload Data Shuttle, open the file in Excel online and record a script of the column sort, open Power Automate and create a flow with the trigger "When a file is modified" and action "Excel> Run a Script". This will auto-sort your offloaded Data Shuttle file and it will be in order when it is uploaded. It might offload/upload before Power Automate can catch it to sort, but it will get it the next time Data Shuttle runs.

https://powerusers.microsoft.com/t5/General-Power-Automate/Sorting-an-Excel-Spreadsheet-Alphabetically-Using-Power-Automate/td-p/1459831

Chuck G

I would love to see a way for the Offload file to have option to Alpha sort before Upload file brings in the new list. Is this a possible update coming?

Mike TV

@Chuck G

To my knowledge, this isn't even on their radar. Please submit the enhancement request. It's our only hope of getting what we want to be updated.

Chuck G

☹️Thanks Mike

Jacob Minnich

For anyone looking to do this, it can be done with MS365 for business through Excel and Power Automate and it's relatively easy to do. Set up your offload Data Shuttle, open the file in Excel online and record a script of the column sort, open Power Automate and create a flow with the trigger "When a file is modified" and action "Excel> Run a Script". This will auto-sort your offloaded Data Shuttle file and it will be in order when it is uploaded. It might offload/upload before Power Automate can catch it to sort, but it will get it the next time Data Shuttle runs.

https://powerusers.microsoft.com/t5/General-Power-Automate/Sorting-an-Excel-Spreadsheet-Alphabetically-Using-Power-Automate/td-p/1459831

AdamF

Another "workaround" since Data Shuttle doesn't yet have built-in sorting… You can use a process like this: Source Sheet (messy data) > SmartSheet Report (sorted data) > SmartSheet Pivot (uses report sort) > DataShuttle.

For example, our organization is frequently updating our Contract list based on the business we win or work that is not renewed. So while there is a source sheet with all of the Contract Names, the sheet contains both old data and rows that are not sorted since the new rows come in via automation or form entry. However, we want the Contract drop-down in our other processes such as Risks to always be up-to-date and sorted alphanumerically.

Step 1) Create a report with the Filters and Sort desired for the dropdown list.

Step 2) Create a Pivot with that new report as a source option. This can be a very simple pivot with the 1 row selected for the data you want, no columns, and maybe a simple count of something in that row

Please note: That since a report is the source you wouldn't be able to have the pivot update automatically upon a sheet update, but you can configure to run every hour, or every day or whatever interval is best.

Step 3) Run the new Pivot, it will create a brand new sheet in the folder you designated during setup, with the data sorted the way you want.

Step 4) Since you have a self-updating sheet now, you can use that sheet as the source for DataShuttle, you'll just likely have to filter out any blank rows and the "Grand Total" row created by the pivot process.

Chizu Hieida

@Jacob Minnich I used your method and it worked great! :) Thanks!