copying a row twice and giving each row a unique identifyer

Hi I have a created an audit form that can have 2 action items. To control each action item individually, I want to copy the row to a separate sheet with a unique identifyer. I'm able to copy the row 2 times based on a workflow condition. the copied rows are identical twins. Because both action information is in one row I need to remove the information of the first action to prevent the user to be confused. i have tried it with formulas but because I don't have a unique identifier I can't separate the data. does anybody have a tip for me?

Thx

HIlbert

Answers

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    Hi @Hilbert Kok

    I hope you're well and safe!

    Could you use the Auto-Numbering column?

    Would that work/help?

    I hope that helps!

    Be safe and have a fantastic weekend!

    Best,

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    If the moves are done at different times, In your second sheet (where the rows are being moved to,) you could use the system column "Created," which has the full date/time that the row was moved, as a unique identifier, or to create a unique identifier by stripping some of the data from the date/time value.

    For example, if Created on the row is 09/30/21 6:04 AM, create a helper column called "CreatedText" to change it to text by adding a + sign and a pair of double quotes:

    =Created@row +""

    Then build a Unique Identifier text string based on criteria:

    =MID(CreatedText@row, 7, 2) + LEFT(CreatedText@row, 2) + MID(CreatedText@row, 4, 2) + IF(MID(CreatedText@row, 11, 1) = ":", "0"+MID(CreatedText@row, 10, 1)+MID(CreatedText@row, 12, 2), MID(CreatedText@row, 10, 2)+MID(CreatedText@row, 13, 2)) + RIGHT(CreatedText@row, 2)

    Which will result in "2109300604AM". Add some value from another field in the row (perhaps your Action) and you'll have a truly unique row.

    =Action@row + MID(CreatedText@row, 7, 2) + LEFT(CreatedText@row, 2) + MID(CreatedText@row, 4, 2) + IF(MID(CreatedText@row, 11, 1) = ":", "0"+MID(CreatedText@row, 10, 1)+MID(CreatedText@row, 12, 2), MID(CreatedText@row, 10, 2)+MID(CreatedText@row, 13, 2)) + RIGHT(CreatedText@row, 2)

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Hilbert Kok
    Hilbert Kok ✭✭✭✭✭

    Hi guys, thx for your response,

    I have tried both but it won't work because the row is copied twice at the same time and as such it has the same date stamp.


    This the result:

    # Row- id created Jeff's formula


    2 40 28-01-22 15:46 222801154646

    2 41 28-01-22 15:46 222801154646

    3 43 28-01-22 15:56 222801155656

    3 44 28-01-22 15:56 222801155656

    I thought of another idear. If i look at the content of the copied field I could compare the content with an if/then statement. Like if row 1 = row 2 then 1 else 2. The problem is that colomn formulas only allows @row and not @47 for example. Is there a way to use like @row + next row?


    cheers

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    2 40 28-01-22 15:46 222801154646

    2 41 28-01-22 15:46 222801154646

    3 43 28-01-22 15:56 222801155656

    3 44 28-01-22 15:56 222801155656

    What the values in BOLD ITALIC? Where are they coming from?

    Regards,

    Jeff Reisman

    Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages

    If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Hilbert Kok

    Happy to help!

    Why doesn't the Auto-number solution work? It should because the number would be unique. (it should be added to the Destination sheet)

    Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Hilbert Kok
    Hilbert Kok ✭✭✭✭✭

    Hi guys, really appreciate your feedback.


    Hi Jeff the number in bold is the Row ID .

    Hi Andrée, the Row ID indeed creates a unique number, but How do I then, based on the unique number, which cell I need to use. I'll explain a bit more in detail.


    step 1, Form is filled with for example 5 actions, with action description, owner and due date, This is then submitted into sheet

    step 2, In sheet I have now one row with the 5 actions, which makes it difficult to manage for the user, so I have created a workflow that will copy the row to a new sheet 5 times.

    Step 3. I know need to have each row (with the same information) to copy the action information into a central action field (in the row) so the user only has to look at one set of fields.

    I can't get this to work, the Row ID add's one number for every row added, But I don't know which number belongs to which row, so I cant create a workflow or a formula.


    I got it to work for max 2 lines using ISODD and ISEVEN to identify the uniqueness.

    =IF(ISEVEN([Row ID]@row); [action 1]@row; IF(ISODD([Row ID]@row); [action 2]@row; ""))

    Using this for more then 3 it won't work. 3 actions will have either 2 odd and 1 even or 2 even and 1 odd, etc.

    So i'm stuck, I have implemented now max 2 actions but the user would like to see max 5 actions.


    I hope this makes it a bit more clear.


    Hilbert

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭

    @Hilbert Kok

    What about adding an auto number as an ID on the first page?

    Would that work/help?

    Remember! Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

    Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

    W: www.workbold.com | E:[email protected] | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!