Survey123 Tricks of the Trade: Repeats

7365
44
04-09-2020 04:42 PM
Esri Frequent Contributor
5 44 7,365

You can think of XLSForm repeats like sub-forms: A form within a form that can also be completed multiple times. Repeats are a very powerful feature in Survey123 because they can be configured in many different ways to support different business workflows. This article covers everything from the basics to the more advanced features of repeats. 

Some use cases

Let's first review some common scenarios that scream for a repeat:

  • In a door-to-door population survey, you will want to capture the location of the household and address, but also information about every member of the family. A repeat will help you model the form to be completed for each of the members of the household, including information about their name, age, gender, etc. The household survey will have a household member repeat in it, which will be completed for each person in the household.
  • In an asset inspection form you may want to capture information about each defect found. The defects repeat (or sub-form) will include questions to let you document each defect with its own photo, a description of what needs to be repaired and estimated cost of repair.
  • Repeats are also a great way to handle routine inspections. An asset form can include a repeat for the inspections: one form for the asset with its ID, location, etc and a sub-form (repeat) for every inspection ever made, with its date, issues found, etc. Since repeats can be nested, the inspection repeat form can also include its own repeat or sub-form for every defect found.

The basics

Repeats are supported through XLSForms via Survey123 Connect. If you are familiar with XLSForm groups, you have a lot of ground covered, because a repeat is really a type of group that lets you cycle through the questions in the group again and again.

To add a repeat to an XLSForm you need to enclose a set of questions within a begin repeat and end repeat set, as shown in this example:

typenamelabel
texthh_idHousehold ID
texthh_addressAddress
begin repeathh_membersHousehold Members
textmbr_nameName and last name
integermbr_ageAge
select_one gendermbr_genderGender
end repeat
select_one yes_noall_members_presentAll household members present?

In the survey above, the main body of the form is really about the household itself and is used to collect the household ID and address. The repeat block is all about household member information. We expect the enumerator to complete the Household Members repeat, or sub-form, once for each person in the household. The All household members present? question is outside the repeat, so it also belongs to the main form, but it will be presented right after the Household Members repeat.

Below is what the survey above would look like in the Connect preview. Note that the repeat appears as a common question group, except that at the bottom there are multiple options to navigate records within the repeat: a plus button to add a new record as well as next and back buttons (once you have more than one household member).

The user experience for repeats within the Survey123 web app is slightly different, presenting all the repeat records at once. You can navigate through them by scrolling up and down, as shown in the next animation.

Repeat records in the Survey123 web app are shown all at once

You can add one or more repeats to your survey. For example, in a school survey, you could include a repeat to document all classes in the school, and a separate repeat to gather information about teachers, etc. 

Understanding repeat data in your feature layer

Survey123 repeats are modeled in ArcGIS as related tables (or related layers if your repeat includes a geopoint, geoshape or geotrace question). In the example household survey above, the information about the household (id, address...) is kept in the main layer of a feature service, and the data from the household member repeat (name, age, gender) is modeled as a separate, but related table. The relationship between the tables/layers is kept through internally created global IDs.

If you would like to preview what your geodatabase schema looks like you can use the Schema Preview option in Connect, as shown in the animation below. Note that the name of the repeat in XLSForm becomes the name of the related table.

Survey123 Connect lets you preview the geodatabase schema of your survey

Using the Survey123 website, you can quickly explore data from repeats using the Data tab. The table will show records from the main layer of your survey as usual, and also extra tabs for every repeat in your form. You can propagate selections across the tabs for easier exploration of your data.

Use the Survey123 website to explore related records (repeats)

Repeat appearances (minimal and compact)

Through the appearance column in the XLSForm you can control how the repeat initially loads and appears in your form. By default, a repeat with no appearance value set is shown expanded, with one initial record showing. This can be good or bad. The good is that all questions within the repeat are ready for end-users to enter data right away. The bad is that if no data is entered, a new empty record will be added to your related table. I generally avoid using repeats with no appearance unless questions within the repeat are flagged as required.

If you want to avoid the repeat from having an initial record preloaded, use the minimal appearance. This will show your repeat group expanded, but with no records. To initialize data entry in the repeat, the end user will need to explicitly add a new record to the repeat by clicking the plus button.

The compact appearance simply collapses your repeat when the form initially loads. Of course, you can combine the minimal and compact appearances.

XLSForm expressions and repeats

XLSForm expressions allow you to bring sophisticated logic into your forms. Generally, XLSForm expressions are used to auto-calculate values (Calculations), define skip logic (Relevant), and to build data validation rules (Constraints). The XLSForm expression syntax for calculations in repeats is basically the same as with any other question in the form (more details at Formulas—Survey123 for ArcGIS | Documentation), but here are some important things to highlight.

When writing XLSForm expressions for a question within a repeat, you can reference other questions inside the repeat, but also questions outside! A common use is that where you want to bring data into your related table from the parent record. For example, in the example household survey you may want to store, along with every household member, their corresponding household ID. It would look something like this:

typenamelabelcalculation
texthh_idHousehold ID
texthh_addressAddress
begin repeathh_membersHousehold Members
hiddenparent_hh_idParent Household ID${hh_id}
textmbr_nameName and last name
integermbr_ageAge
select_one gendermbr_genderGender
end repeat
select_one yes_noall_members_presentAll household members present?

Note how the parent_hh_id question within the repeat is calculated to automatically fetch the value of the Household ID for that person. The Household ID is outside the repeat, but the calculation brings that value into the repeat record. This is a common use of calculations within repeats because it helps bringing more information into the repeat table that would otherwise require a join.

Styling

There are a few techniques you can use to style your repeat with custom colors and labels. This can be help users navigate your form more efficiently.

One technique is to dynamically set the label of your repeat to provide some context as to what information should be entered into the repeat. For example, in our household survey, you can include the address of the household in the header of the repeat. Instead of just reading 'Household Members', you can make it read 'Household Members at: <Address of the household comes here>. This is what is known as a dynamic label and it is described in more detail in the https://community.esri.com/groups/survey123/blog/2018/11/01/understanding-dynamic-labels-in-survey12... blog post.  This is quite handy when working with long surveys, because it brings additional context for the person filling out the form. Here is what this would look like. Note how the label for the repeat is dynamically generated using a value from a question outside the repeat.

typenamelabel
...
texthh_addressAddress
begin repeathh_membersHousehold Members at ${hh_address}
...

You can also use colors to make your repeats stand out better within the form, like in the following screenshot. The repeat block uses a darker background color than the rest of the form, clearly separating questions within the repeat, from those outside.

The background and border colors of your repeat can be controlled through the body::esri:style column. In the example above, the vale in this column for the begin_repeat question is: backgroundColor='#cbcddb' borderColor='#494a4f'.  The color is expressed in hexadecimal format or HTML color name.  You will generally not want to use bright colors for you repeat backgrounds. Try to be subtle using slight variations of the background color of your form.

The body::esri:style color parameters are only honored by the Survey field app.

Aggregation functions (max,min, count and sum)

XLSForms support a handful of functions specially built to work with repeats. Specifically: count, sum, min, and max. They are all pretty self-explanatory. The design below shows the max and count functions in action.

typenamelabelcalculationbind::esri:fieldType
texthh_idHousehold ID
texthh_addressAddress
begin repeathh_membersHousehold Members
textmbr_nameName and last name
integermbr_ageAge
end repeat
hiddenmax_ageAge of oldest personmax(${mbr_age})esriFieldTypeInteger
hiddentotal_countTotal persons in housecount(${mbr_name})esriFieldTypeInteger

The count function can get a bit tricky as it will only consider in the count records with a value in the question it references. For this reason, it is best to use the count function against a question within the repeat that is flagged as required.

The sum question is also an interesting one in that it can sum not only numbers, but also text and even geopoint questions as well. If you sum text, it will concatenate all responses to your question within the repeat. If you sum on a geopoint question within the repeat, it will generate a geotrace object.

Check the Repeat Aggregation Survey123 Connect survey sample for an example using aggregation functions.

Aggregation functions (join)

There is one more aggregate function that may be of interest: join. The join function is used to create a single list including all responses to a particular question within the repeat. Its syntax looks as follows:

join (",",${questioname})

The first parameter defines a separator. You can use a comma ",", a hyphen "-", or other character. The second parameter is the question within your repeat from which you want to create the list.

In our household survey example, we could use join to display a list of all people interviewed so far.

typenamelabelcalculation
texthh_idHousehold ID
texthh_addressAddress
begin repeathh_membersHousehold Members: ${interviews}
textmbr_nameName and last name
integermbr_ageAge
end repeat
hiddeninterviewsEvery person interviewedjoin(",",${mbr_name})

Note that the hidden question at the end uses the join function in the calculation column. Then the label in the begin repeat takes that value and displays it dynamically. You can see the effect in the next animation:

We can take join a bit further. Let's pretend we need to design a manhole inspection form. We want all six parts of the manhole to be inspected: the cover, chimney, cone, wall, bench and channel. We will use a repeat to document the inspection results of every component, and the join function to help us make sure all components have been inspected. Let's first have a look at what the form's behavior looks like:

Note that a checklist at the bottom of the form keeps track of the components inspected so far. When a new component is selected for inspection within the repeat, the value of the checklist is recalculated accordingly. This checklist at the bottom is set as read-only, so the user cannot manipulate it. A constraint in the checklist is also in place to avoid users from submitting the form unless all components are checked.

A simplified version of the XLSForm, highlighting the use of the join function is shown below:

typenamelabelcalculation
textmanhole_idManhole ID
begin repeatcomponents_groupComponents
select_one componentscomponentComponent
select_one conditionsconditionCondition
end repeat
select_multiple components
comps_inspectedComponents Inspected
join(",",${component})

