topic Re: I need a spreadsheet like grid solution in ArcGIS Survey123 Questions

I need a spreadsheet like grid solution

ChipMorgan65 — Mon, 04 Mar 2024 21:49:05 GMT

Hi,

I have a customer that requires a true spreadsheet/paper form grid in Survey 123.

I need to implement a Survey 123 digital form for species data collection. I'm trying to recreate the Carolina Vegetation Survey Level 5 species form (see attachment) and (separately) the Virginia Natural Heritage Program data collection protocols.

Each of these workflows depend on the ability of the data collector (i.e., the human) being able to visually review several dozens (or more) of species records in the same screen (with scrolling allowed).

Further (and related to the above), each species requires that several questions be answered. These questions (particularly on the CVS Level 5 form) are not completely filled out species by species (row by row), but are typically filled out question by question (column by column).

Defining a Survey 123 repeat does not meet this need since you can only see one species at a time. Imagine having 70 species in the list (which did occur on one of my samples). Say that you're on species 70 and you need to return to species 1 to answer the next "column" of questions. Are you really going to have the data collector click the form's "left/previous" button 69 times to get back to species 1? And what happens if you need to answer the questions non-sequentially?

Survey 123's repeat is just not the right UI structure to support this workflow.

I've heard of this similar need from multiple conservation organizations. They have separately described prior attempts by experienced developers using Survey 123 (and other software) who have built what turn out to be unusable/impractical digital forms, primarily due to the developer not understanding the workflow of the data collector and the need to see all of the species/questions at once.

Each of these organizations prefer to collect data by hand and hand transcribe their forms because it is faster and simpler than trying to use a "repeat" style digital form that hides 99% of the data.

There's gotta be a way to address this.

Re: I need a spreadsheet like grid solution

abureaux — Mon, 04 Mar 2024 22:04:40 GMT

Short answer is: that cannot currently be done.

The web form is a little better since repeats are added as sequential blocks, but nothing like a grid though. I would also love for repeats to be configurable like this and have had similar requests.

Re: I need a spreadsheet like grid solution

ChipMorgan65 — Mon, 04 Mar 2024 22:05:01 GMT

My working approach is to:

Using Survey123 gridding (obviously)
Shrink the questions down with <font style=x> to make them as small as possible to take up less room both vertically and horizontally.
Define a series of question variables with xxxx suffix, such as species_0001, species_0002, etc., up to a max that the customer can live with (probably 100-200).
Do the same for each question strata_tree_0001, strata_tree_0002, etc, strata_shrub_0001, strata_shrub_0002, etc.

I'd build build an XLSForm group of entries that covers one "row" of the form, then replicate that group of XLSForm entries x times (100 - 200) and increment the variable numbers/suffixes.

I'm not going to do it by hand. Instead, I'd probably use Python and openpyxl to generate the spreadsheet entries and copy the completed spreadsheet (somehow) into the actual XLSForm.

On the backend side, I'd need to have a Python script to process the survey data and generate a more manageable feature set with a related table containing the species information. From there, I'd review/analyze the results like normal.

I'm not sure this is worth this much effort, but I'm at least going to think it through.

Thoughts?

Re: I need a spreadsheet like grid solution

ChipMorgan65 — Mon, 04 Mar 2024 22:07:20 GMT

@abureaux, thanks for the quick reply! I know I'm not alone here.

Re: I need a spreadsheet like grid solution

abureaux — Mon, 04 Mar 2024 22:11:43 GMT

For sure! There are definitely a lot of people that both want this feature added, and would benefit greatly from its additional.

This feature specifically is on my short-list of things to ask Esri for when I get a chance.

Re: I need a spreadsheet like grid solution

DougBrowning — Mon, 04 Mar 2024 22:28:02 GMT

I think this is the idea for upvote from 2019. https://community.esri.com/t5/arcgis-survey123-ideas/table-view-for-repeats/idi-p/938544

I use a repeat and have all my questions for that species in the one repeat. Have a dup checker and a summary page and it gets us by. You will not be able to recreate excel so changing the workflow can help. It is not paper so the workflow usually needs to be adjusted.

This style may work for you? https://community.esri.com/t5/arcgis-survey123-ideas/table-view-for-repeats/idi-p/938544

Re: I need a spreadsheet like grid solution

ChipMorgan65 — Mon, 04 Mar 2024 22:39:54 GMT

@DougBrowning, thanks for pointing this out. It does answer some of the issue, and I really appreciate the ideas it utilizes.

However, there are some workflows that really are the way they need to be, and this is an example of that. It's not a good response to the customer in this situation that they should change their workflow because my software can't support their current approach.

Thanks again. I do appreciate it.

Re: I need a spreadsheet like grid solution

ChipMorgan65 — Mon, 04 Mar 2024 23:14:47 GMT

I've gotten this far with manually configuring the XLSForm. I think the form is usable, but am definitely not going to build 100+ rows by hand.

This is what the XLSForm looks like for the first three rows:

Re: I need a spreadsheet like grid solution

DougBrowning — Mon, 04 Mar 2024 23:22:13 GMT

I just mean that sometimes the best workflow for paper is not the best workflow on a tablet. It can be better even. Never hurts to review the process when changing the way it is collected.

Re: I need a spreadsheet like grid solution

ChipMorgan65 — Tue, 05 Mar 2024 00:23:36 GMT

Yeah, I definitely agree with you on that. Sorry for coming across too strongly.

Re: I need a spreadsheet like grid solution

TylerGraham2 — Tue, 05 Mar 2024 23:13:05 GMT

You could always copy/paste the block of questions and since it is in Excel use Find >Replace to change the pasted question block's appendix number. e.g. Find "_0002" and Replace with "_0003".

Or Esri could get around to giving us repeat grids and help everyone out.

Re: I need a spreadsheet like grid solution

ChipMorgan65 — Wed, 06 Mar 2024 16:51:24 GMT

@TylerGraham2 agreed on both counts!

Re: I need a spreadsheet like grid solution

suntoygeo — Fri, 29 Mar 2024 01:04:46 GMT

Super agree, and agree that this affects many natural resources/conservation agencies. I made this idea post a little while ago, but your description is better. Improve forms/related records workflow to support ... - Esri Community