Help processing multiple_select selections to create a table of values

[Strahanjen]

I have a few groups of multiple_selects that I would like to process and turn into a table in the format in the attached image. I've seen information on setting up hidden fields to capture selections of the multiple_select so that I don't wind up with a bunch of comma separate values in a single column. I'd like to not have a column for each of these responses, however, but a table like a repeat would generate. I don't want the user to have to use a repeat for these selections because it would take a lot of time. 

Any suggestions on whether there is any code I can write to process the multiple selects and generate a hidden table that can be submitted? 

[DougBrowning · Accepted solution]

Yea select multiple look nice and are easy for the user but they create data that is super hard to work with.

I do not think creating multiple repeat records on the fly is going to happen but I have some other ideas.

Personally I post process this using Python.  Here is my code snippet.

# Flips SpeciesList from value;vale;value to 1 value per row
fieldList = ["RecKey", "subPlotID", "subPlotDesc", "SpeciesCount", "SpeciesList"]
searchC =  arcpy.da.SearchCursor("in_memory/tempSpecRichSearch", fieldList)
insertC = arcpy.da.InsertCursor("in_memory/tempSpecRichInsert", fieldList)
for row in searchC:
# must check for Null and for end values (like aarp;)
    if row[4]:
        if row[4].endswith(";"):
            tmpRow = row[4][:-1]
        specList = tmpRow.split(";")
        for spec in specList:
            insertC.insertRow((row[0], row[1], row[2], row[3], spec))
del insertC

The other option is arcade depending on what you want to display.  This blog post has examples with comma list data.  https://www.esri.com/arcgis-blog/products/ops-dashboard/announcements/introducing-data-expressions-i...

Hope that helps

[Strahanjen]

Doug, many thanks for your code snippet and help once again. I like your idea of handling this with post-processing. I've learned a lot from your posts and responses and I'm appreciative of the time you have taken to share your knowledge. 

[DougBrowning]

What you really want is to grow a repeat down the page which is asked for a lot.  See this recent post with links to upvote the idea.  https://community.esri.com/t5/arcgis-survey123-questions/create-a-table-or-question-with-multiple-te...

I have ran into this several times and basically 3 options for now.

Select multiple and use post processing to arcade as I posted.

Create one field per option and then post process into a table.  The users like this cause its fast and easy.  The code is long but easy (I can post if you like).

Or use a repeat and slow the user down.  Some forms its not bad and some it triples the taps.

I have used all 3 at different times.  On yours I am not sure how you will keep Problem and the value lined up though.  Unless you named it somehow.

Hope that helps

[Strahanjen]

Doug, I'm going to have to treat you to dinner! I'd love to see your code for post processing into a table for cases where you have one field per option. I'll experiment with both of those approaches to see what is easier for creating the stressors list but also keeping the problem stressors in the same row. 

I did upvote the idea for growing down the repeat. That would be immensely useful!

[DougBrowning]

Ok here is a sample of one I did with a bunch of fields.  Crew like the speed of it. It starts at 10 but can grow to 15.  It does mean 45+ extra fields which slowed the form load down but users do not mind that.

In Connect it looks like this all written out

Then in Python I use insert cursors to split it back out into rows and all into the same table with one set of column names.  Again it looks long but its easy cause you can just copy paste each Particle and change the number.  