To complete the XLSForm to mimic the behavior shown in the animation, you would also need to:

  • Set the comps_inspected question as read-only, to avoid users from manipulating the checklist manually.
  • Set the constraint of the comps_inspected question to count-selected(${components_tested})=6.

Repeat Count

If you want to control how many records must exist within a repeat, use the repeat_count XLSForm column. If you set the value to 2, then your repeat will be initialized with 2 empty records. If you set it to 5, it will contain 5, etc. You can also set the repeat_count dynamically as shown in the example below.

typenamelabelrequiredrepeat_count
texthh_idHousehold ID
texthh_addressAddress
integerhh_totalHow many people live in this house?
begin repeathh_membersHousehold Members${hh_total}
textmbr_nameName and last nameyes
integermbr_ageAgeyes
end repeat

The repeat_count column simply defines how many records are shown in the repeat. All records will be empty until the end user goes through every record to add data. Unless you have required questions within the repeat, Survey123 will allow you to submit records from the repeat with no data, and they will show as what they are: empty records. Generally speaking, you do not want to use repeat_count, unless you want to force people to submit a specific number of records in the repeat. If that is the case, you will want to add required fields within the repeat group. In the household example above, we first ask, "How many people live in this house?" We use that number to define the exact number of records in the household roster that must be completed. By making the questions within the repeat required, we guarantee that data is entered for every person in the house.

In cases where you want users to submit a number of records within a range, you will not want to use repeat_count. Instead, you will want to use constraints and the count function. For example, in an electric panel survey, you include a repeat to document all circuit breakers. Say that the number of circuit breakers is always between 5 and 20. This is what you would want to do:

typenamelabelconstraintcalculation
textcc_idCircuit Panel ID
begin repeatbreakersCircuit Breakers
select_one typebreaker_typeType
select_one statusbreaker_statusStatus
end repeat
integertotalTotal Breakers${total}> 4 and ${total}<21count(${breaker_type})

As described above, for the count function to work effectively, you will want to make the breaker_type question required.

Nested repeats

A nested repeat is a repeat within a repeat. For example, say you are doing a survey of a campground in a National Park. You want a survey for the campground itself (the name, the location of the entrance, etc). Within each campground you have campsites, so that gives you one repeat (location of the campsite, photo, size, etc). Then for each campsite you have equipment, which gives the second repeat (equipment type, condition, etc).

You can nest as many repeats as you want. That is, you can nest 3, 4 even 5 repeats if you like. Technically you could nest even more but for the sanity of your users avoid it whenever possible. 

Nested repeats are only supported in the Survey123 field app.

The Survey123 web app does not support nested repeats.

Repeats in the Inbox

The Inbox is a powerful feature in the Survey123 field app, allowing you to edit existing GIS records in a feature layer. If you are not familiar with the Inbox, I strongly suggest you watch this short video tutorial.

By default, when GIS features are loaded into the Inbox, the repeat records will be empty. That is, the Survey123 field app does not fetch data from related records unless told otherwise. When updating an existing record from the Inbox in this way, you will be able to add new repeat records, but not view or update existing ones from the feature layer.

If you want to look at or update existing related records, you need to specify in your XLSForm what you want to download into the app, and for what purpose. This is all done through the bind::esri:parameters XLSForm column.

For example, let's pretend you are creating an asset inspection form and you want the complete history of inspections to be available in read-only mode. Then you would go with something like this:

