Triggering Data Shuttle (workaround for missing functionality to support Foreign Keys)

ThePhotoBus

Hi - I know you can trigger Data Shuttle workflows manually or via schedule in the Data Shuttle UI. However, is there another way I can trigger a Data Shuttle workflow on demand? (e.g. by calling a URL, calling from an automation, etc.)

My problem is pretty basic. To illustrate, let's say I have reference data in 2 different sheets: A "Projects" sheet and an "Employees" sheet. Then I have a separate sheet called "Project Employee Allocations" where I want to manage data that links a "Project", and "Employee" and their % allocation to the Project (a "Project" can have many "Employees"; an "Employee" can be associated to many "Projects"). If I were to use standard database design principles, I would have a "Project ID" as the Primary Key on the "Projects" sheet, and on the "Project Employee Allocations" sheet I would have "Project ID" as a Foreign Key reference that essentially links the 2 tables/sheets together, and allows data about the Project to be managed separately from the Allocations.

However, it is my understanding that in Smartsheet there is currently no functionality to support simple Primary Key / Foreign Key relationships like this across sheets. Am I correct so far?

In searching through the Community pages, I found suggestions for "Using Data Shuttle to Update a Dropdown in another Sheet." Although it's cumbersome, I've taken the advice, and so I now have 1 Data Shuttle that writes "Projects" to a file, and then another Data Shuttle that uses data from the file to update the "Project ID" dropdown in my "Project Employee Allocations" sheet. It does work, but there's a major problem with the user experience.

Let's say a person goes to add a new "Project Employee Allocation" record and finds that the "Project" does not yet exist. Currently, they need to:

1) Go to the "Project" sheet and add a new record

2) Wait 15 minutes until we can safely say that the "Offload" Data Shuttle ran

3) Wait another 15 minutes until we can safely say the "Upload" Data Shuttle ran

4) Go back to the "Project Employee Allocations" sheet - where they can now enter the new record

If I had a way to trigger the 2 Data Shuttle workflows (inside or outside Smartsheet), I could at least improve the user experience to something manageable. That said, native support for Primary Key / Foreign Key relationships across Sheets would be a MUCH BETTER solution.

Any ideas, thoughts or workarounds you could share that might help me on this one?

Cheers!

Ryan

Genevieve P.

Hi @ThePhotoBus

Data Shuttle uses the Smartsheet API to trigger/run, so any other action you set up to trigger it (calling a URL, calling from an automation, etc.) will all have the same run-time as the manual trigger or scheduled trigger.

The current system you have set up, with the steps at the end of your post, sounds like the smoothest way to do this within current Smartsheet functionality. Please provide your feedback on Data Shuttle and on dropdown lists to the Product team through this form, here!

Thank you,

Genevieve