# need to do a cursor for each of the 15 particles
        #Particle 1
        fieldListIn = ["DimCanPartEvaluationID", "Transect", "Location1", "ParticleSize1", "NonParticleSize1"]
        searchC =  arcpy.da.SearchCursor(surveyGDB + "\\" + tableList[table], fieldListIn)
        fieldListOut = ["EvaluationID", "Transect", "ParticleNum", "StreambedLocation", "ParticleSize", "ParticleSizeClassNonMeas"]
        insertC = arcpy.da.InsertCursor(outDB + "\\" + table, fieldListOut)
        for row in searchC:
            insertC.insertRow((row[0], row[1], 1, row[2], row[3], row[4]))
        del insertC
        del searchC

        #Particle 2
        fieldListIn = ["DimCanPartEvaluationID", "Transect", "Location2", "ParticleSize2", "NonParticleSize2"]
        searchC =  arcpy.da.SearchCursor(surveyGDB + "\\" + tableList[table], fieldListIn)
        fieldListOut = ["EvaluationID", "Transect", "ParticleNum", "StreambedLocation", "ParticleSize", "ParticleSizeClassNonMeas"]
        insertC = arcpy.da.InsertCursor(outDB + "\\" + table, fieldListOut)
        for row in searchC:
            insertC.insertRow((row[0], row[1], 2, row[2], row[3], row[4]))
        del insertC
        del searchC

        #Particle 3
        fieldListIn = ["DimCanPartEvaluationID", "Transect", "Location3", "ParticleSize3", "NonParticleSize3"]
        searchC =  arcpy.da.SearchCursor(surveyGDB + "\\" + tableList[table], fieldListIn)
        fieldListOut = ["EvaluationID", "Transect", "ParticleNum", "StreambedLocation", "ParticleSize", "ParticleSizeClassNonMeas"]
        insertC = arcpy.da.InsertCursor(outDB + "\\" + table, fieldListOut)
        for row in searchC:
            insertC.insertRow((row[0], row[1], 3, row[2], row[3], row[4]))
        del insertC
        del searchC

        #Particle 4
        fieldListIn = ["DimCanPartEvaluationID", "Transect", "Location4", "ParticleSize4", "NonParticleSize4"]
        searchC =  arcpy.da.SearchCursor(surveyGDB + "\\" + tableList[table], fieldListIn)
        fieldListOut = ["EvaluationID", "Transect", "ParticleNum", "StreambedLocation", "ParticleSize", "ParticleSizeClassNonMeas"]
        insertC = arcpy.da.InsertCursor(outDB + "\\" + table, fieldListOut)
        for row in searchC:
            insertC.insertRow((row[0], row[1], 4, row[2], row[3], row[4]))
        del insertC
        del searchC

        #Particle 5
        fieldListIn = ["DimCanPartEvaluationID", "Transect", "Location5", "ParticleSize5", "NonParticleSize5"]
        searchC =  arcpy.da.SearchCursor(surveyGDB + "\\" + tableList[table], fieldListIn)
        fieldListOut = ["EvaluationID", "Transect", "ParticleNum", "StreambedLocation", "ParticleSize", "ParticleSizeClassNonMeas"]
        insertC = arcpy.da.InsertCursor(outDB + "\\" + table, fieldListOut)
        for row in searchC:
            insertC.insertRow((row[0], row[1], 5, row[2], row[3], row[4]))
        del insertC
        del searchC

        #Particle 6
        fieldListIn = ["DimCanPartEvaluationID", "Transect", "Location6", "ParticleSize6", "NonParticleSize6"]
        searchC =  arcpy.da.SearchCursor(surveyGDB + "\\" + tableList[table], fieldListIn)
        fieldListOut = ["EvaluationID", "Transect", "ParticleNum", "StreambedLocation", "ParticleSize", "ParticleSizeClassNonMeas"]
        insertC = arcpy.da.InsertCursor(outDB + "\\" + table, fieldListOut)
        for row in searchC:
            insertC.insertRow((row[0], row[1], 6, row[2], row[3], row[4]))
        del insertC
        del searchC

        #Particle 7
        fieldListIn = ["DimCanPartEvaluationID", "Transect", "Location7", "ParticleSize7", "NonParticleSize7"]
        searchC =  arcpy.da.SearchCursor(surveyGDB + "\\" + tableList[table], fieldListIn)
        fieldListOut = ["EvaluationID", "Transect", "ParticleNum", "StreambedLocation", "ParticleSize", "ParticleSizeClassNonMeas"]
        insertC = arcpy.da.InsertCursor(outDB + "\\" + table, fieldListOut)
        for row in searchC:
            insertC.insertRow((row[0], row[1], 7, row[2], row[3], row[4]))
        del insertC
        del searchC

        #Particle 8
        fieldListIn = ["DimCanPartEvaluationID", "Transect", "Location8", "ParticleSize8", "NonParticleSize8"]
        searchC =  arcpy.da.SearchCursor(surveyGDB + "\\" + tableList[table], fieldListIn)
        fieldListOut = ["EvaluationID", "Transect", "ParticleNum", "StreambedLocation", "ParticleSize", "ParticleSizeClassNonMeas"]
        insertC = arcpy.da.InsertCursor(outDB + "\\" + table, fieldListOut)
        for row in searchC:
            insertC.insertRow((row[0], row[1], 8, row[2], row[3], row[4]))
        del insertC
        del searchC

        #Particle 9
        fieldListIn = ["DimCanPartEvaluationID", "Transect", "Location9", "ParticleSize9", "NonParticleSize9"]
        searchC =  arcpy.da.SearchCursor(surveyGDB + "\\" + tableList[table], fieldListIn)
        fieldListOut = ["EvaluationID", "Transect", "ParticleNum", "StreambedLocation", "ParticleSize", "ParticleSizeClassNonMeas"]
        insertC = arcpy.da.InsertCursor(outDB + "\\" + table, fieldListOut)
        for row in searchC:
            insertC.insertRow((row[0], row[1], 9, row[2], row[3], row[4]))
        del insertC
        del searchC

        #Particle 10
        fieldListIn = ["DimCanPartEvaluationID", "Transect", "Location10", "ParticleSize10", "NonParticleSize10"]
        searchC =  arcpy.da.SearchCursor(surveyGDB + "\\" + tableList[table], fieldListIn)
        fieldListOut = ["EvaluationID", "Transect", "ParticleNum", "StreambedLocation", "ParticleSize", "ParticleSizeClassNonMeas"]
        insertC = arcpy.da.InsertCursor(outDB + "\\" + table, fieldListOut)
        for row in searchC:
            insertC.insertRow((row[0], row[1], 10, row[2], row[3], row[4]))
        del insertC
        del searchC

        #Particle 11
        fieldListIn = ["DimCanPartEvaluationID", "Transect", "Location11", "ParticleSize11", "NonParticleSize11"]
        searchC =  arcpy.da.SearchCursor(surveyGDB + "\\" + tableList[table], fieldListIn)
        fieldListOut = ["EvaluationID", "Transect", "ParticleNum", "StreambedLocation", "ParticleSize", "ParticleSizeClassNonMeas"]
        insertC = arcpy.da.InsertCursor(outDB + "\\" + table, fieldListOut)
        for row in searchC:
            insertC.insertRow((row[0], row[1], 11, row[2], row[3], row[4]))
        del insertC
        del searchC

        #Particle 12
        fieldListIn = ["DimCanPartEvaluationID", "Transect", "Location12", "ParticleSize12", "NonParticleSize12"]
        searchC =  arcpy.da.SearchCursor(surveyGDB + "\\" + tableList[table], fieldListIn)
        fieldListOut = ["EvaluationID", "Transect", "ParticleNum", "StreambedLocation", "ParticleSize", "ParticleSizeClassNonMeas"]
        insertC = arcpy.da.InsertCursor(outDB + "\\" + table, fieldListOut)
        for row in searchC:
            insertC.insertRow((row[0], row[1], 12, row[2], row[3], row[4]))
        del insertC
        del searchC

        #Particle 13
        fieldListIn = ["DimCanPartEvaluationID", "Transect", "Location13", "ParticleSize13", "NonParticleSize13"]
        searchC =  arcpy.da.SearchCursor(surveyGDB + "\\" + tableList[table], fieldListIn)
        fieldListOut = ["EvaluationID", "Transect", "ParticleNum", "StreambedLocation", "ParticleSize", "ParticleSizeClassNonMeas"]
        insertC = arcpy.da.InsertCursor(outDB + "\\" + table, fieldListOut)
        for row in searchC:
            insertC.insertRow((row[0], row[1], 13, row[2], row[3], row[4]))
        del insertC
        del searchC

        #Particle 14
        fieldListIn = ["DimCanPartEvaluationID", "Transect", "Location14", "ParticleSize14", "NonParticleSize14"]
        searchC =  arcpy.da.SearchCursor(surveyGDB + "\\" + tableList[table], fieldListIn)
        fieldListOut = ["EvaluationID", "Transect", "ParticleNum", "StreambedLocation", "ParticleSize", "ParticleSizeClassNonMeas"]
        insertC = arcpy.da.InsertCursor(outDB + "\\" + table, fieldListOut)
        for row in searchC:
            insertC.insertRow((row[0], row[1], 14, row[2], row[3], row[4]))
        del insertC
        del searchC

        #Particle 15
        fieldListIn = ["DimCanPartEvaluationID", "Transect", "Location15", "ParticleSize15", "NonParticleSize15"]
        searchC =  arcpy.da.SearchCursor(surveyGDB + "\\" + tableList[table], fieldListIn)
        fieldListOut = ["EvaluationID", "Transect", "ParticleNum", "StreambedLocation", "ParticleSize", "ParticleSizeClassNonMeas"]
        insertC = arcpy.da.InsertCursor(outDB + "\\" + table, fieldListOut)
        for row in searchC:
            insertC.insertRow((row[0], row[1], 15, row[2], row[3], row[4]))
        del insertC
        del searchC

Hope that all makes sense.

[Strahanjen]

Doug, many thanks for your guidance. I think I'll certainly have a need for each of the options you shared. Here's what I ended up doing for  this case:  I'm working with a .csv export from Survey123 that will be imported into another database.  I wrote the following script similar to yours that converts the survey123 output into a new .csv file. I thought I'd share here so others can see how I managed to keep the data for the stressor id and problem columns together.  I used the stressor ids as checkbox values for both the stressor presence and problem checkboxes. Survey123 then outputs a table like the image attached. For each row, I loop through the stressor ids present at a site and then check to see if that stressor id is in the list of problem ids. 

Thanks for helping me to be successful with this!

import csv

def getProblem(stressor, problemList😞

    if stressor in problemList:

        return 'Y'

    else:

        return 'N'

with open('scsi.csv', 'r') as f,open('stressors.csv', 'w') as f_out:

    reader = csv.reader(f)

    writer = csv.writer(f_out)

    writer.writerow(['VisitID', 'StressorID', 'Problem'])

    for row in reader:

        stressorList = row[1].split(",")

        problemList = row[2].split(",")

        for stressor in stressorList:

            writer.writerow([row[0], stressor, getProblem(stressor,problemList)])