Select to view content in your preferred language

How to SUM a field in repeat by conditional selected field?

2027
8
01-16-2020 12:25 PM
GeoprocessamentoCerradinho
New Contributor III

Need to total the volumes reported in the repeater by product in "insumo" field. 


In the same form I can have in the repeater several inclusions of the limestone product (named "insumo"), in addition to the inclusion of other products such as phosphate. I need to total each at the end, but I am unable to persist the sum of each product in its respective note. Similar logic to excel sumif.

8 Replies
KassandraRodriguez
Occasional Contributor

Have you tried putting the 'if selected' in the relevant column instead?

*Sorry change the 'if selected' to 'selected' in the relevant column

0 Kudos
GeoprocessamentoCerradinho
New Contributor III


Sorry, I didn't understand how relevant column could help me do the summation. I do not want to skip any of the fields, but bring the sum of each product ("insumo" field) on their respective note.

0 Kudos
KassandraRodriguez
Occasional Contributor
GeoprocessamentoCerradinho
New Contributor III

Thank you for your attention, in the article the sum is performed on all records regardless of category. Interestingly it seems that when storing the data the sum is separated by category. I don't know if there is a way to retrieve this sum in a note field.

0 Kudos
KimberlyMcCallum
New Contributor III

I am also trying trying to sum values across all repeat records, excluding those with a specific category. It doesn't look a solution was found in this post. Any updates or suggestions on this?

0 Kudos
HaroldHerrera1
Occasional Contributor

Hello, did you resolve this issue? I have the same question...

0 Kudos
IsmaelChivite
Esri Notable Contributor

If I understand the problem here, each record in the repeat as a quantity attribute and a category. The goal is to do a sum of the quantities for each of the categories, as shown in the animation below:

Sum.gif

 

I do not think the sum function is going to help. This is because the sum function sums values for ALL records in the repeat. We cannot tell the sum function to ignore certain records from the repeat.

An alternative approach is to use a custom JS function. I created the animation above with the attached XLSForm and JS Function. 

IsmaelChivite_0-1683569835531.png

 

function totalFruit (fruits, quantities, fruit)
{
 var fruits_array = fruits.split(',');
 var quantities_array = quantities.split(',');

 var totalFruit = 0;
 var i;
 for (i = 0; i < fruits_array.length; i++) {
  if (fruits_array[i]==fruit) {
     totalFruit = totalFruit + Number(quantities_array[i]);
  } }
 return totalFruit;
}

 

I tested this in both the mobile and web apps. Worked good! Hope it helps. 

JackieChan
New Contributor

Would you mind elaborating the JS function? I have the exact problem except I need to sum the input number when a specific word was pulled from an external excel listing. something like this:

if(${PrimaryGrowthform}="Tree(TG)", sum(${coverage%}), 0)

The category - PrimaryGrowthform could be Tree, shurb, grass etc depends on what species was selected. Then I need to sum all the coverage% of all the selected species under the category Tree(TG) or Shrub etc.

0 Kudos