Count number of empty fields in a form using Power Apps

[casr133]

Hi, I have a SharePoint List that I'm customizing using PowerApps. My list has over 200 columns and I want to find a way to display how many empty columns are left once someone starts a new entry. For example, when starting, it should display (200 columns blank) and as more and more columns are completed, that number should decrease accordingly.

Thanks.

[mmollet · Accepted solution]

This is similar to another request from earlier today but you have many more columns. The same thing should work but might take a bit to code! (copy paste will be a godsend here)

Sum(

If(IsBlank(DataCardValue1), 0, 1),

If(IsBlank(DataCardValue2), 0, 1),

If(IsBlank(DataCardValue3), 0, 1),

If(IsBlank(DataCardValue4), 0, 1),

If(IsBlank(DataCardValue5), 0, 1),

etc...

)

Do the above for each of the 200 and then as they are answered you will add 1 until all are answered and 200/200. You can put this in a textbox/label or use it to augment the size of a rectangle to use as a progress bar etc. 

Progress Bar:

• Create a label (backLabel)
  • make the border 2
  • make the background no color
  • make the width 600 and the height 80
• Now create a second label (frontLabel)
  • make the border 2
  • make the background Color.LimeGreen
  • make sure it is directly on top of the first label and has the same height
  • make the width equal to: (Value(LabelWithTotalAnswered.Text)/200) * backLabel.Width (pictures below for example of how it would look)

If you dont want the total showing, you would still need the label with the total showing so you could just send it to the back of the screen and make it not visible. that way the completion bar can still read the value it needs to work but users wont have to see it. It is just a middle man essnentially to help clean up the code.

Little more than you asked for but oh well. GL!

[bocoman1]

Thank you for this solution.

I wanted to also add that if you are doing this with a variable total, it is the same process, but instead of using a hard coded number (200 in this case) you are able to input variable divided by variable. I do not print total or number complete, just a percent. I have implemented this and the only issue that I run into is when the form/app is first opened, it notifies the user of a 'divide by 0' error. Other than that error, it works perfectly.

This is the expression that I used to get my percentage, where label63 is visible fields (total fields), label63_2 is completed fields and label63_1 is fields marked as not applicable.

Text((Value(Label63_2.Text) / (Value(Label63.Text) - Value(Label63_1.Text)))*100, "0.0%")

I had to use this solution as my form has 7 possible combinations of total fields. Formatting and everything else in the solution post is the same.

Also, since mine is part of a SharePoint form rather than an app, I needed the percentage to show in the list so that the user can glance at the field and know if it is complete or not. To do this, I added another column to the list, inserted into the form, copied the formula from the progress bar and changed visible to "false" so that it does not show on the form.