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

Help with Writing function to properly update to SharePoint

Hello, 

 

I recently posted a question which I thought I was able to find the solution for, however, I am still struggling to find the solution. 

 

I have made a app through SharePoint where I have a header (Personal Appearance) with three questions after. 

 

In the app I display three values in a drop down list: Expectation Not Met, Expectation Met, Expectation exceeded. I need to be able to display the text but be able to add the values 1,3,5 to the text so I can average out the scores.

 

I shared an image from my SharePoint for how a recored response should look. I aslo included images of the app that I created from SharePoint, but I need the Personal Appearance to show as A header (Ill provide an app for how it should look) instead of having a input box.  In SharePoint the header should contain the averaged out score from each response.

 

I am not sure where I should put the function because of what is already in the data field

 

If there is any additional questions please ask. thank you. 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Help with Writing function to properly update to SharePoint

Hi @bubs ,

Do you want to save a average score within the Personal Appearance field column based on four question answers (including "Not Applicable")? Which value does the "Not Applicable" represents for? Zero (0) ?

Do you want to disregard N/A and not include it in the average function if selected?

 

If you want to disregard N/A and not include it in the average function if selected, I have made a test on my side, please take a try with the following workaround:

Set the Update property of the Personal Appearance Data card in your Edit form to following:

If(
DataCardValue7.Selected.Value = "Not Applicable",
If(
DataCardValue8.Selected.Value = "Not Applicable",
If(
DataCardValue9.Selected.Value = "Not Applicable",
0,
Average( Switch(DataCardValue9.Selected.Value, "Expectation Not Met", 1, "Expectation Met", 3, "Expectation exceeded", 5, 0) )
),
If(
DataCardValue9.Selected.Value = "Not Applicable",
Average( Switch(DataCardValue8.Selected.Value, "Expectation Not Met", 1, "Expectation Met", 3, "Expectation exceeded", 5, 0) ),
Average( Switch(DataCardValue8.Selected.Value, "Expectation Not Met", 1, "Expectation Met", 3, "Expectation exceeded", 5, 0), Switch(DataCardValue9.Selected.Value, "Expectation Not Met", 1, "Expectation Met", 3, "Expectation exceeded", 5, 0) )
)
),
If(
DataCardValue8.Selected.Value = "Not Applicable",
If(
DataCardValue9.Selected.Value = "Not Applicable",
Average( Switch(DataCardValue7.Selected.Value, "Expectation Not Met", 1, "Expectation Met", 3, "Expectation exceeded", 5, 0) ),
Average( Switch(DataCardValue7.Selected.Value, "Expectation Not Met", 1, "Expectation Met", 3, "Expectation exceeded", 5, 0), Switch(DataCardValue9.Selected.Value, "Expectation Not Met", 1, "Expectation Met", 3, "Expectation exceeded", 5, 0) )
),
If(
DataCardValue9.Selected.Value = "Not Applicable",
Average( Switch(DataCardValue7.Selected.Value, "Expectation Not Met", 1, "Expectation Met", 3, "Expectation exceeded", 5, 0), Switch(DataCardValue8.Selected.Value, "Expectation Not Met", 1, "Expectation Met", 3, "Expectation exceeded", 5, 0) ),
Average( Switch(DataCardValue7.Selected.Value, "Expectation Not Met", 1, "Expectation Met", 3, "Expectation exceeded", 5, 0), Switch(DataCardValue8.Selected.Value, "Expectation Not Met", 1, "Expectation Met", 3, "Expectation exceeded", 5, 0), Switch(DataCardValue9.Selected.Value, "Expectation Not Met", 1, "Expectation Met", 3, "Expectation exceeded", 5, 0) )
)
)
)

Note: The DataCardValue7 represents the Text Input box within the "Overall Professional Image" Data card, the DataCardValue8 represents the Text Input box within the "Uniform in Compliance with YX Standards" Data card, the DataCardValue 9 represents the Text Input box within the "Appropriate Personal Grooming" Data card.

 

Please take a try with above solution, then check if the issue is solved. If you have solved your problem, please go ahead to click "Accept as Solution" to identify this thread has been solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Community Support Team
Community Support Team

Re: Help with Writing function to properly update to SharePoint

Hi @bubs ,

Could you please share a bit more about your scenario?

Do you want to make the Personal Appearance field to show as a Header instead of having a Input box within it?

Are the three question columns all Choice type column in your SP list?

Is the Personal Appearance column a Number type column? Do you want to save a average score within this column based on three question answers?

 

For your first question, if you want to make the Personal Appearance field to show as a Header instead of having a Input box within it, you could consider unlock the Personal Appearance field (Data card) within your Edit form, then remove the Text Input box from it.

I have made a test on my side, please take a try with the following workaround:Test.gif

 

 

For your second question,  if you want to save a average score within the Personal Appearance column (Number column) based on three question answers (Expectation Not Met, Expectation Met, Expectation exceeded), please take a try with the following workaround:6.JPG

Set the Update property of the Personal Appearance Data card in your Edit form to following:

Average(
        Switch(DataCardValue7.Selected.Value, "Expectation Not Met", 1, "Expectation Met", 3, "Expectation exceeded", 5, 0),
        Switch(DataCardValue8.Selected.Value, "Expectation Not Met", 1, "Expectation Met", 3, "Expectation exceeded", 5, 0),
        Switch(DataCardValue9.Selected.Value, "Expectation Not Met", 1, "Expectation Met", 3, "Expectation exceeded", 5, 0)
)

Please check the following GIF screenshot for more details:Test.gif

 

If you want the number value stored in the  Personal Appearance field in your SP list remain one decimal places, please modify above formula as below:

