Need Help with Auto-Generating Project IDs in CopyRow Automation

Hello Everyone,

I'm looking for some advice on automating Project ID generation in our CopyRow Automation process. My department uses this tool for project intake when sending work between teams. However, we need to include a unique Project ID that transfers to the destination sheets.

Currently, our project managers spend a lot of time manually linking cells. They want to start using DataMesh, but we're hitting a roadblock: we can't use the AutoNumber column for this because the destination sheet can't have a pre-existing column name when using CopyRow.

I'm considering creating a Project ID using a formula in the Text/Number column, possibly with a combination of Prefix and Suffix helper columns. The problem is, I'm stuck on how to generate the unique number itself.

Has anyone dealt with this or have suggestions on how to approach it?

Thanks in advance for your help!

Answers

  • Adam Murphy
    Adam Murphy ✭✭✭✭✭✭

    With DataMesh you can choose the data mapping yourself, so if you generate the project ID in your intake sheet with Autonumber you can then use DM to update downstream sheets where you set that source field to map to destination sheet "Project ID" text field. Would that work?

  • Ryan Holguin
    Ryan Holguin ✭✭✭✭

    Hey @Adam Murphy - Thanks for the response. The problem we run into is this, since we use the CopyRow automation for project intake:

  • Adam Murphy
    Adam Murphy ✭✭✭✭✭✭

    Ok, I think you can either rename the column in question so it does not match, or you can eliminate that automation and use Data Mesh instead. Please let me know if I am misunderstanding your question.

  • Ryan Holguin
    Ryan Holguin ✭✭✭✭

    @Adam Murphy

    It sounds like you're on the right track. However, there's a limitation: the automation can only run once because having an auto-number column with the same name on both sheets isn't possible. This could be a roadblock, especially since we rely on the automation for project intake. If we only use DataMesh, it might lead to QC issues.

  • Adam Murphy
    Adam Murphy ✭✭✭✭✭✭

    I just tried this with a couple of sheets and was able to copy rows from one to the other without issue where each had an autonumber column with the same name (Row ID) and existing rows, both manually and via automation. I renamed the Row ID column on sheet 1 to Row ID 2 and it still worked without any errors both manually and via automation.

    Can you please describe how to reproduce the error between your sheets?

    For Data Mesh, what QC issues would arise? If set to mesh immediately it should keep your central sheet very up to date. I would say it is no less reliable than any other part of Smartsheet i've used.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!