User Portal v Update Row v Form with URL Query v??

LAW

We need to collect data from over a thousand volunteers on an annual basis. Last year, we collected the basic info using forms. This year, we would like to get the returning volunteers to confirm or update their basics and request new information.

Objectives would be a) to collect/update data in one fell swoop, rather than send multiple requests for sections of data b) never ask the user to retype information they already provided c) make sure no one but the user (and the admin) is able to see or update their data d) allow for updates from the user more than once..for instance as the user gets more data, they can go back to the request and update.

There are likely multiple ways to complete this workflow and I would like advice on the options and any pros and cons for each.

Right now, we are thinking about the following ideas:

Intake form: Gather all the information via an Intake FORM, compare with existing data using the (mutually exclusive) volunteer number and then merge the two rows of data. Unfortunately, this would make the user retype all the info they gave us last year.

I just read something about a URL query in forms to pre-populate data - should I pursue this?

Row Update: Send the user a ROW UPDATE request from the underlying sheet. The underlying sheet has their existing information and we have also add the columns (now blank) for the new info we need this year.

User Portal: We drafted a user portal "page" and like the concept but not sure how that would work for non-licensed people.

Any insight on the pros/cons for each of the above OR other paths are appreciated.

Adam Murphy

You are correct there are several ways to do this, with tradeoffs, and most depend on your licensing (not the end user, if you do this correctly they should not require a license). As you seem to know, forms cannot be pre-filled via a lookup, but can be pre-filled using url query strings. This would mean a custom URL for each user (which you could generate via a formula and email out to users assuming you have email address) and does still generate a new form submission (row) in your sheet, so using the form as an intake and have records moved to another sheet then using cross-sheet references to check for dupes, etc. Update requests work by send a single use form (that you cannot customize directly) to the user allowing them to update whatever fields you specify (does not have to be the entire row). Because they are single use you would need a mechanism for users to request a new update request (that could be a call/email to you and you manually sending it, or you can get creative and automate something through a dashboard). User portal or dashboard is great option, depending on your licenses. I like dynamic view and workapps for making portals, but you can do some things with a simple dashboard such as embed a report with a filter set to "current user" for the email field, and make them create an account (free) and login to view…if it is blank, they are new, so you can also embed a form to submit and instructions. If they have a record you can have the report open when they click on it and allow them to edit values in the report.

Good luck!

Adam Murphy

You are correct there are several ways to do this, with tradeoffs, and most depend on your licensing (not the end user, if you do this correctly they should not require a license). As you seem to know, forms cannot be pre-filled via a lookup, but can be pre-filled using url query strings. This would mean a custom URL for each user (which you could generate via a formula and email out to users assuming you have email address) and does still generate a new form submission (row) in your sheet, so using the form as an intake and have records moved to another sheet then using cross-sheet references to check for dupes, etc. Update requests work by send a single use form (that you cannot customize directly) to the user allowing them to update whatever fields you specify (does not have to be the entire row). Because they are single use you would need a mechanism for users to request a new update request (that could be a call/email to you and you manually sending it, or you can get creative and automate something through a dashboard). User portal or dashboard is great option, depending on your licenses. I like dynamic view and workapps for making portals, but you can do some things with a simple dashboard such as embed a report with a filter set to "current user" for the email field, and make them create an account (free) and login to view…if it is blank, they are new, so you can also embed a form to submit and instructions. If they have a record you can have the report open when they click on it and allow them to edit values in the report.

Good luck!

LAW

Sounds like the portal idea with embedded files would be nice, but given all the other priorities I may go with the row update this year and tackle the portal when there is more time. Row updates with the email intros are easily segmented by user by asking only for columns needed, and zero work on the back end matching data and eliminating dups. I can also use a trigger for another row update if we see missing data by x date. While it is not user-driven I think this will serve our purposes.

If you can think of any tips or small nuances for this path, pls let me know.

I appreciate the thoughtful response as planning insight decreases pain in later stages.