typenamelabelbind::esri:parameterscalculation
textpanel_idElectric Panel ID
geopointlocationLocation
begin repeatcircuitsCircuit Breakersquery
datetimeinsp_dateInspection date
select_one statusstatusStatus
end repeat
integertotalTotal Breakers${total}> 4 and ${total}<21count(${breaker_type}

The query value in bind::esri:parameters will force the Survey123 app to download every related record, so all your inspections will show in the repeat of the asset form. The values supported in this column include:

  • query: Used to fetch related records and populate repeats. If query is not used, surveys from the Inbox will not display any repeat information from the existing feature. You can also control which records should be fetched, by defining a where statement for your query. For example: query="status='needs_repair'" will populate the repeat with all related records where the status is set to needs_repair. If no where statement is passed, the query parameter will fetch all related records.
  • allowUpdates: This parameter controls if the existing data in the repeat can be updated by the end user of the Survey123 field app or not. This is extremely useful because often you want end users to have read-only access to historical data (for example previous inspections of an asset) in the repeat, while allowing entering a new inspection to the repeat. allowUpdates by default is always false, but you can turn it on: allowUpdates=true.
  • allowAdds: By default this parameter is always true, allowing the end user to enter new records in the repeat. You can turn it off by passing allowAdds=false.

In the example below, our electric panel survey will display all existing circuit breakers ever recorded for that asset. It will also allow the end user to modify information from those previously recorded breakers because allowUpdates is set to true. The only exception will be the installation_date, which will be shown in the repeat as read-only.

typenamelabelbind::esri:parameterscalculationreadonly
textpanel_idElectric Panel ID
geopointlocationLocation
begin repeatbreakers Breakersquery allowUpdates=true
datetimeins_dateInstallation dateyes
datetimeinsp_dateInspection date
select_one statusstatusStatus
end repeat
44 Comments
New Contributor II

This is some excellent and helpful information on repeats - thank you!

We've got a survey123 right now to inventory items being added to a pallet - it has a repeat inside of it which holds item information and outside of the repeat is information on the pallet.  To facilitate the FME process on the layers, I've added hidden fields to the repeat schema calculated from fields in the parent record like the pallet barcode and final pallet destination.  This is similar to what you've shown with the household ID when you brought it into the repeat as a hidden reference.  We're having a problem though - not all of the fields I'm adding in come before the repeat.  The fields in the parent record which are answered after the repeat is completed only appear on the last item entered as a repeat.  If we enter five items, the fifth one is the only one which will have the references to the parent attributes entered after the repeats were entered.

Ideally, I could reorder the survey so those questions are before the repeat but in how a pallet is assembled, the questions would still be blank when the items are added and I think that is a part of the problem - if all attributes in the parent record are not complete before people start adding to the repeat, those hidden references won't be populated. 

How can I get around this?  We've built our process around having the parent fields referenced in the repeat and I can't figure out how to get them to all populate.  This work is being done in support of the COVID pandemic going on right now and time is absolutely of the essence in figuring this out.  Please let me know what we could try instead - we're open to suggestions.  Thank you, 

Emily

Esri Frequent Contributor

Hi KYTC GIS‌  Glad the content helps!  Calculations in a repeat record are evaluated when the user is entering the information in that record. Do the following test: Enter the pallet ID and then complete information in a repeat record. Next click on + in the repeat to add a second record. This time, go back in your form and change the pallet ID to some other number, and then complete the record you have open and submit. You will observe that the first record in the repeat has the pallet ID that you entered originally, but the second record in the repeat has the last value of the pallet ID you entered.

Similarly, with your own survey, the only record that has the information from questions below the repeat is the last, only because the last record is active when you actually enter that information.

While the above confirms what your were suspecting, it does not quite help solve the problem. If you cannot enter values in the questions below the repeat until all repeat records have been entered, I am not sure how this could be resolved. At first sight, the most logical place to bring the post-repeat values into the repeat records would be your FME process.

New Contributor III

Just going to share something that has taken me an hour to figure out so others don't suffer.

I have been trying to replicate the "Aggregation functions (join)" example above with the auto-populating of a readonly select-multiple to show progress. In S123 Connect I could not get the blasted thing to work until I realised that I had a (very slightly) dated version of S123 Connect.  Update to most recent version and all is good.

New Contributor II

Hi Ismael. This is Gabriel from Geovoluntarios España.

I am interested in the Agrgregation function (join), which allows you to see the already inspected components in a Repeat sub-form. In my case, before the repeat I have a question Pick an Area that conditionates (choice_filter) the following Repeat Pick an Element (from that Area) with external_choices.

So, when I try to use the Join function as you explained, the problem comes if I want to keep the filter condition, so the full list of Elements doesn't appear (only those selected by the Area). If I change the question to select_one, the filter works, but I understand it must be a select_multiple question, otherwise it won't check all the elements while populating the form.

Any solution for this case?
Thank you.



Occasional Contributor II

Hi,

This is really great info and I've followed your example (join(",",${mbr_name}) in a repeat within a survey. What I'm wondering is there a way to display the label of the field instead of the field itself for in this case {mbr_name}. I have coded values and would prefer the labels over codes.

Thanks!,

Kathy

New Contributor III

Thank you so much for this post!  It has been very helpful. 

We have run into a snag with a set of nested repeats.  The desired outcome is to have all existing repeats and nested repeats to be read only while still allowing for new records.  For both the repeat and the nested repeat, the bind::esri:parameters were set to query allowAdds=true

Example:

Permit X Inspection

  (Repeat)

  Pond 1 Inspection

      (Nested Repeat)

      First inspection of pond 1

      Second inspection of pond 1

   Pond 2 Inspection

      First inspection of pond 2

      Second inspection of pond 2

This was mostly working until I realized that the nested repeats were over-writing themselves instead of allowing new records.  So the First inspection of pond 1 was being over-written when the Second inspection for pond 1 was submitted. 

After a bit of testing, realized that if I added allowUpdates to the Pond 1 Inspection (top level repeat) then the nested repeats will allow for additional records and not over-write each other when a new inspection of that pond was submitted.  However, this of course allowed the top level repeats to be editable for past inspections which we really don't want.  I can not put my figure on what I'm doing wrong.  But I'm assuming there indeed is a way to be able to add additional records to the repeats and nested repeats while still allowing for existing records in both the repeat and nested repeat to stay read only.

Again thanks for the article!

Esri Frequent Contributor

Hi Katy. Sorry for the belated response. Have you tried jr:choice-name(choice, 'question') ? More details at:

https://community.esri.com/groups/survey123/blog/2018/12/02/survey123-tricks-of-the-trade-xlsform-fu... 

Esri Frequent Contributor

Hola Gabriel. Unfortunately, it is not possible to filter choices in a select multiple. This is a known limitation at the moment.

Occasional Contributor III

Thanks for an informative blog post. I have run in to some issues with styling (using body::esri:style). My form uses theme-grid pages in the Settings. On one of the pages, the structure is as follows:

  • begin group 1 (page)
    • begin repeat 1
      • begin group 2
      • end group 2
      • begin repeat 2
        • begin group 3
        • end group 3
      • end repeat 2
    • end repeat 1
    • begin group 4
    • end group 4
  • end group 1

I am trying to use backgroundColor arguments in body:esri:style to give different groups or repeats different background colours. I can set background colours for groups (tested on group 2 and group 4), but the argument does not work for repeat 2. Is this due to the nestedness, or the theme-grid pages settings? I am also finding that the borderColor argument is not being honoured.

Many thanks,
Rob

Occasional Contributor II

Hi Ismael 

I thought this should work but i am getting a dependency error in my calculation. I basically need to have the repeat populate with the selections from a previous select multiple question. Is this possible in anyway? So if 3 hazards are selected the Risk assessment for each is to be  carried out in a repeat and each repeat shows the value of the consecutive hazard selected in the select multiple question  ${Hazards_present}. 

Occasional Contributor II

I figured it out i was using  the combination of the functions selected-at and count but in the wrong way. I had it inside my overall repeat which was causing the issue i believe,  i moved it outside and set up a default answer in side my repeat to use as the count  field and then i just use a minus one to account for the indexing of the select multiple question. Works a treat and the repeats are also constrained to the number of selections in the select multiple question so as i add a repeat the answer is populated as required. 

 

 

selected-at(${Hazards_present},count(${default_1})-1)

Frequent Contributor II

You may also need to use once() or when you scroll backwards in the repeat things can happen.

See this post for more

https://community.esri.com/thread/193049-survey123-functionality-calculate-position-in-repeat-group?...

Hope that helps.

Occasional Contributor II

Thanks Doug i did nt check the effect of that but I will defo use this as well .  I did worry what would happen if the user changed the selection in the original select multiple question but it seems to account for that by giving a delete repeat option 

Esri Frequent Contributor

HI Melanie,

Are you able to share your xlsx form so I can take a closer look at the configuration and question types. Let me know if you can not share it publicly on here, then I can send you my email address so you can send it to me if you prefer.

Regards,

Phil.

New Contributor III

If you wouldn’t mind sending me your email, I can definitely send you the xlsx form. Thank you so much for taking a look at it! I’m not sure what I’m trying to do is allowed but it could just be that I’m doing something wrong.

Thanks so much!

New Contributor III

I need to be able to publish a survey (from a non-federated feature service) that uses a feature class with a related table (streets (parent)-->Inspections (child)).  The related table fields are successfully publishing as a repeat within the survey.  I need to be able to create a separate repeat (or multiple repeats) that would be 'null' values to use the outcome in the relevant fields in the table.  Is there a way to do this?  I keep getting the error "The custom feature service submission url is not compatible with this survey (Table AlligatorFakeRepeat not found)."  

I should add that I assume this would require the 'nested repeat' functionality, but I cannot figure out how to overcome the issue of having a repeat for a field that isn't actually in the service, but just acts as a function for adding up decimal values.

Is this possible?

*I dont seem to be able to provide the excel file here, but I would be happy to provide one.

Esri Frequent Contributor

Hi Leah,

I had a quick look at your survey you sent via email, what you are trying to achieve would require the nested repeat related table to be created in the feature service. You can not put a single field in a repeat or nested repeat and make it a null field type, if there is no other field in the related table. When the form is published or re-published, it will require this table to exist.

 

Are you able to add this related table to your feature service? If so, it will solve the problem, even if you make the calculation field null, as there are other system fields already created in the layer, it would work ok.

Regards,

Phil.

Occasional Contributor III

Is there a way to make the join return the label of a value rather than its actual name?  Mine is starting to look messy as all then names have _ in them whereas the labels have spaces.

Esri Frequent Contributor

Hi Naomi,

Are you referring the table names? If so, then no, in the name column only the actual name of a table/layer can be used, not its label.

Regards,

Phil.

Occasional Contributor III

Hi Phil

I've just copied the below bit, however instead of referencing a free text field like mbr_name, it references a select_one field.  I can't wait for the day that Survey123 becomes like AGOL and is able to have domains that have spaces in them.

Occasional Contributor III

Also, how can I reference a date in a human format for this?

So my repeat label is:   ${date input from above Q} Text join{other field}.

Its currently looking pretty ugly .... 

 

Esri Frequent Contributor

Hi Naomi,

You need to apply date formatting in the expression on the question you are referring to. See this blog for more information: https://community.esri.com/groups/survey123/blog/2016/04/17/dates-and-time-in-survey123-for-arcgis 

Phil.

Occasional Contributor III

I've figured out the date    format-date(${datefield},’%e/%n/%Y’).

I thought I had seen that this wasn't meant to be functional in Survey123 however it does work now.

Occasional Contributor III

Thanks to Jim Moore

I now have a pretty repeat label, with a human readable date and labels for my selected areas.

Getting the select_one labels rather than coded values was done using the jr:choice-name mentioned in the below blog.  The one mistake I made while trying to use this was to place the choice-name outside of my repeat rather than within it.

https://community.esri.com/groups/survey123/blog/2018/12/02/survey123-tricks-of-the-trade-xlsform-fu... 

And the date using the formtat-date function.   

format-date(${datefield},’%e/%n/%Y’).

Occasional Contributor III

Hi Survey123 team,

I am attempting lots of repetitive calculations and running into some issues I hope you can help with. 

I am designing a "Daily Log" survey where the repeat is for creating multiple "Log Entries". Each entry can record up to three violation codes; our violation code list consists of 31 choices. For each violation code, the # violations, # written warnings and # citations can be recorded. Violation codes can be collected in any order, in each entry. Sometimes there are no violations in an entry. Sometimes there will be 1, sometimes 2, sometimes 3. They can be recorded in any order. See example in photo 1 at bottom.

I am attempting a series of calculate questions outside of the repeat so that we can determine, per violation code, the # violations, # written warnings and # citations. I am running into issues because if (using photo 1 below as an example) my "select first CMR violation type" is 2a1 in my first repeat "entry", but my "select first CMR violation type" is 2a2 in my second repeat "entry" it is adding them together anyways - giving me an incorrect total. 

These calculations work correctly if my "select first CMR violation type" is 2a1 in my first repeat "entry"; if in my second repeat "entry" I leave "select first CMR violation type" blank and then select 2a1 as my "select second CMR violation type" - in this situation, they are tallied properly. 

 

These are the calculations I am using:

Each calculation also has a relevant statement for the correct violation code.

This is what my repeat looks like:

I am happy to share my XLS if anyone is able/willing to help!

Thank you in advance!!

Photo 1:

Occasional Contributor II

Hi Erica Tefft

I hope I've understood you correctly but I  would have thought you would use if statements on different fields within the repeat first then use sum outside the repeat. You mentioned 31 values which can be selected at any time in any field so you would need 31x3 checks with in the repeat then a total sum out side. 

Eg Inside repeat for each code

Field 1 = if cmr_violations 1 = 2a1 , violations1,0

Field 2= if cmr_violations 1 = 2a1 , violations1,0

Field 3 = if cmr_violations 1 = 2a1 , violations1,0

Outside repeat

Sum (field1)+Sum (field2)+Sum (field3)

Would this work for you ? 

 

Occasional Contributor

Thanks for this article!

I've heard rumors that it's possible to set up a survey so that the most recently filled-out repeat can automatically populate/update a field in the main survey/table - is this true?

We have a couple surveys where staff fill out the main section of the survey on their first visit to a site, and then they fill out a follow-up section, in a repeat, on subsequent site visits.  It would be amazing if they could update the site status in the follow-up/repeat and have that automatically change the answer to the "site status" question in the main part of the survey, so that the map symbology (which is based on site status) could automatically reflect the current site status.  An example of site status would be to change it from "good condition" to "poor condition."  It's a "select one" question.  Any thoughts on whether this would be possible? 

Thanks so much!

Allen

Occasional Contributor III

Hi Paul Sweeney  - Somewhere between testing this and publishing this things are not working. I am feeling extra dense about it at this point. I have got to be missing something simple - I just can't figure it out.

 

I created 31x3 questions within my repeat (one per violation code per type of thing [citation, written warning, violation]) - totaling 293 fields within the repeat to deal with this. Outside of the repeat, I have 31x3 calculations with the sum (field1) + sum (field2) + sum (field3). 

 

These still do not work properly when a violation code (e.g. 2a1) is not filled in for each of the three options. I feel like it is because I don't have an "if" statement calculation for each field within the repeat. I am having a hard time creating a statement that works...I basically need every single field to have a value of 0 (at least) for the sum calculation outside of the repeat to work. 

 

I've added in these statements, but the calculation still doesn't work properly...

 

if(selected(${cmr_violations1}, '2a12'), 1, 0)

if(selected(${cmr_violations2}, '2a12'), 1, 0)

if(selected(${cmr_violations3}, '2a12'), 1, 0)

 

Calculation for final sum:

sum(${ParkTicket2a12First}) + sum(${ParkTicket2a12Second}) + sum(${ParkTicket2a12Third})

 

I've also tried this calculation for the final sum, but that also doesn't work:

if(sum(${ParkTicket2a12First}), ${ParkTicket2a12First}, 0) + if(sum(${ParkTicket2a12Second}), ${ParkTicket2a12Second}, 0) + if(sum(${ParkTicket2a12Third}), ${ParkTicket2a12Third}, 0)

 

My XLS is here if you would like to take a look at it.  

 

Thank you,

Erica 

Frequent Contributor II

I think it is because of the relevant on your calc field.  As soon as it hides it the value goes to "" blank so then there is no calc to 1,0.  I thought sum took out the blanks for you but maybe something is off (cant find the post on this). 

I would turn the lines like this if(selected(${cmr_violations1}, '2a12'), 1, 0) into calculates and remove the relevant.  Then it should always fire.  Not sure why you show it anyway since it is just a 1 or 0.  In the end you want it to always calculate so need to get rid of that relevant.

Hope that does it.

Frequent Contributor II

Strings are harder then say numbers but here is one idea.  Use join(status) to get a list of all status.  Then use substring(len(string)-some chars, len(string) to pull off the last few chars).  When I did this my items were always 4 chars so easier.  But you should be able to make an if statement off of the last few chars.

In your example it would work. Simple example

if(last 14 chars contains good, good, poor)

In other words if you use join then you know the last value is at the end.  Then you can peal it off and do something.

That said I personally think doing revists in a repeat is a not a great practice.  Allows users to edit main level data, loses the spatial for each visit (I guess now you can have GPS in a repeat), no history of points, lose of each editor tracking, etc.  I like to have 1 form per visit all related to the main point.  Just an idea.

Hope that helps.

Occasional Contributor III

Hi Doug,

It looks like removing the relevant statements work, however this isn't practical for me as I have 282 fields - imagine the nightmare. I want my users to only see the fields relevant to the violation code they have selected, hence the relevant statements. 

I really just need a way for the relevant statements to stay in place but to ensure that all 282 fields are 0 even if they haven't been "activated" via the relevant statements so that my sum statements work. Then selected violations would get some # greater than 0. I was hoping a calculation could help with that, but I haven't figured out the proper calculation either inside the repeat or outside of it. 

Ismael Chivite‌ - do you have any thoughts on this problem?

Frequent Contributor II

As I posted above just turn them to calculate type.  They will not show to the user then but still fire the calc once you remove the relevant.

Occasional Contributor III

This doesn't work. Without the relevant, even with a calculation in there fields show regardless of what is selected for the violation type. Like I said, with 282 fields, this just doesn't work. 

Here is one portion of the survey's XLS:

Here is what the survey looks like when previewing in Connect:

I think that might illustrate why relevant statements are a must. 

If you are referring to a calculate in the "type" column of the XLS...this also will not work because the user needs to be able to select a violation code - e.g. 2a1 - and enter a number. My calculation was set so that at the worst the field gets a value of 0 if the violation code isn't selected, and if violation code 2a1 is selected, the field get's a 1 that the user can then increase to represent the actual number of violations (sometimes there will be multiple -- one violation code could have 3 people = 3 violations). 

I am using "type" = calculate outside of the repeat to sum up all values for one violation type in the repeat. 

Frequent Contributor II

Ok I see what you are trying to do.  Have you seen issues when they change the value that a recal button shows?  Not sure on spinner but other fields do.  I have seen times where the if recalcs and it sets the value back to 1 then.  A default value would work..if that was not a bug. 

I think you would need to either show it always and default to 0 or have a second hidden field that did if(2a is blank, 0, 2a) and then use that to sum.  In the end the + will not work if a string is in there.  Sum() will take out blanks from each in a repeat but you are blanking the entire field.

You could also try using coalesce(sum(), 0) function at line 443 but I am not sure if that logic works depending on exactly how you have the repeat.   I would prob try this one first and see if it goes.

Sorry its a big form to get at a glance.

Occasional Contributor III

Yeah - this survey is a bear. 

I was able to get this to work using the coalesce function. I've never used it before (or even seen it used before), but it was able to fix this issue. Inside of the repeat I added 31x3 'calculate' field types with the bind::esri:fieldType set to null, and bind::type set to int; then I used the coalesce function in the 'calculation' column. Outside of the repeat, I changed my sum calculation to sum(${coalesce}). Now that I see how it works, it seems like a very powerful calculation to deploy. 

Thank you for all of your help with this! 

Frequent Contributor II

You can also now use the new visible (hidden) column style that just came out in 3.11.  I did not mention it before since it was in beta and it was still firing required and constraint.  They fixed that now.

See https://community.esri.com/groups/survey123/blog/2020/10/19/whats-new-in-survey123-october-2020 

Hope that helps.

New Contributor

This is a great article, thank you!

I'm working with a survey that's created to collect requests for tasks at certain locations.  This information is in a map and is displayed in an Operations Dashboard that shows counts of how many taskings and the statuses. I've used the ObjectID as a way of creating sequential tasking numbers and have used Join Features to create another feature layer that connected all the information based upon the parent unique row id and the child's parent row id (I now see that there may have been an easier way of coding this in to the XLSform itself!). 

There are two things I'm struggling with:

  1. there are two positions that were entered incorrectly - I would like to edit these positions but when I go to the web form I get the error that says "Editing existing records is not supported for surveys that contain repeats"
  2. I would like to be able to embed the form to the Operations Dashboard and have the team who is completing the task be able to change the status from Open to Closed. - Issue here is that we are not able to use the phone app as we must be connected to the network

I have set the bind::esri:parameters to query allowUpdates=true and just allowUpdates but neither seem to work.  I am able to change the status by selecting the dropdown within the Map Viewer but the team will not have access to this.

Any ideas as to how I would be able to do this? Or any other work around to get this to work?

Thanks!

Meredith

Occasional Contributor III

@IsmaelChivite is there any way to achieve the following?

  1. Outside of a repeat, a user fills in values relating to quantities for different species, e.g.:
    1. ${Species 1Quantity}: quantity
    2. ${Species 2Quantity}: quantity
    3. ${Species 3Quantity}: quantity ...
  2. For each species filled in, a repeat is generated (controlling the repeat_count parameter). (If it helps things to work, I could set it up where every species option has a repeat option but those where a quantity is not assigned is left as 0).
  3. Each repeat is indexed
  4. Each repeat uses pulldata() to pull in appropriate data based on the index (e.g. each index number is matched to a Species name, setting each repeat with a different Species name)
  5. Each repeat is auto-filled with data from the values entered at step 1, e.g.:
    1. Repeat 1 (Species = Species 1Quantity = quantity for Species 1

This approach would make data entry much more straightforward for our users whilst breaking the data up in to the repeats as we need them for the back end. 

From reading this and other excellent posts (including those with input from @DougBrowning who may have some thoughts here) I think at least part of this may be possible using the once(count) formula to assign each repeat a unique ID, and position() but I don't believe the latter is yet implemented?

*EDIT: I've now managed to set up repeats with unique ID numbers each, and to use a pulldata function to bring in the majority of the data I need from an external csv. However the point I am stuck on is step 5 above. I've been trying to use the pulldata function to pull in a formula from the external csv in to the repeat, which would then call the appropriate field to get the value (e.g. ${Species1Quantity}). However, for obvious reasons this just calls the formula in as text. Is there any way that pulldata could be used to bring in a formula and then use that in a subsequent calculation? Or a way of building a calculation formula within a repeat from text components?

Other threads covering similar topics I have been referring to:
Survey123 functionality: calculate position in rep... - GeoNet, The Esri Community 

Number each repeat - GeoNet, The Esri Community

Auto populate answers in repeats from previous rep... - GeoNet, The Esri Community 

 

Many thanks,
Rob

Occasional Contributor III

@DougBrowning  - 

I am curious if you have any thoughts as to why the coalesce function does not work properly when a survey is edited from the Inbox. Our workflow is that someone starts a survey, sends it and edits it from the Inbox many times in a day. I've recently discovered that the coalesce function does not work properly when this happens. If I start a survey and then submit it and make no edits from the Inbox, it works perfectly. 

This is a huge issue for us. If you have any thoughts on how to get around it, that would be great. Initially I thought it was due to the fact that my users were all in different versions of the Survey123 field app, but updating to 3.10 / 3.11 has not helped.

My current thought is that I could use the Outbox instead of the Inbox, however I like the added security (data loss-wise) of using the Inbox. I haven't tested the Outbox yet, but hope to do this when I make my next batch of modifications to the survey. 

Thank you!

Erica

Frequent Contributor II

Sorry I have never used Inbox.  I do have issues where calcs do not work when opening a draft however.  Has been a big issues for us.

Occasional Contributor III

 

@DougBrowning  - 

I figured out a solution to this issue. In an attempt to cut down on attribute fields in my survey, the coalesce functions inside my repeat were 'null' field types. I changed this to integer field types to persist the data. Now my "exterior" (outside the repeat) coalesce functions work properly. The downside, is that I now have 94 additional attribute fields in my table. 

 

Frequent Contributor II

You need to put the null in the bind esri FieldType column not the bind type column.  You can also mix and match so set bind type to int and esri Field Type to null.  That should get what you want.

 

Hope that helps

Occasional Contributor III

Yes, that is what I did, but it did not work properly when utilizing the Inbox in the workflow. 

You can also mix and match so set bind type to int and esri Field Type to null.

Once I switched from esri Field Type = null to Integer, this workflow & the coalesce functions worked properly when a survey was edited from the Inbox. 

Note: The combination you mention (bind type to int and esri Field Type to null) did work properly when completing the survey entirely after clicking "Collect". 

Frequent Contributor II

Well yes if you set the esri bind to null the data is not stored in the service.  So when you open it via inbox the field values are not there - since Inbox just pulls from the service.  Null types only live in the survey.  You had not mentioned the inbox part before.  I am not sure where you expected the data to be.