cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Anonymous
Not applicable

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
Highlighted
Community Support
Community Support

Re: Help with Writing function to properly update to SharePoint

Hi @Anonymous ,

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
Highlighted
Community Support
Community Support

Re: Help with Writing function to properly update to SharePoint

Hi @Anonymous ,

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.
Highlighted
Anonymous
Not applicable

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
Anonymous
Not applicable

Re: Help with Writing function to properly update to SharePoint

 
Highlighted
Anonymous
Not applicable

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. 

Highlighted
Anonymous
Not applicable

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

Highlighted
Community Support
Community Support

Re: Help with Writing function to properly update to SharePoint

Hi @Anonymous ,

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

Highlighted
Anonymous
Not applicable

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
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

secondImage

Return to Workplace

Reopen responsibly, monitor intelligently, and protect continuously with solutions for a safer work environment.

secondImage

Super Users Coming in August

We are excited for the next Super User season.

secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (7,834)