Help with Writing function to properly update to SharePoint

[Anonymous]

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. 

[v-xida-msft]

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:

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:

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:

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,

[Anonymous]

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

[Anonymous]

@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. 

[Anonymous]

@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

[v-xida-msft · Accepted solution]

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,

[Anonymous]

@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