Hello,
I have a series of labels that are fed from a LookUp function. I want to have a count at the end of the form which shows users the #/Total labels that are filled.
If label has no text, it receives a "0". If it has text, it receives a "1". I want to sum the "1" 's at the end of the row like so:
NOTE: The count below should read "2/13" because 2 labels have text.
Here is the function I have so far, but its not giving me a sum of all the "1"'s.
Sum( If( IsBlank( label_complianceComp1 || label_complianceComp2 || label_complianceComp3 || label_complianceComp4 || label_complianceComp5 || label_complianceComp6 || label_complianceComp7 || label_complianceComp8 || label_complianceComp9 || label_complianceComp10 || label_complianceComp11 || label_complianceComp12 || label_complianceComp13 ), 0,1) ) & "/13"
Can someone give me a pointer on what this function is missing?
Thank you
Solved! Go to Solution.
Hello ericonline,
Interesting problem!
The Sum() function takes 1 or more arguments and sums them up. What's happening here is the || (Or) function is returning false, because none of the controls are true (they can't be, they're controls. This is different from programming in JavaScript, for example, which evaluates whether operands are truthy or falsey. PowerApps just evaluates whether it's actually true here). So, we get the following:
Sum( If( IsBlank(false), 0, 1) )
Sum( If( false, 0, 1) )
Sum( 1 )
1
So that's why you're experiencing what you are. But how do we fix it? I feel like there should be a nice elegant solution, but I haven't been able to come up with it, yet. I do know a solution, which I'll share, but if someone figures out a better one, please feel free to add it to the discussion!
Anway, I think the following will work for you:
Sum( ForAll( [ Value(label_complianceComp1.Text), Value(label_complianceComp2.Text), Value(label_complianceComp3.Text), Value(label_complianceComp4.Text), Value(label_complianceComp5.Text), Value(label_complianceComp6.Text), Value(label_complianceComp7.Text), Value(label_complianceComp8.Text), Value(label_complianceComp9.Text), Value(label_complianceComp10.Text), Value(label_complianceComp11.Text), Value(label_complianceComp12.Text), Value(label_complianceComp13.Text) ], If( !IsBlank(Value), 1, 0 ) ), Value ) & "/13"
What this does is it gets the Text property of each label and finds the value of that using the Value() function. It makes a table of these values (basically a list). Then, it uses the ForAll() function to loop over these values and return a table of 1's and 0's (1 if not blank, 0 if blank). Then, it uses the version of the Sum() function that works on tables ( Sum(table, expression) ) to sum all of these values up. Then it joins it to "/13" like you were doing before.
So, that will work. One thing you might want to consider, though, is making a horizontal gallery instead of all of these labels. If you can give the horizontal gallery a table of the values as text, you can just have one label inside the gallery with Text=ThisItem.Value and the sum expression can simplify to (assuming it's still strings of non-zero numbers or blank):
Sum( ForAll( MyGallery.AllItems, If( !IsBlank(Value), 1, 0 ) ), Value ) & "/" & CountRows(MyGallery.AllItems)
Finally, here's some relevant documentation links, if you want to learn more:
Sum Function (and other similar aggregate functions)
CountRows Function (and other similar functions)
Anyway, I hope this helps!
Hello ericonline,
Interesting problem!
The Sum() function takes 1 or more arguments and sums them up. What's happening here is the || (Or) function is returning false, because none of the controls are true (they can't be, they're controls. This is different from programming in JavaScript, for example, which evaluates whether operands are truthy or falsey. PowerApps just evaluates whether it's actually true here). So, we get the following:
Sum( If( IsBlank(false), 0, 1) )
Sum( If( false, 0, 1) )
Sum( 1 )
1
So that's why you're experiencing what you are. But how do we fix it? I feel like there should be a nice elegant solution, but I haven't been able to come up with it, yet. I do know a solution, which I'll share, but if someone figures out a better one, please feel free to add it to the discussion!
Anway, I think the following will work for you:
Sum( ForAll( [ Value(label_complianceComp1.Text), Value(label_complianceComp2.Text), Value(label_complianceComp3.Text), Value(label_complianceComp4.Text), Value(label_complianceComp5.Text), Value(label_complianceComp6.Text), Value(label_complianceComp7.Text), Value(label_complianceComp8.Text), Value(label_complianceComp9.Text), Value(label_complianceComp10.Text), Value(label_complianceComp11.Text), Value(label_complianceComp12.Text), Value(label_complianceComp13.Text) ], If( !IsBlank(Value), 1, 0 ) ), Value ) & "/13"
What this does is it gets the Text property of each label and finds the value of that using the Value() function. It makes a table of these values (basically a list). Then, it uses the ForAll() function to loop over these values and return a table of 1's and 0's (1 if not blank, 0 if blank). Then, it uses the version of the Sum() function that works on tables ( Sum(table, expression) ) to sum all of these values up. Then it joins it to "/13" like you were doing before.
So, that will work. One thing you might want to consider, though, is making a horizontal gallery instead of all of these labels. If you can give the horizontal gallery a table of the values as text, you can just have one label inside the gallery with Text=ThisItem.Value and the sum expression can simplify to (assuming it's still strings of non-zero numbers or blank):
Sum( ForAll( MyGallery.AllItems, If( !IsBlank(Value), 1, 0 ) ), Value ) & "/" & CountRows(MyGallery.AllItems)
Finally, here's some relevant documentation links, if you want to learn more:
Sum Function (and other similar aggregate functions)
CountRows Function (and other similar functions)
Anyway, I hope this helps!
Hello Paul,
Thank you for the detailed reply. I really appreciate it and yes, your solution to sum all labels worked great.
Since you are familiar with this puzzle piece, I'd like to go out on a limb and push the conversation a bit further as I'm really stuck on the label query itself:
The labels are already part of a vertical Gallery as shown here. I'm nervous about nesting Galleries as I don't fully understand them.
Each label is looking up records in a Sharepoint list like so. The "." notation on the end is very strange to me!
Here is the Sharepoint list:
The label query is not correct nor complete.
There is a writeup on the issue here as well with a bit different context. Perhaps thats a better place to take this (if you'll come along 🙂 ).
Its very cool how PowerApps opens up development to a population of folks who would otherwise not be engaged. Thank you for the very useful product you've developed. I'll be honest and say its right up there with OneNote in my top 2 products that Microsoft makes.
User | Count |
---|---|
253 | |
106 | |
88 | |
51 | |
43 |