Evaluating a calculated number column from SharePoint (without the help of forms or galleries)

[carterprine]

Hello,

I have a PowerApp which is built somewhat unconventionally (for me)... purely to create a much nicer-looking interface for a form. This might not be the ideal approach, but I'm sold on it because it looks cool (hint/hint Microsoft, please allow more flexibility to design our own controls... no offense but the standard ones look very meh). I have a bunch of yes/no columns which get populated by patching from my PowerApp. As each column is populated, I have calculated columns in SharePoint which tally the score (total number of "Yes" responses) for each section and screen. When the user clicks a button, I would like to conditionally send them to a specific screen depending on their score. I'm using the following formula, with many other attempts with no success. 

Here is the If()Navigate() code in the OnSelect property of the button:

If(First(Filter('IT Change Project Assessment Survey', Title=ProjectName.Text && 'Created By'.Email=User().Email)).'Part 2 Score'<9, Navigate(Part3,ScreenTransition.Fade))

It gives me the error "Invalid argument type. Expecting a number."

But wait... the calculated column in SharePoint IS a number (and formatted to be a number with 0 decimal spaces)... Paint me confused. 

For those interested in the eye-candy: here are screenshots. Any help will be greatly appreciated. I'm not using forms or galleries... just patching from elements, which has been challenging but I really prefer to control the aesthetic (something PowerApps is not fantastic with).

[WarrenBelz]

Hi @carterprine ,

Please have a read of this blog of mine on data structure - you may re-think the use of historical data types when all you really need in Power Apps is somewhere to store the data (generally Text/Numeric/Date).

Calculated and complex field types will give you nothing but unnecessary (unless you really need to use them) grief and frustration.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[carterprine]

Thank you @WarrenBelz. I'm happy to go a different route... Do you have an idea of how I can achieve the same goal from within PowerApps? E.g. sum of yes/no columns in SharePoint marked as "Yes" and those marked as "No"?

[carterprine · Accepted solution]

I actually ended up going about it a different way. I created a global "Score" variable by summing the count of columns marked "yes" (true for y/n columns) for the current row. Then I used a conditional UpdateContext to show a custom message depending on the user's score. 

Here, I'm setting the variable by adding any columns for the filtered (current) row which are marked as "yes" (true). 

Set(Score,
(Sum(CountIf(Filter('IT Change Project Assessment Survey',Title=ProjectName.Text && 'Created By'.Email=User().Email),'3 Operating Companies and/or 3rd Party Impact'=true)+CountIf(Filter('IT Change Project Assessment Survey',Title=ProjectName.Text && 'Created By'.Email=User().Email),'4 Location Impacts'=true)+CountIf(Filter('IT Change Project Assessment Survey',Title=ProjectName.Text && 'Created By'.Email=User().Email),'5 Functional Impacts'=true)+CountIf(Filter('IT Change Project Assessment Survey',Title=ProjectName.Text && 'Created By'.Email=User().Email),'6 Stakeholder Impact Conflicts'=true)+CountIf(Filter('IT Change Project Assessment Survey',Title=ProjectName.Text && 'Created By'.Email=User().Email),'7 Current Process Impact'=true)+CountIf(Filter('IT Change Project Assessment Survey',Title=ProjectName.Text && 'Created By'.Email=User().Email),'8 Acceptance'=true)+CountIf(Filter('IT Change Project Assessment Survey',Title=ProjectName.Text && 'Created By'.Email=User().Email),'9 Values/Identity Change'=true)+CountIf(Filter('IT Change Project Assessment Survey',Title=ProjectName.Text && 'Created By'.Email=User().Email),'10 Prior Attempt'=true)+CountIf(Filter('IT Change Project Assessment Survey',Title=ProjectName.Text && 'Created By'.Email=User().Email),'11 Legacy Workarounds'=true)+CountIf(Filter('IT Change Project Assessment Survey',Title=ProjectName.Text && 'Created By'.Email=User().Email),'12 Cost of Errors'=true)+CountIf(Filter('IT Change Project Assessment Survey',Title=ProjectName.Text && 'Created By'.Email=User().Email),'13 Criticality of Business Objectives'=true)+CountIf(Filter('IT Change Project Assessment Survey',Title=ProjectName.Text && 'Created By'.Email=User().Email),'14 Systematic Approach'=true))))

 Here, I'm using UpdateContext to show the user a custom message:

If(Score1 <5, UpdateContext({show1msg:true}), UpdateContext({show1msg:false}));
If(Score1 >4 && Score1<9, UpdateContext({show2msg:true}), UpdateContext({show2msg:false}));
If(Score1 >9, UpdateContext({show3msg:true}), UpdateContext({show3msg:false}))

[WarrenBelz · Accepted solution]

Hi @carterprine ,

A lot of repetitive values there - try this format

With(
   {
      wScores:
      Filter(
         'IT Change Project Assessment Survey',
         Title=ProjectName.Text && 'Created By'.Email=User().Email
      )
   },
Set(
   Score,
   Sum(
      CountRows(
	     Filter(wScores,'3 Operating Companies and/or 3rd Party Impact')
      ) +
      CountRows(
         Filter(wScores,'4 Location Impacts')
      )  + 
      CountRows(
         Filter(wScores,'5 Functional Impacts')
      )  + 
      CountRows(
         Filter(wScores,'6 Stakeholder Impact Conflicts')
      )  + 
      CountRows(
         Filter(wScores,'7 Current Process Impact')
      )  + 
      CountRows(
         Filter(wScores,'8 Acceptance')
      )  + 
      CountRows(
         Filter(wScores,'9 Values/Identity Change')
      )  + 
      CountRows(
         Filter(wScores,'10 Prior Attempt')
      )  + 
      CountRows(
         Filter(wScores,'11 Legacy Workarounds')
      )  + 
      CountRows(
         Filter(wScores,'12 Cost of Errors')
      )  + 
      CountRows(
         Filter(wScores,'13 Criticality of Business Objectives')
      )  + 
      CountRows(
         Filter(wScores,'14 Systematic Approach')
      )
   )
)

also your Variables

UpdateContext({show1msg:Score1 <5});
UpdateContext({show2msg:Score1 >4 && Score1<9});
UpdateContext({show3msg:Score1 >9})

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[carterprine]

Thank you @WarrenBelz! This is super helpful! As you can tell, I'm still far from jedi status in PowerApps 😂