cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
NickAdams1975
Level: Powered On

First App Issues - Calculating Value from Input Data and Write Back

Hi,

 

I am trying to complete my first Canvas App based on an Excel data source in OneDrive. It (should be!) a very simple app that takes existing risk data in the data source and then the end user can update this. This all works fine. The single issue I am having is that based on the responses (low, medium, high) to the risk questions I want the app to calculate a 'risk score'.

 

E.g. Question 1: Risk of rain affecting construction = Low, Medium or HIgh (a score of 1,2,5) is created using a formula. 

 

I have a series of 10 questions in a form (all with original data in the data source) and they all create their own risk score in a separate label control. Thus there are ten label controls that have a single number in each of them. I have then hidden these labels as I don't want the end user to see these.

 

I then created a final label control that simply sums the other ten labels to give a final risk score (e.g. 58). This risk score is then used to select one of three 'Levels of Control' for each construction project in the app. E.g. if the risk score is between a certain range it calls it a high risk project and should write this back into the 'Level of Control' field in the data source. Calculating all the correct values works fine.

 

So...The problem!

 

First I tried to include the 'Level of Control' field (which has pre-existing data in the data source) at the bottom of the form that included the questions; this created a circular reference error.

Then I tried adding a second form to the top of the screen and this worked absolutely fine on the developer screen but as soon as it went into 'play mode' the form at the top of the page with this data stated 'no item to display'

 

It seems like as a noob I am just going about this simple calculation and over-write of 'Level of Control' using the completely wrong method! Can anyone help steer me in the right direction as to method for this please?

 

Any help, very much appreciated.

 

Thanks,

 

Nick

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: First App Issues - Calculating Value from Input Data and Write Back

Hi @NickAdams1975 

 

For doing so, please update your collection as :
ClearCollect(RiskCalculator,{Question:"Q1",Name:"Low",Value:1},{Question:"Q1",Name:"Medium",Value:2},{Question:"Q1",Name:"High",Value:5},{Question:"Q2",Name:"Low",Value:3},{Question:"Q2",Name:"Medium",Value:5},{Question:"Q2",Name:"High",Value:7},{Question:"Q3",Name:"Low",Value:5},{Question:"Q3",Name:"Medium",Value:7},{Question:"Q3",Name:"High",Value:9})
This includes an attribute for question Name and when you are fetching the value, use the below expression:
 
Sum(LookUp(RiskCalculator,Question = "Q1" && Name=Dropdown1_2.Selected.Result).Value,LookUp(RiskCalculator,Question = "Q2" &&Name=Dropdown1.Selected.Result).Value,LookUp(RiskCalculator,Question = "Q3" &&Name=Dropdown1_1.Selected.Result).Value)
This will fetch Risk value corresponding to a specific question at a specific risk level.
MicrosoftTeams-image (133).png
Hope this Helps!
3 REPLIES 3
Super User
Super User

Re: First App Issues - Calculating Value from Input Data and Write Back

Hi @NickAdams1975 

 

So to overcome this issue, you can use the following workarounds:
 
1) Instead of having 10 separate hidden labels, you can use a local collection to store all the risk name with its value
ClearCollect(RiskCalculator,{Name:"Low",Value:1},{Name:"Medium",Value:2},{Name:"High",Value:5})
and when you are calculating the sum, you can reference to the collection as:
 
Sum(LookUp(RiskCalculator,Name=Dropdown1_2.Selected.Name).Value,LookUp(RiskCalculator,Name=Dropdown1.Selected.Name).Value,LookUp(RiskCalculator,Name=Dropdown1_1.Selected.Name).Value)
If you donot have dropdown control, then you need to replace Dropdown1.Selected.Name with the corresponding control reference.
 
This sum can be set as a variable or you can show it on a label and refer this label in the "Update" property as:
If(Value(Label2_1.Text) > 10,"High Risk Project",Value(Label2_1.Text) > 5 && Value(Label2_1.Text) <=10,"Medium Risk Project",Value(Label2_1.Text) > 0 && Value(Label2_1.Text) <=5,"Medium Risk Project")
Through this you will be able to patch correct value into the form.MicrosoftTeams-image (132).png

 

2) You are seeing "No Items to display" because the form is not initiated, you need to set a button control/ or update the "OnVisible" property of your screen to either edit or create a new form using, NewForm(FormName) or EditForm(Form1).
 
If you want a specific item to be displayed in the form, you can set the "Item" property of your form.
 
Hope this Helps!
 
If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!
NickAdams1975
Level: Powered On

Re: First App Issues - Calculating Value from Input Data and Write Back

Thats really great.

 

One issue is that each of the questions I have has a different risk rating, e.g.

 

Q1) Inclement weather risk (low = 1, medium=2, high =3)

Q2) Poor ground conditions (low = 3, medium = 5, high =9)

 

So it is different numbers for each of the risks that needs to be summated into the overall 'risk score'

 

Do you have any ideas on this? I can feel I am only minutes away from success now with your help!

 

Thanks,

 

Nick

Super User
Super User

Re: First App Issues - Calculating Value from Input Data and Write Back

Hi @NickAdams1975 

 

For doing so, please update your collection as :
ClearCollect(RiskCalculator,{Question:"Q1",Name:"Low",Value:1},{Question:"Q1",Name:"Medium",Value:2},{Question:"Q1",Name:"High",Value:5},{Question:"Q2",Name:"Low",Value:3},{Question:"Q2",Name:"Medium",Value:5},{Question:"Q2",Name:"High",Value:7},{Question:"Q3",Name:"Low",Value:5},{Question:"Q3",Name:"Medium",Value:7},{Question:"Q3",Name:"High",Value:9})
This includes an attribute for question Name and when you are fetching the value, use the below expression:
 
Sum(LookUp(RiskCalculator,Question = "Q1" && Name=Dropdown1_2.Selected.Result).Value,LookUp(RiskCalculator,Question = "Q2" &&Name=Dropdown1.Selected.Result).Value,LookUp(RiskCalculator,Question = "Q3" &&Name=Dropdown1_1.Selected.Result).Value)
This will fetch Risk value corresponding to a specific question at a specific risk level.
MicrosoftTeams-image (133).png
Hope this Helps!