Dynamic Drop Down menu

Hi -

Is Data Shuttle the only way to create a dynamic drop down menu? It seems overly complicated for what I am trying to do:

I have a company intake list, which lists all the companies our organization supports. This list will grow over time, and we will check them off as 'complete' once our support is complete
I have multiple other sheets that track projects, contracts, etc that all need a Company drop down menu where we can select from a drop down of active company names from the company intake list

I've figured out how to do this using Data Shuttle (I've not yet added in the condition to exclude 'complete' companies, but I'm sure that is possible), but this seems like a heavy handed work flow to export data and re-import it, when what I am trying to do is all within the Smartsheet architecture. And the Data shuttle is not a live process, my understanding is that it only runs on a pre-set schedule, which means it will be doing a lot of work for nothing since I don't expect changes to the company intake list will be that frequent, but I still need the drop downs to be as close to live as possible.

Please let me know if there is an easier way to accomplish this goal to create a dynamic drop down menu without using the Data Shuttle feature?

Much appreciated!!

Find more posts tagged with

Accepted answers

All comments

Paul Newcome

You can also use the API or the premium add-on Bridge. I believe there may also be some other 3rd party apps out there that can be configured for the same.

We at 10xViz also off a dropdown manager that is designed specifically for this sort of thing and can be configured for multiple reference and target columns very easily. We have also built other solutions that are even more user friendly when less flexibility is required such as only having a single reference column.

Here is a link to an article that can help get you started if you are using the Bridge application:

https://help.smartsheet.com/articles/2482712-update-dropdown-columns#toc-what-you-need-to-get-started

Of course the Community is a great way to get DIY help, and there are a lot of knowledgeable users here that can get more detailed with api and Bridge options depending on which route you go. If you do go this route, you are more than welcome to "@mention" me in your question, and I'd be happy to help.

If you are interested in working with 10xViz directly, feel free to check us out by clicking on that first link in my signature.

egardner

Hi there!

The best option is to use Bridge or API for this. If you don't have access to those things, or don't feel like you have the technical knowledge to use them, there's a workaround (still in Data Shuttle, if you're open to it) that I have found works well. It also might be what you were already doing, so feel free to ignore if it is not helpful.

Essentially, you'll want to create an Internal Looper helper sheet to act as the middleman for the dropdown options. This means you can have one file/offload workflow that updates all of the different dropdowns you need. Then, you will create an upload workflow to take the data from that offloaded file and update the options in your dropdown list. From there, if you need to update it in multiple places, you can easily just copy the upload workflow you created and simple change the target sheet & mapped cells.

All this is still in Data Shuttle, but replaces the need to create both an offload & and upload for every instance of updating you want to create with just needing to create/copy the existing upload.

Here's how to do it:

Create a helper sheet (I call mine "Internal Looper"). We're not actually going to do anything with this sheet other than use it to house attachments.
1. Create a blank Excel Document, called "Company List For Dropdowns" or something similar.
  1. Add fake header the company name
  3. Save it as a .csv or .xlsx document.
2. Attach the "Company List For Dropdowns" excel document to the Internal Looper sheet.
Create an offload Data Shuttle workflow from the company intake list.
1. For the file, select "Smartsheet Attachment", and select the Internal Looper you created.
3. Make sure the names/file type match up exactly between the document you attached the looper and what you have put in the Data Shuttle flow.
4. Add a filter so that only companies that are not "complete" will be included
6. Map your columns out
7. Schedule it to run frequently - once a day, once every two hours, whatever makes sense. There is no worry of running this too much, as if there are no changes it doesn't take that much processing power.
8. Save.
9. Manually run the data shuttle offload you just created to populate the internal looper attachment/so that you can move on to the next step
Create upload to update dropdown options in target sheet.
1. Source Location: Smartsheet Attachment
2. Internal Looper
3. Choose Attachment By: Name
4. Enter the name of the excel file exactly as it appears in in the looper sheet
5. This file has column headers: checked
7. Select target sheet w/ dropdown you want to update
8. Choose the update dropdowns option
11. Map your field options
12. Schedule to "run on attachment"

You can then "copy as new" the upload workflow and simply change the destination sheet & mapped columns to easily create workflows to update multiple sheets with this information

I find it is helpful to be really intentional with how I name these Data Shuttle Workflows, especially if I will need a lot of them to update various things from one source. For this example, I'd probably name the work flows something like this:

Offload Workflow:
- Active Companies - OFF - LOOP - Company Names for DD
Upload Workflow:
- Target Sheet - UP - DD - Active Company Names

So, essentially, I am notating a) which sheet my data is originating from or where it is going to, b) whether it's uploading or offloading, c) the process it's following (LOOP for looping process or DD for dropdown update process), and then d) what's being ultimately updated. This is a system that works for me, but of course do what makes sense for you!

Hopefully this helps! Let me know if you need any help or have any questions.

Emma

IT Project Coordinator

Karenmst

Hi Paul and Emma,

Thanks both for your response. I wasn't aware of the Bridge add on, and while I don't have a lot of extra budget to add features to my account, I"m just learning that the Data Shuttle is going to cost me as well. I will talk to my Smartsheet account rep about the cost implications of Bridge vs Data Shuttle - but if I have to pay for one or the other, would Bridge be the way to go? (I have managed to get the Data Shuttle work flows up and running as a demo, so I'd need an incentive to switch)

Emma - the solution you presented looks very similar to what I have set up as a demo (following these instructions: https://help.smartsheet.com/articles/2482755-dynamic-dropdowns-data-shuttle%29. . The one step that seems different is that I didn't actually create a new excel file and link to my internal looper, I just input 'companynamesfor dropdown.csv' in the export file name field (your step 2 screen shot) and went from there. The work flow seems to work fine, despite me wondering where the excel file is actually housed! Appreciate all your additional commentary, this is all helpful reference for me.

I guess I"m still a bit surprised that these premium add on features (Bridge / Data Shuttle) are required for a process that is focused on using Smartsheet data within the Smartsheet architecture. I had understood Data Shuttle is really about offloading/uploading data between platforms, not within Smartsheet itself!

Appreciate all your support!

Paul Newcome

Data Shuttle will most likely be the least expensive initially, but it can add up depending on how much it is used. Bridge is the most cost effective if you can swing it though. It is not priced based on usage, and it can open up a whole new world of possibilities well beyond just updating some columns.

Andrée Starå

https://community.smartsheet.com/discussion/comment/490617#Comment_490617

Hi,

I hope you're well and safe!

I agree with Paul that Data Shuttle is the most economical solution, but be aware that there has been an update to the pricing model, and now you'd pay per workflow instead of by the number of rows affected.

There are limits to each workflow, and additional purchases are required if you exceed these limits.

FYI @Paul Newcome ⬆️ (was released around a week ago)

I hope that helps!

Be safe, and have a fantastic day!

Best,

Andrée Starå | Smartsheet Expert Consultant & Partner / CEO @ WORK BOLD

✅ Did I help answer your question/solve the problem? Please support with💡 ⬆️ ❤️, and/or ✅ Answer. This will make it easier for others to find a solution or help answer. I appreciate it, thank you! 🙏

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.