Matching new webform data to existing data

Options

Hi There,

I have a sheet that automatically populates with data from a linked webform. This is the EOI stage. If the EOI applicant meets certain criteria those applications are sent to certain project team members for approval. Once approved by project team members the applicants are sent a round two application form. The data from the round two webform applications then automatically populate into a new sheet in the same workspace.

What I want to do is match some of the details from rows in the approved EOI applications to new data coming in from the round two applications. Are you able to advise how I would do this. At the moment the new form just creates rows below the fields that I want to match from the EOI process. I am thinking it might be the INDEX/MATCH functions at this stage and would be grateful for any assistance that could be provided.

Thanks.

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options
  • Sean McNulty
    Options

    Thanks for your reply Paul. I did look into this, however I need the applicant/ supplier to fill in a new form that's on the formal application and I want to match this or join this with existing data I have in another sheet.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Ok. Do you have a unique identifier on each row for both sheets?

  • Sean McNulty
    Options

    Hi Paul,

    Yes I have,

    EOI Register has ABN and the matching row in the other sheet is ABN/ACN same data different heading

    (basically the ABN Numbers match)


    Thanks

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Ok. So the way an INDEX/MATCH works is the MATCH function automates the row number based on the unique identifier so that the INDEX function will know which row to pull from.

    =INDEX({Other Sheet Column To Pull}, MATCH([Unique ID]@row, {Other Sheet Unique ID Column}, 0))

  • Sean McNulty
    Options

    Thanks Paul,

    I am now totally confused! Essentially what I am trying to do is to merge or sync data from a previous form to the data coming in from another form. So in the snippet below rows 1 to 84 are copied cells from the EOI register and cells 85 and 86 is new data coming in from the Formal Application form attached to this below sheet.

    As an example row 22 (Freedom Constructions Queensland) is the data copied in from the EOI register and as you can see when we have sent the new Formal Application form from the new sheet the data is added to row 85. I somehow need to bring row 22 and row 85 together including the attachments. The data for both EOI and the Formal Application will continue to be collected through these forms for at least the next six months so it is not static and ideally I need it to auto populate the data comes in.

    Thanks in advance. If you would like a direct copy of the SmartSheet I can also share that with you.

    Sean.


  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    Now I am confused. In one post you talked about 2 different sheets, but in your last post you talked about 2 different rows on the same sheet. Are we working in one sheet or two?

  • Sean McNulty
    Options

    Hi Paul,

    Form/Sheet 1- Register 1

    Form/Sheet 2- Register 2


    Customer fill out form that populates Register 1. If data matches a certain criteria it triggers a workflow for approval and once approved copies selected data from the row on register 1 to Register 2.

    Once data is visible on register 2 second form is sent for additional data. When form completed a new row is created in Register 2 at bottom of the sheet.

    I then need to manually match the row data that has dropped in from Register 1 with the new data that populates in register 2.

    Not sure if this is achievable and also how/if attachment across to row can merge when completing such a task?

    Hope this makes sense

    Cheers

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

    Hi @Sean McNulty,

    Second form

    I’d recommend using an Update Request instead if possible for the second form process like Paul mentioned earlier because then you don’t need to match the rows together.

    Is there a reason why that wouldn’t work, and if yes, what is the reason?


    Re. attachments

    Unfortunately, it's not possible at the moment to merge different rows attachments to the same row, but It's an excellent idea!

    Please submit an Enhancement Request when you have a moment

    It would be a manual process to save from one row locally and then upload those to the "main" row, but if you use an Update Request, they attachments will already be on the same row.


    Would that work?

    I hope that helps!

    Be safe and have a fantastic week!

    Best,

    Andrée Starå

    Workflow Consultant / CEO @ WORK BOLD

    Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. 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:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

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

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Options

    @Sean McNulty I do feel like an Update Request would work for this. I know you said you wanted to send a second form, but the Update Request is filled out as a form. This would allow the person to update the same row and would make the process much easier if it is an option. This would also allow the user to attach new items to the row since you cannot merge row attachments.

  • Sean McNulty
    Options

    Thanks Gents,

    It sounds like it would work if you only had to updates the same columns and or cells.

    The problem I have is the first form has approx 20 data sets and the second form has over 50 additional data points How can I link the second form to a request update?

    Thanks for your assistance

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    edited 04/30/20
    Options

    @Sean McNulty

    You're more than welcome!

    You'd use the Update Request instead of the second form, and you can select which fields should be included in the request. Similar to how you set up the form.

    Make sense?

    More info about the update request:


    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

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

  • Jason Albrecht
    Jason Albrecht ✭✭✭✭✭✭
    Options

    G'day Sean,

    I know it's a few years since you posted the above question.

    As an Aussie, I have a suspicion you can help me with an ABN dilemma, and would like to ask you a few questions.

    I have also used the 'update request' solution and could possibly help with formatting the emailed (form) requests.

    Hopefully you get this notification and are willing to chat offline.

    Jason

    Hope this helps and that you have a great day,

    Jason Albrecht MBA, MBus(AppFin), DipFinMgt

    LinkedIn profile - Open to work