Is there a way to aggregate data between repeats only when both repeats have a common value?

Question

I'm looking for a way to populate a repeat with data aggregated from another repeat when a field in each repeat contains a common value - i.e. only aggregate the values from Repeat 1 to Repeat 2 if a field in both has the same value.

When our field technicians find something below surface, we record by location (parent survey), soil strata (repeat), and individual descriptions (repeat). The survey currently has the techs filling out the individual descriptions - which is aggregated to the location - and the soil strata forms separately. What I'd like to do is have the same information aggregated into the soil strata repeat so that all artifacts identified in the individual descriptions as coming from strat 2 of a given test location are added to the strat 2 soils record. This would streamline our workflow and cut-out an opportunity for internal disagreement in our data.

Any ideas?

AndyHeller · Answer

Thanks Zach,

I've attached a version of the XLSF with the necessary fields. What I'd like to do is take the data from the fsLog repeat, aggregate it, and send it to the appropriate record in the the stpLog repeat. The Stratum field (integer) is the common field in the repeats. The data that I'd like to pass is in the fsLog_art_sample field (text). When there is a common value in the Stratum fields, I'd like to pass the text in fsLog_art_sample to a similar field in the stpLog (I've removed this field from the survey, but it would just be a basic text field). There could be multiple fsLog records associated with a single point, as well as multiple strata. For example: STP 1-1 has three strata (A, B, C). Strata B and C each have 2 FS log records. I would like to take the data from the FS Log that corresponds to Stratum B and join it to the STP Log entry for Stratum B and then do the same for Stratum C. Alternatively, if an text field is unnecessarily difficult, I would happy to aggregate the fsLog_fs field (integer) to their respective stpLog records.

Our current workflow works like this:

A technician navigates to a location and collects data. When there is a specific find to record, the technician fills out the FS Log repeat. The summary field (fsLog_art_sample [text]) is then joined back to a field in the main feature service - art_sample (text). Later, we go through and join the fsLog data to the stpLog based on the stpID and Stratum fields. Note this is only important when the stpLog is relevant (pointType = 'stp', 'tu' or status = 'auger', 'soil_prob', 'trowel_test', 'trench', or 'scrape').

Best,

Andy

Sample_20200803.xlsx

ZacharySutherby · Answer

Hello Andy Heller‌,

Without really knowing what field types are in the repeats you can do something like if(${repeatAquestion} = ${repeatBquestion}, sum(repeatAquestion) + sum(repeatBquestion), or concat() whichever function is suitable for your data type, <whatever you would like returned>).

For example here is a quick sample that adds integers from two repeats together. If the sum's are equal the function returns the total sum other wise it says that they are not equal. I have attached the XLSForm for testing.

typenamelabelcalculationgeopointlocationlocationbegin repeatrepeatARepeat AintegerquestionAQuestionAend repeatbegin repeatrepeatBRepeat BintegerquestionBQuestionBend repeattextcalrepeatsCal Repeatsif(${questionA}=${questionB},sum(${questionA})+sum(${questionB}),"these are not equal")

Please use this link for reference regarding the if() sum() and concat() functions: Formulas—Survey123 for ArcGIS | Documentation

If possible would I be able to obtain a copy of your XLSForm and what fields would contain common values and I can modify my sample to fit you survey a little better.

Thank you,

Zach

AndySampleSurvey.xlsx