Data Shuttle - Notifications if Unique ID is Missing?

RAdamowicz
RAdamowicz ✭✭✭
edited 02/04/25 in Add Ons and Integrations

Hi all! I'm setting up a data shuttle to automatically update our records when we receive inventory reports from a third party vendor who manages our warehouse. The trouble is, we've noticed that some of our items have fallen off their reports, and we need to keep an eye on this week over week. I'm trying to see if there is a way to set up an alert for when rows in my sheet aren't updated because the item number (our unique identifier) isn't showing up in the report from the vendor. Any suggestions? Thanks in advance!

Best Answer

  • Samuel Mueller
    Samuel Mueller Overachievers
    Answer ✓

    @RAdamowicz

    If you are manually updating the spreadsheet before you attach it for the datashuttle workflow, just add a column to the spreadsheet and populate all the rows with the date of your attachment. Map that in your data shuttle workflow, and then you will be able to easily see which rows don't have the updated date

Answers

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp Community Champion

    Hi @RAdamowicz

    For your use case, there are a couple of ways to handle missing item numbers using Smartsheet Data Shuttle and Automation:

    1. If the report comes as an attachment to a row:
      • Data Shuttle allows you to map values from an attachment to specific columns in your sheet.
      • However, if you update the target sheet with the item number and the item number value is missing, the row in the corresponding target sheet will not be updated.
      • A workaround is to use Data Shuttle to update a staging (temporary) sheet, then set up Smartsheet automation to track missing items and send alerts when an Item Number is blank.
        • To alert by automation, you need a contact column.
          • One approach is to have a contact value in the Summary field and add a column (e.g., Vendor) with the formula =[Vendor]#.
          • However, if multiple vendors are reporting, this method won’t work. In that case, you can ask the vendor to use a standardized Excel or CSV file format with the same column (e.g., Reporting Vendor or Reporter) that contains their email address in each row.
          • Additionally, if you use the "replace all Target sheet rows" workflow action, you must add clear values automation for the successive file attachment at the source sheet.
      • So, file attachment to the source sheet triggers two Data Shuttle workflows.
        • One is to update rows using the item number on the target sheet.
        • The other is to alert the vendor regarding the missing Item Number.
      • Then, if the vendor attaches a new file with complete Item Numbers, the target sheet will be updated with the first workflow above.
    2. If the report comes as a new row to a sheet:
      • You can use a Smartsheet automation to check for missing Unique IDs.
      • Set up a workflow that alerts you when an item number is blank or does not match previous reports.

    Sample Workflow (This uses an update request, but an alert should work.)

    Data Shuttle Setting is for Uploading to the staging or workflow sheet.

  • RAdamowicz
    RAdamowicz ✭✭✭

    Hi @jmyzk_cloudsmart_jp ! Thanks for the detailed response!

    I've been trying to figure this out but I think I'm a bit confused about the second part. So far, the process I have running is:

    • We receive an emailed attachment from the warehouse vendor, which we clean up (it has inventory for multiple departments in our company on it, and we only want Smartsheet to look at our stuff) and then upload as an attachment to a helper sheet.
    • Data Shuttle then runs to compare the new Excel file to our Smartsheet, using item numbers to update item descriptions and quantity in stock. It also adds new rows if there are items that aren't already listed in our Smartsheet inventory tracker (which is part of why we clean up the file first).

    That's all working fine. It's getting Smartsheet to recognize when an item number goes missing that I am unsure about. I understand the idea of setting up an additional helper sheet to calculate this without messing up our main inventory, but can you please explain further what sort of formula or automation I'd use to identify if something is missing? Sorry if I am missing something obvious!! Once it detects anything missing, I can just set up an alert to our team to call and address with the vendor.

    Thank you!!

  • Samuel Mueller
    Samuel Mueller Overachievers
    Answer ✓

    @RAdamowicz

    If you are manually updating the spreadsheet before you attach it for the datashuttle workflow, just add a column to the spreadsheet and populate all the rows with the date of your attachment. Map that in your data shuttle workflow, and then you will be able to easily see which rows don't have the updated date

  • RAdamowicz
    RAdamowicz ✭✭✭

    @Samuel Mueller great idea! I love when a solution is simple haha. Thank you!!

  • jmyzk_cloudsmart_jp
    jmyzk_cloudsmart_jp Community Champion

    Hi @RAdamowicz

    Since your vendor's Excel sheet includes data for multiple departments, you can use the Data Shuttle filter to upload only the data relevant to your department into a staging sheet.

    A Sample Excel Sheet from a Vendor

    Data Shuttle Workflow 1  (Replace)

    With this filter, when I attach the Excel file to a sheet, "Data Shuttle Upload Helper Sheet: Staging", for example, the data will be uploaded to the Staging Sheet.

    Upload Staging Sheet with Helper Columns

    The staging sheet from the vendor’s report will contain most of the Item IDs. You can create a lookup table to retrieve the vendor’s email based on the Item IDs using the following formula:

    [Vendor] =INDEX({Item Number to Vendor List : Vendor}, MATCH([Item Number]@row, {Item Number to Vendor List : Item Number}, 0))

    Then, you can reference these values in the Sheet Summary field:

    Vendor#=INDEX(DISTINCT(Vendor:Vendor), 1)

    Next, add a Missing Vendor column with this formula to flag missing entries:

    [Missing Vendor] =IF(ISBLANK([Item Number]@row), Vendor#)

    Note: If your vendor includes their email in each Excel file row, you won’t need these lookup formulas. However, since it’s unlikely that you can ask them to do this, the lookup method remains useful.

    Sample Lookup Sheet

    Finally, automation should be created to send Update Rows requests based on missing values.


    The vendor will receive an update request for any rows where the Item ID is missing.

    Once the missing Item ID has been corrected, you can download the sheet as an Excel file, attach it to a helper sheet, and trigger Data Shuttle for the final upload. (This is the final update, not the staging update.)

    This approach enables you to:

    • Automatically filter only your department’s data
    • Send update requests to vendors for missing Item IDs
    • Attach the updated Excel file and trigger Data Shuttle for an accurate inventory update

    I tested this method using two demo Excel sheets from different vendors, and the process worked as expected.

    Let me know if you need any further clarification!

  • RAdamowicz
    RAdamowicz ✭✭✭

    Thanks @jmyzk_cloudsmart_jp ! Unfortunately, the vendor report isn't neat enough to have a department section - we have to go through an determine which items are ours based on the title, which is a bummer. We're working on getting updates to the report though, so if we're able to get them to make that change, your method will certainly come in handy. Thanks again!