Need for Unique identifier column in Data Shuttle
Hi team,
I have a sheet in Smartsheet which captures data via form and also know wanted to update with data from excel. For this I have created upload workflow and have attached one drive excel attachment. I do not really have ID column within my sheet, because the participants who are completing the form do not know this ID. I just tried upload a data creating ID columns in both excel and Smartsheet and it worked. However, keeping in mind the participants who complete the form, (they do not know what this ID would be), how can I achieve this transfer without needing the unique identifier column? Can somebody please help me with this? Thanks in Advance!
Best Answers
-
Hi @Mounika
Although your users are entering "new" information each time and they know what rows are there and what's newly added, each time the Workflow is run, Data Shuttle reads the entire file as if for the first time. Does that make sense? Think of it like you "export" that data into a static file on your desktop. Then the next day, you export the same file again. That creates a second file on your desktop, with previous rows and newly added rows.
This means that either you need the whole file to only contain new content (so the workflow purely adds data into Smartsheet), or there needs to be a data point on the row to indicate that it should be skipped / ignored.
Based on your scenario, adding an "ID" column in your excel file seems to be the best way to go. If you're concerned about merging this with the Form content, you can keep the 2-sheet idea:
- Have an intake sheet that's specifically only used for the Onedrive file
- Use the ID column as a text-number column in both files, as the Unique Identifier column in the workflow
- Add new rows using Data Shuttle (skipping over previously added data, based on ID)
- Copy rows from this first sheet into the second sheet when they're added.
^ Having the workflow based on newly added rows means that it won't copy over previous data that was entered into this sheet. Copying (instead of moving) the rows means that this intake sheet keeps track of duplicates / previously copied rows.
Let me know if all of this makes sense or if you'd prefer to see screen captures to help illustrate my suggestions! 🙂
Cheers,
Genevieve
-
Hi @Mounika
At this point it looks like you have two completely separate processes:
- Individual Form intake that will be static rows added into one sheet
- Bulk upload "synced" rows that need to be continually updated/checked every 15 minutes
However you want to see all of this content in one place in Smartsheet.
How about using a Report to combine the two sheets together instead of moving/copying rows from one place to another? This way you can fully replace all rows in the intake sheet from your Onedrive file, always keeping them in sync (even when there's only been half a row entered), but you can still display that content along side your second Form sheet.
A Report could auto-sort your rows as they come in from each place to ensure they're in the order you want. You can also add content into a Report which will update the underlying source sheets immediately.
You can use a Report to source a Dynamic View.
-
Yes 🙂
Answers
-
Hi @Mounika
You'll need to have one column that has a unique identifier so that the "Update Rows" part of your workflow can find the matching row to update.
The "Add rows" part will add any new submissions that don't have a matching identifier, so your submitters won't need to know the unique ID.
For example, if the ID is their email address and someone enters multiple form submissions, then the Smartsheet sheet would find the matching email and update the row with the newest content (instead of adding a new row).
However if you wanted duplicates for each person, so each of their submissions to be a separate row in Smartsheet, then you'll need to use a column that will have something new every time (such as a row number or row ID). Does that help?
Cheers,
Genevieve
-
Thanks for the response! Yes, my sheet will have duplicate entries, which mean each participant submit the information several times. Any possible workaround for this?
-
Hi @Mounika
If you want the duplicate entries, then you'll just need to select a different column as your Unique ID 🙂
I would suggest an auto-number column or something that will have a new number with every submission so you can pull in all entries.
Since you have "Merge" selected, what is it you're looking to merge? (Versus add new)
-
It is a financial Tracker sheet, where all the subdepartment project coordinators submit their spent expenses via form most of the times. However, sometimes they also have bulk entries which they usually enter them in excel, so in order to have them copy each of these bulk expenses into Smartsheet form, I am looking for a way to merge excel with Smartsheet, so that whenever they are entering bulk entries into excel, it automatically copies into Smartsheet, without having them complete a Smartsheet form again. I have already tried creating a auto-number column in Smartsheet and also an ID coulmn in Excel, within excel they can direct view and drag the ID, whenever they have new entry, however, as in Smartsheet since it is auto ID coulmn they will not know what ID is in there (and the with Auto numbered column, it cannot take the data from excel with the ID present in Excel sheet).
In the attached screenshots, the first one is from Smartsheet, and second from common one-drive excel where bulk entries are added at once. The Participants (project admins) who are submitting the form vis Smartsheet do not have access the Smartsheet spreadsheet that was attached here, instead, based on "Subspeciality column", I have created a dynamic view for each subspecialty. So, the project admins can only view/edit their subspecialty financial information. Currently, as you see in the Smartsheet spreadsheet, the data shuttle upload workflow has been successful, until I have not made the "Unique ID" column as Auto numbered. As soon as I have changed it auto-numbered, it did not upload the "EX-ID-6" row from excel and workflow was an error.
For the excel item, I have given ID starting with "EX " as just to identify that it copied from Excel, and in fact it is not required to have them (project admins) to this column in Smartsheet. So even if I am using the same suffix as I am using in Smartsheet spreadsheet, the project admins who are completing the entry either in Excel or Smartsheet do not the ID that should use, since they do not have access to Smartsheet spreadsheet which has all the subspecialties data, they can rather only view, their subspecialty data within dynamic view.
-
Hi @Mounika
This is super helpful, thank you!
So to clarify, it looks like you always want to add new rows, and there is never a time where you want to update a row that already exists in Smartsheet, yes?
In this case, what I would do is actually have a completely separate sheet where you're using Data Shuttle to upload the data. Use the "Replace all Target rows with the data from the source file" option into this intake sheet. Then you can use a Move Row workflow built directly in that intake sheet so when a new row is added it's moved over into your actual working sheet automatically.
This way you have no need for a unique identifier column, since you want the duplicates. You can remove the auto-ID from Smartsheet and remove the need for your users to drag-fill a random number in Excel.
Will this process work for you?
Cheers,
Genevieve
-
Thanks for the response, I have tried the way you have suggested, however, every time when the data shuttle work flow runs, all the data from excel sheet is uploaded into intake sheet, and then being moved to master sheet (which collects data via form as well), creating a entire duplicated list again and again!
I do not want the duplicates, because the project admins will not enter the information in excel, if they are completing it via form. So, for each row, the information is unique.
-
Hi @Mounika
Apologies, I seem to be missing a key piece here - what is it that identifies if a row is a "duplicate" or not?
Do you mean that they're using the same Excel file every time, adding new rows, instead of using a new Excel file each time with the bulk-rows?
-
Yes, they will be using the excel file every time when they have bulk entries, and currently I do not have any column that identifies if a row is duplicate or not! And I don't think I need one, because it is only three of my team members who uses this sheet, so whenever they have single entries, they use form, with bulk entries they use excel (which is a common one-drive file)
-
Hi @Mounika
Aha, here is the crux of the issue. There are a few ways forward, depending on your preferences.
- Have your three team members use a new excel (perhaps a copied and cleared version) with only new rows every time they want to bulk-upload rows. Then use the method of importing all rows in an intake sheet and moving them over with a workflow.
- Have a column in the excel file to indicate if they have already "submitted" those rows before. (This can be a simple "Done" cell). Use a filter in your Data Shuttle set-up to ignore the rows that have this marker. Then use the method of importing all rows in an intake sheet and moving them over with a workflow.
- Have a column in your excel file with a unique identifier (like your "EX-ID-6" value) but do not make the same column an auto-number column in Smartsheet. Keep them both as text/number columns. Then use the Merge + Add option in the Workflow directly into your master Smartsheet.
Do these options make sense?
-
- Have your three team members use a new excel (perhaps a copied and cleared version) with only new rows every time they want to bulk-upload rows. Then use the method of importing all rows in an intake sheet and moving them over with a workflow. -- This will not be an ideal solution in my case, to create a new excel file every time when they have bulk entries :).
- Have a column in the excel file to indicate if they have already "submitted" those rows before. (This can be a simple "Done" cell). Use a filter in your Data Shuttle set-up to ignore the rows that have this marker. Then use the method of importing all rows in an intake sheet and moving them over with a workflow. -- The data Upload workflow in data shuttle, runs for every 15 minutes for all the 7 days of a week, how do they know when should check box it? And also for sure they will not enter any duplicate information, and I do not have to check on it, it will be a new information every time (either via form or Excel).
- Have a column in your excel file with a unique identifier (like your "EX-ID-6" value) but do not make the same column an auto-number column in Smartsheet. Keep them both as text/number columns. Then use the Merge + Add option in the Workflow directly into your master Smartsheet. -- In this case participants submitting information via form will not enter ID (because they do not know what this ID is), so the row filled via form will have the ID column empty within a Smartsheet for that row (and will be the same for the entries submitted via form). Within the data upload workflow in data shuttle, since we have to select this ID column as Unique identifier, and having blank cells (for ID column) for all the entries submitted via form will not affect the workflow?
-
Hi @Mounika
Although your users are entering "new" information each time and they know what rows are there and what's newly added, each time the Workflow is run, Data Shuttle reads the entire file as if for the first time. Does that make sense? Think of it like you "export" that data into a static file on your desktop. Then the next day, you export the same file again. That creates a second file on your desktop, with previous rows and newly added rows.
This means that either you need the whole file to only contain new content (so the workflow purely adds data into Smartsheet), or there needs to be a data point on the row to indicate that it should be skipped / ignored.
Based on your scenario, adding an "ID" column in your excel file seems to be the best way to go. If you're concerned about merging this with the Form content, you can keep the 2-sheet idea:
- Have an intake sheet that's specifically only used for the Onedrive file
- Use the ID column as a text-number column in both files, as the Unique Identifier column in the workflow
- Add new rows using Data Shuttle (skipping over previously added data, based on ID)
- Copy rows from this first sheet into the second sheet when they're added.
^ Having the workflow based on newly added rows means that it won't copy over previous data that was entered into this sheet. Copying (instead of moving) the rows means that this intake sheet keeps track of duplicates / previously copied rows.
Let me know if all of this makes sense or if you'd prefer to see screen captures to help illustrate my suggestions! 🙂
Cheers,
Genevieve
-
I have tried using the workflow you have suggested by keeping 2-sheet idea. It worked as expected, however, the data upload workflow runs for every 15 minutes, and let's thinks for example if the participant starts entering the data into excel right before the workflow runs, and after completing one or two columns, the workflow has run and the data is uploaded into Smartsheet (helper sheet/2-sheet from where data is being copied to master sheet) including only the data from those columns in excel sheet. With the net upcoming worflow, the data from remaining columns gets uploaded into helper sheet, but it is not going to be copied into master sheet (kindly see the attached.
In the "Copy rows" workflow in Helper sheet, I have selected the following setting:
I have I selected the last column name in this workflow (as participants usually fill from column A- column Z) So that when the last column is not empty, the row gets copied into master sheet.
And another downfall that I see with this workflow is, any changes/ edits made in excel are being updated in helper sheet, but not in Master sheet (since we are copying rows as they are added in the helper sheet)
-
Hi @Mounika
At this point it looks like you have two completely separate processes:
- Individual Form intake that will be static rows added into one sheet
- Bulk upload "synced" rows that need to be continually updated/checked every 15 minutes
However you want to see all of this content in one place in Smartsheet.
How about using a Report to combine the two sheets together instead of moving/copying rows from one place to another? This way you can fully replace all rows in the intake sheet from your Onedrive file, always keeping them in sync (even when there's only been half a row entered), but you can still display that content along side your second Form sheet.
A Report could auto-sort your rows as they come in from each place to ensure they're in the order you want. You can also add content into a Report which will update the underlying source sheets immediately.
You can use a Report to source a Dynamic View.
-
Can I build a dynamic views from that report?
-
Yes 🙂
