Two part form to one line on data sheet

dbussiere

I have 2 forms that are capturing information from two audiences on the same primary key.

1. Can I get the data from the Primary Key to become a drop down field in a second form?
2. How can I get data from both forms referencing the same Primary Key to record on one line in the data sheet?

Form 1 link: https://app.smartsheet.com/b/form/18a84ba1412e4152b56bd30c6868e1e1

Form 2 link: https://app.smartsheet.com/b/form/8dc3177db9da4d5ea2587681ea0c4119

Sheet link: https://app.smartsheet.com/sheets/27fR24VqqpChVmH9q47m27WPF2j8Hfjm5Q7PR7v1

I am very new to SmartSheets so any tips/tricks are welcome. I used the RFP template as a start for this and will delete what's not needed when complete.

Thanks!

dru

Scott Orsey

Hi @dbussiere , To answer your questions straightforwardly:

1) You can't make a dropdown on a sheet (or form) based on the values in a row or another sheet. Dropdown values must be set (hard coded) for the column within the sheet. Of course, you can set the column to accept values beyond those in the dropdown, but that won't make them appear in the dropdown in the future.

2) Form submittal will create a new row in your sheet. A form cannot be used to add to an existing row.

I believe what you are trying to do is have the first audience initiate the form and create a new row and then the second audience add additional data to the row. Is that correct? If so, then one easy way to do this would be to use an automation to trigger an "update request" once the the row is created. The update request would be sent to your second audience and you can specify the fields that they can see or edit. Those fields will appear in a form-like web tab just like your original form, but you don't have all of the same formatting/customization options as a form would. Any changes they make will be saved to the row within the sheet.

I'd start with that and see what you think. There are more complicated solutions that would result in two sheets that you would cross-reference with your primary key, but that will step up the complexity a lot for a SS newcomer.

Good luck and be well!

Andrée Starå

Hi @dbussiere

I hope you're well and safe!

• Can I get the data from the Primary Key to become a drop down field in a second form?

[Andrée Starå] Yes, but you'd have to add the premium app, Data Shuttle*, the Smartsheet API, or a third-party service.

• How can I get data from both forms referencing the same Primary Key to record on one line in the data sheet?

[Andrée Starå] The simplest way would be to have the main form on the main sheet and the second form on a separate sheet and then use cross-sheet formulas (VLOOKUP or INDEX/MATCH) to collect the information from form two to the main sheet.

Make sense?

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, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

*Feel free to reach out at andree@workbold.com, and I'd be happy to help you with the Data Shuttle license and more.

Scott Orsey

Hi @dbussiere , To answer your questions straightforwardly:

1) You can't make a dropdown on a sheet (or form) based on the values in a row or another sheet. Dropdown values must be set (hard coded) for the column within the sheet. Of course, you can set the column to accept values beyond those in the dropdown, but that won't make them appear in the dropdown in the future.

2) Form submittal will create a new row in your sheet. A form cannot be used to add to an existing row.

I believe what you are trying to do is have the first audience initiate the form and create a new row and then the second audience add additional data to the row. Is that correct? If so, then one easy way to do this would be to use an automation to trigger an "update request" once the the row is created. The update request would be sent to your second audience and you can specify the fields that they can see or edit. Those fields will appear in a form-like web tab just like your original form, but you don't have all of the same formatting/customization options as a form would. Any changes they make will be saved to the row within the sheet.

I'd start with that and see what you think. There are more complicated solutions that would result in two sheets that you would cross-reference with your primary key, but that will step up the complexity a lot for a SS newcomer.

Good luck and be well!

dbussiere

Thanks for the quick response. Of course I have to be difficult! I like the request an update automation however, the two forms (or parts) are being completed by external people and not someone necessary known on the contact list. The scenario is a split commission on a sale. One initiates and the other closes, with the Job Name being the common denominator. I'm up for complexity ;)

Andrée Starå

Hi @dbussiere

I hope you're well and safe!

• Can I get the data from the Primary Key to become a drop down field in a second form?

[Andrée Starå] Yes, but you'd have to add the premium app, Data Shuttle*, the Smartsheet API, or a third-party service.

• How can I get data from both forms referencing the same Primary Key to record on one line in the data sheet?

[Andrée Starå] The simplest way would be to have the main form on the main sheet and the second form on a separate sheet and then use cross-sheet formulas (VLOOKUP or INDEX/MATCH) to collect the information from form two to the main sheet.

Make sense?

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, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

*Feel free to reach out at andree@workbold.com, and I'd be happy to help you with the Data Shuttle license and more.

dbussiere

Thank you @Andrée Starå ! Simple spreadsheet basics - so obvious!

Andrée Starå

@dbussiere

Happy to 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/Awesome or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!