Data Uploader - if value exists on input file, need it to update status field on source sheet

We have tracking sheet that we use to track client accounts that are transferring. I want to take a different file that shows accounts that have confirmed been transfer and use Data Uploader to have the information on the input sheet either map to or change certain cells.

I have the mapping down - but I can't figure out the formula that says "If the account number listed on the source sheet is also found on the input sheet, update the Status column". In a perfect world what I really need it to do is "If the account number listed on the source sheet is also found on the input sheet, update the Status column with EITHER 'Complete, Waiting for Download' if the Account Balance is not available(less than .01) OR update the Status with 'Complete' if the Account Balance is available (greater than .01)."

The columns on my source sheet are titled Status, Account Number and Account Balance

The column on my input sheet is titled Account Number.

Best Answers

  • Christina Reid
    Christina Reid ✭✭✭✭
    Answer ✓

    You are awesome! That totally worked and I really appreciate it!!!

Answers

  • Genevieve P.
    Genevieve P. Employee
    edited 03/16/20

    Hi Christina,

    You could set it up like this... "If the Account Number in the source sheet matches the Account Number in Smartsheet, update the Account Balance column and state that there is a match". Then in the Smartsheet sheet you can build a formula that looks at the account balance and reflects the different statuses that you would like, but only if there's a match (separate from Data Uploader).

    To do so, you would need to add in a helper column in both sheets that essentially is just looking to confirm if the Account Number is in both. You could have this be a simple checkbox column in Smartsheet, and input the number 1 in every row in the other sheet, when there is new data. This will check the box in Smartsheet when you run the Data Uploader workflow, if the Account Number has a match.

    You'll want to have all four columns in your Smartsheet sheet before setting up the Uploader workflow.

    Your Unique Identifier column will be the Account Number column, and you'll map this, the Account Balance, and the matching checkbox column to their counter-parts. Make sure that you've selected "Update Existing Rows" in the Target section of the workflow, as well.

    Once you have the workflow running as expected, this is the formula you can use in your Status Column in Smartsheet:

    =IF(AND(ISBLANK([Account Balance]@row), [Matching Column]@row = 1), "Complete, Waiting for Download", IF([Matching Column]@row = 1, "Complete", "Not Complete"))


    This will first check to see if the Account Balance is blank, even though there is a match - if so, then it will return "Complete, Waiting for Download". If the Account Balance is not blank, and there's a match, then it will return "Complete". Otherwise, if there is no match, the status will say "Not Complete".

    Let me know if you think this will work for you!

    Here are some Help Center articles I used:


    Cheers,

    Genevieve

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

  • Christina Reid
    Christina Reid ✭✭✭✭

    Ok, I'm struggling on:

    "To do so, you would need to add in a helper column in both sheets that essentially is just looking to confirm if the Account Number is in both. You could have this be a simple checkbox column in Smartsheet, and input the number 1 in every row in the other sheet, when there is new data. This will check the box in Smartsheet when you run the Data Uploader workflow, if the Account Number has a match."

    I setup a column on my SmartSheet that I titled DL? and setup as a checkbox. I'm not sure what else to do? I'd prefer to not have to make any changes to the sheet that I'm dropping in for upload as it defeats the purpose of the upload feature to mark these things as new. Is there an expression that I need to setup and map to the DL? in Data Uploader. At one point I did have the formula working with the checkbox and I thought the expression was something as simple as =1 (this was the formula as it was setup by my SmartSheet implementation specialist).

    When I couldn't figure it out I change the column to text and I thought I had it when I entered a formula of =IF([Account Number]@row=NOTBLANK,"Yes","") as my source sheet showed updates only to the rows that had account numbers matching on both sheets, but they came back as #unparseable. I tried ISBLANK and MATCH with the same results.

    Any clearer instructions on that particular piece would be helpful, as you can tell it's the peice of the puzzle I need to make some of the other formulas work.

  • Christina Reid
    Christina Reid ✭✭✭✭
    Answer ✓

    You are awesome! That totally worked and I really appreciate it!!!

  • I'm so glad that worked for you! Thanks for letting me know 🙂

    Need more help? 👀 | Help and Learning Center

    こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!