Two Forms working in conjunction?

J.Barrow

The brain power of this community is amazing so I am going to put it to the test and see if anyone could help me solve my design idea and share how to do it.

The idea is this, it is for site visit inspections. I want to have one form that gathers the data for the visit, dates, people who need to attend, automatically send outlook appointments, etc.. This form would generate a Visit ID auto generated. Then I would have a second form that then populates the inspection findings and action items. this would have notes, Pictures attached, corrective actions, due dates and assigned people to complete the actions. (think work order form). This would also generate an Observation Number auto generated, but hopefully would link to the original Visit ID automatically or easily in the form without having to remember what the ID was each time.

Just for clarity I don't have any of the fancy upsell or add ons as I originally thought that I could use a drop down tab preloaded with the Visit ID on the second form and use that to connect it, but this community has tons of discussions on loading a dropdown from another sheet and I don't have data loader or whatever it is called that is not an option.

I could build a sheet for each inspection that links one cell to another, but without automation I might as well just write a word document each time as that would be faster than building a new sheet from a template and linking the cells together.

The goal is that when I design it and it works properly I or any of my team members can do all of this work from a smartphone in the field taking pictures and assigning follow up items to people and then I can run reports and dashboards filtered by the Visit ID to give to each site and my boss after each visit

here are some pictures of what I am trying to do at this time there are more cells to the right on both forms but no need to show them.

Form 1 with visit ID (this will have generic information for the visit)

Form 2 with both Visit ID (but I had to manually impute Visit ID. I wish it was easy to have it auto fill a drop down on Form 2 or to be linked in some way? Maybe by date or ???)

So let the mid trust solve my problem or suggest a better way to do my desired results!

Paul Newcome

Have you looked into using a form first but then using update requests after?

J.Barrow

@Paul Newcome, No I have not, but I have two different sheets. How do you seeing automating that flow to possibly fix that issue? I don't see how that would populate the right information in Visit ID on the second sheet. or allow me to have multiple "sub" items to be loaded as I have two different criterias for the sheets as I want to have multiple "findings" in sheet two for each visit.

BullandKhmer

Hi Mate,

@Paul Newcome 's idea will work but there are a few limitations with update requests.

I've build a few solutions like this in the past. The workaround is pretty simple actually. INDEX(MATCH()). You will need two equal + unique fields in both sheets for this to work and honestly, the easiest way to do that is by using the visit ID and requiring the observer to enter that number as the first field in the dropdown box.

You second option: Are there any matching but unique fields in both forms that you can parse and then use as a unique ID to INDEX?

The final option (and best in my view) is to install a dynamic dropdown in the observation form. This way the observer can select from a selection of Visit ID's (or addresses, or names, or phone numbers) as the first field. This will required a DDUpdate lisence/ subscription a helper sheet, and is relatively complex to set up.

Any one of the three options you choose from above, you can simply use an index match formula on your visit sheet to combine the data, i.e.

INDEX({Data I want},MATCH([Visit ID]@row,{Visit ID},0))

Let me know how you get on and if you need any other help.

Adam.

J.Barrow

@BullandKhmer,

Thanks for this idea, I wish my company had the DDUpdate license, but it does not. 😫

So with my original pictures up above in my original post, using your idea of INDEX(MATCH()) The data I want pulled over from Form 1 is the Visit ID and the data that I will match would be the following columns, (Location Column as that is on both forms in a drop down format and then the matching the observation Date falling between the Visit start and End Dates. of form 1.

Could this be done and help me understand how to do that with INDEX(MATCH()) as I had no luck.

BullandKhmer

Hey,

DDUpdate is like $5 a month.

Lets say you wanted to pull the Location into your sheet, the basic column formula is =INDEX({Location},MATCH([Visit ID]@row,{Visit ID},0))

You will need matching visit IDs on both sheets, so without DDUpdate or another solution the visit ID will need to be manually entered on the observation form.

J.Barrow

@BullandKhmer,

Thanks, Unfortunately we are a huge company with ton's of licenses so the DDUpdate is way more expensive of a cost and I am not able to change what add ons the company buys.

Unfortunately the information I am trying to Auto pull in is the visit ID and yes the team could type in the Visit ID, but that manual entry without the drop down will inevitably have typos and errors with the number of times and number of people that will be entering it.

So that was why I thought the data to Index and Match while a harder formula could be the Location and the date ranges vs the date of the finding.

Then all reports and data will pull from one data source of the Visit ID allowing me to easily pull reports and data from multiple forms by the Visit ID that will be the same on all forms/sheets

J.Barrow

@Paul Newcome and @BullandKhmer,

Ok I think I am really close and just need to find what is giving me the #UNPARSEABLE error.

So I got this to work for just the date ranges:

=INDEX(COLLECT({01 New Visit Details Range 3}, {01 New Visit Details Range 1}, @cell <= [Observation Date]@row, {01 New Visit Details Range 2}, @cell >= [Observation Date]@row), 1)

But I needed to add the location name to both forms so that I could do date ranges and location to eliminate if two visits by two different people happened on the same day. so I tried to do this:

=INDEX((COLLECT({01 New Visit Details Range 3}, {01 New Visit Details Range 1}, @cell <= [Observation Date]@row, {01 New Visit Details Range 2}, @cell >= [Observation Date]@row), {01 New Visit Details Range 4}, @cell = [Resort Name]@row, 1)

This formula is the one that comes back #UNPARSEABLE error. Any ideas?

BullandKhmer

https://community.smartsheet.com/discussion/comment/371268#Comment_371268

Hi,

There were a couple of misplaced brackets and I think you can just remove the @cell stuff right? so....

=INDEX(COLLECT({01 New Visit Details Range 3}, {01 New Visit Details Range 1}, <= [Observation Date]@row, {01 New Visit Details Range 2}, >= [Observation Date]@row), {01 New Visit Details Range 4}, = [Resort Name]@row), 1)

You should also consider naming your cross sheet references.

Paul Newcome

@J.Barrow There is an extra opening parenthesis between the INDEX and COLLECT functions that needs removed, and the closing parenthesis after [Observation Date]@row needs to be moved to after [Resort Name]@row,

=INDEX((COLLECT({01 New Visit Details Range 3}, {01 New Visit Details Range 1}, @cell <= [Observation Date]@row, {01 New Visit Details Range 2}, @cell >= [Observation Date]@row), {01 New Visit Details Range 4}, @cell = [Resort Name]@row, 1)

changes to

=INDEX(COLLECT({01 New Visit Details Range 3}, {01 New Visit Details Range 1}, @cell <= [Observation Date]@row, {01 New Visit Details Range 2}, @cell >= [Observation Date]@row, {01 New Visit Details Range 4}, @cell = [Resort Name]@row), 1)

J.Barrow

@Paul Newcome AND @BullandKhmer,

Thanks I have all my sheets connected and pulling the right visit ID's making reporting easy as I just filter by ID!

Paul Newcome

Happy to help. 👍️