Sum( If( IsBlank assistance

[ericonline]

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

[Paul_C · Accepted 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:

ForAll Function

Sum Function (and other similar aggregate functions)

Value Function

CountRows Function (and other similar functions)

Gallery Control

Anyway, I hope this helps!

[ericonline]

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. 

• Not correct because as you can see in the Sharepoint List, rdm_cost_center S0801 completed comp1_num on 6/1/2018 but its not showing in the label.
  • I thought LookUp would go down the column and pull the first record it found. This is not the case here. 
• Not complete because the query is not comparing the current year, half, or month against the records to populate the label. 
  • I have global variables set for current year, current half, and current month in the app, but how do I compare them against the same values in each record, then return the results? 

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.