Round(
      Average(
             Switch(DataCardValue7.Selected.Value, "Expectation Not Met", 1, "Expectation Met", 3, "Expectation exceeded", 5, 0),
             Switch(DataCardValue8.Selected.Value, "Expectation Not Met", 1, "Expectation Met", 3, "Expectation exceeded", 5, 0),
             Switch(DataCardValue9.Selected.Value, "Expectation Not Met", 1, "Expectation Met", 3, "Expectation exceeded", 5, 0)
     ),
     1
)

Please take a try with above solution, then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
bubs
Level: Powered On

Re: Help with Writing function to properly update to SharePoint

 

Thank you for the solution, I am going to try it now and see if it works.

 

I am currently working on digitizing the form linked below.

 

The Personal Appearance is the section that contains the three questions which I would like to display as a header, but be able to use it to record the data from the three questions.

 

All the questions are choice, though i do not know what would be best.  I currently have personal appearance as just a single line of text.

 

And yes I would like to average the score in that column sho when I bring it into SharePoint you can easy see the average for each section

Highlighted
bubs
Level: Powered On

Re: Help with Writing function to properly update to SharePoint

 
bubs
Level: Powered On

Re: Help with Writing function to properly update to SharePoint

@v-xida-msft ,

 

Sorry I was using the wrong names I was able to figure it out and get the average to return, However I am not getting the correct average returned. You know why that might be? I copied the same average function that you used. 

bubs
Level: Powered On

Re: Help with Writing function to properly update to SharePoint

@v-xida-msft 

 

I have got the function to work complety now Thank you. I just have one last question.

 

I will now have to start averaging out 4 options: "Expectation Not Met", "Expectation Met", "Ecpectation Exceeded", and the new option "Not Applicable"

 

How do I modify the average function to disregard N/A and not include it in the average if selected?

 

Thanks

Community Support Team
Community Support Team

Re: Help with Writing function to properly update to SharePoint

Hi @bubs ,

Do you want to save a average score within the Personal Appearance field column based on four question answers (including "Not Applicable")? Which value does the "Not Applicable" represents for? Zero (0) ?

Do you want to disregard N/A and not include it in the average function if selected?

 

If you want to disregard N/A and not include it in the average function if selected, I have made a test on my side, please take a try with the following workaround:

Set the Update property of the Personal Appearance Data card in your Edit form to following:

If(
DataCardValue7.Selected.Value = "Not Applicable",
If(
DataCardValue8.Selected.Value = "Not Applicable",
If(
DataCardValue9.Selected.Value = "Not Applicable",
0,
Average( Switch(DataCardValue9.Selected.Value, "Expectation Not Met", 1, "Expectation Met", 3, "Expectation exceeded", 5, 0) )
),
If(
DataCardValue9.Selected.Value = "Not Applicable",
Average( Switch(DataCardValue8.Selected.Value, "Expectation Not Met", 1, "Expectation Met", 3, "Expectation exceeded", 5, 0) ),
Average( Switch(DataCardValue8.Selected.Value, "Expectation Not Met", 1, "Expectation Met", 3, "Expectation exceeded", 5, 0), Switch(DataCardValue9.Selected.Value, "Expectation Not Met", 1, "Expectation Met", 3, "Expectation exceeded", 5, 0) )
)
),
If(
DataCardValue8.Selected.Value = "Not Applicable",
If(
DataCardValue9.Selected.Value = "Not Applicable",
Average( Switch(DataCardValue7.Selected.Value, "Expectation Not Met", 1, "Expectation Met", 3, "Expectation exceeded", 5, 0) ),
Average( Switch(DataCardValue7.Selected.Value, "Expectation Not Met", 1, "Expectation Met", 3, "Expectation exceeded", 5, 0), Switch(DataCardValue9.Selected.Value, "Expectation Not Met", 1, "Expectation Met", 3, "Expectation exceeded", 5, 0) )
),
If(
DataCardValue9.Selected.Value = "Not Applicable",
Average( Switch(DataCardValue7.Selected.Value, "Expectation Not Met", 1, "Expectation Met", 3, "Expectation exceeded", 5, 0), Switch(DataCardValue8.Selected.Value, "Expectation Not Met", 1, "Expectation Met", 3, "Expectation exceeded", 5, 0) ),
Average( Switch(DataCardValue7.Selected.Value, "Expectation Not Met", 1, "Expectation Met", 3, "Expectation exceeded", 5, 0), Switch(DataCardValue8.Selected.Value, "Expectation Not Met", 1, "Expectation Met", 3, "Expectation exceeded", 5, 0), Switch(DataCardValue9.Selected.Value, "Expectation Not Met", 1, "Expectation Met", 3, "Expectation exceeded", 5, 0) )
)
)
)

Note: The DataCardValue7 represents the Text Input box within the "Overall Professional Image" Data card, the DataCardValue8 represents the Text Input box within the "Uniform in Compliance with YX Standards" Data card, the DataCardValue 9 represents the Text Input box within the "Appropriate Personal Grooming" Data card.

 

Please take a try with above solution, then check if the issue is solved. If you have solved your problem, please go ahead to click "Accept as Solution" to identify this thread has been solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

bubs
Level: Powered On

Re: Help with Writing function to properly update to SharePoint

@v-xida-msft ,

 

I have tried the solution and everything seems to work other than one issue. 

 

I receieve no error message but when I select: Expectation Met, Expectation Not Met, N/A, I get a total of 8 points. I receive an average for that section of 2.67, but I want it to be 4. THis is happening because its divided by 3 rather than 2.

I want the average to be done only on expecation not met, met, exceeded. 

 

Do you know how I can do this?

 

Let me know if you need anymore detail

 

Thanks

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,193)