Survey123 Connect populates multiple excel tabs upon export

Question

Hello! I am having issues exporting my Survey123 data to Excel. I am using Connect to build my survey for reference. Upon export, my Excel file has multiple tabs instead of just one. My survey does include repeats. For further reference, repeats are included in the survey for when a survey taker sees the same type of wildlife sign again and needs to add an extra data point. Is there a way to condense the exported Excel sheet into a single tab? Or a different way to structure the survey in Connect so I don't have this issue? Thanks in advance!!

(Side not: I am also having issues with the data output in Survey123 showing text and note fields as a field type... how do I include a text field in a survey without it showing up in the field type of the data? I have tried putting 'null' in the 'bind::esri:fieldtype' but the field still shows up upon viewing the output data..)

NathanMack · Answer

Hi Vicki, I have a very similar survey. I embed StationCode in each repeat as my relatable item. Given the size of our project, I also embed SampleDate, ProjectCode, EventCode, etc. These together make each unique a identifier for each sampling event. In retrospect, I should have probably should have used the parent/global IDs. This is an example of how you can embed StationCode in each repeat all the same. typenamelabelcalculationbegin groupGenSiteInfoGeneral Site Information ${genSiteInfo_complete}select_one StationCodeStationCodeStation Code datesample_dateSample Date begin repeatTransectTransect: ${Trans} hiddenT_StationCode${StationCode}hiddenT_SampleDate${sample_date}decimalwwWetted width decimalbfwBankfull width decimalbfhBankfull height end repeat begin repeatIntertransectIntertransect: ${intertrans} hiddenI_StationCode${StationCode}hiddenI_SampleDate${sample_date}decimalinter_wwWetted width end repeat

ClayDonaldsonSWCA · Answer

Survey123 builds relationships based on `globalid` to `parentglobalid` fields. ObjectID is not part of the relationship class.

You can join in arcgis pro based on those fields, or use something like xlookup()in excel. Here is an example:

* Sheet_1 ("Stations") contains a list of stations with a globalid.
* Sheet_2 ("Survey Results") contains survey results with a parentglobalid, which refers to the globalid in the "Stations" sheet.

Sheet 1 ("Stations")

Station Name globalid

Station A123abcStation B456defStation C789ghi

Sheet 2 ("Survey Results")

Survey Date parentglobalid Result

2024-01-01123abcPass2024-01-02456defFail2024-01-03789ghiPass2024-01-04123abcPass

You want to lookup the corresponding Station Name in Sheet 1 for each survey in Sheet 2 using the relationship between parentglobalid (in "Survey Results") and globalid (in "Stations").

In Sheet 2, to lookup the Station Name for each survey based on the parentglobalid, you can use the following formula in a new column (e.g., in cell D2 of "Survey Results"):

=XLOOKUP(B2, 'Stations'!B:B, 'Stations'!A:A, "Not Found")Explanation:

* B2 The parentglobalid value in "Survey Results" (Sheet 2).
* 'Stations'!B:B: The column in "Stations" (Sheet 1) that contains the globalid.
* 'Stations'!A:A: The column in "Stations" (Sheet 1) where we want to return the Station Name.
* "Not Found": Optional message if a matching globalid is not found.

Survey Date parentglobalid Result Station Name

2024-01-01123abcPassStation A2024-01-02456defFailStation B2024-01-03789ghiPassStation C2024-01-04123abcPassStation A

VickiMcKay · Answer

Hi Clay,

I am having the same issues and I didn't find your answer clear. I also have repeat groups within my form, often for images related to a section. I don't necessarily need all the data in a single tab, but I do need to be able to identify which station the data in each tab belongs to. Right now there is an ObjectID on each tab of the spreadsheet, but these appear to be unique numbers that don't clearly link the data on one tab to matching data on another tab. How do I link all the tabs to a Site Code that is entered in the very first section of the form? Do I need to do anything on the form itself, like repeat the Station Code in every section? Is there a way to add the Station Code to each section based on the Station Code entered in the first section (i.e. not have the user re-enter the information)? I have three forms like this. Thanks for your help.