cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
shabilass
Helper II
Helper II

Sum of numbers from SharePoint list columns

Dear all,

 

I have SharePoint list columns as below. The default value of each columns are:

FRR1 = 101, FRR2 = 102, FRR3 = 103, FRR4 = 104, FRR5 = 105

total rating.PNG

In PowerApps, I displayed the data in Gallery view. I wanted to add all numbers from FRR1 until FRR5 columns and store it in Total Rating column. The app will sum up all numbers in the columns (that are not default value).

 

On OnSelect button property, I put below code. However, it does not sum up all items correctly. As you can see on Total Rating column above, the result are all "3". Any solutions to solve this? 

 

ForAll(RenameColumns(Gallery1.AllItems, "ID", "IDD"),
    Patch('List Name', LookUp('List Name', ID = IDD),
    {
        'Total Rating': Sum(
        LookUp('List Name', FRR1 <> 101, FRR1),
        LookUp('List Name', FRR2 <> 102, FRR2), 
        LookUp('List Name', FRR3 <> 103, FRR3), 
        LookUp('List Name', FRR4 <> 104, FRR4), 
        LookUp('List Name', FRR5 <> 105, FRR5))
    }))

 

 

Thank you in advance!

1 ACCEPTED SOLUTION

Accepted Solutions

Hi@shabilass,

Do you want to sum the real values rather than the default values in these 5 fields?

Follow your lead, you want to define if the current value within the field is a real one firstly. If it is, adds the number into the sum operation, otherwise, do not add.

I do not know that you have a form to submit, as such that you need to try the following workaround.

Set the Default property of the TextInput control corresponding to the 'Total Rating' column as below:

Sum(
    If(
        ThisItem.FRR1 <> "101",
        ThisItem.FRR1
    ),
    If(
        ThisItem.FRR2 <> "102",
        ThisItem.FRR2
    ),
    If(
        ThisItem.FRR3 <> "103",
        ThisItem.FRR3
    ),
    If(
        ThisItem.FRR4 <> "104",
        ThisItem.FRR4
    ),
    If(
        ThisItem.FRR5 <> "105",
        ThisItem.FRR5
    )
)

Set the Update property of the data card as below:

DataCardValue19.Text

Note: DataCardValue19 represents the TextInput control corresponding to the 'Total Rating' column.

111101GIF.gif

Hope it could help.

Best Regards,

Qi

Best Regards,
Qi

View solution in original post

4 REPLIES 4
v-qiaqi-msft
Community Support
Community Support

Hi@shabilass,

Based on the issue that you mentioned, do you want to sum every row from FRR1 until FRR5 and store the summed result in the 'Total Rating' column?

Could you please share a bit more about the scenario, I am quite not clear about your formula:

  1. Do you want to sum all the default value or the real value within the corresponding column?
  2. Why you need a ForAll() function?

If my understanding is correct, you want to add all numbers in a row. If so, you just need to use the Sum() function as below:

Set the Text property of the Label represents the  'Total Rating' column as below:

 

Sum(
    ThisItem.FRR1,
    ThisItem.FRR2,
    ThisItem.FRR3,
    ThisItem.FRR4,
    ThisItem.FRR5
)

 

Note: I assume that all the columns are Text types.

sum.png

Hope it could help.

Best Regards,

Qi

Best Regards,
Qi

Hi @v-qiaqi-msft

 

1. Do you want to sum all the default value or the real value within the corresponding column?

- I wanted to sum all the real value of each item and store it in Total Rating column. If the column is not yet updated into real value, it will not sum it up.

 

2. Why you need a ForAll() function?

- I used ForAll() to update "Total Rating" of all items in the gallery at the same time once the Submit Button in PowerApps is clicked. Correct me if I'm wrong.

 

Maybe I can add a Label in the gallery and set the Text as you mentioned and patch it into SharePoint list to update the column. Now, any ideas to sum up the real value only?

 

Thank you.

 

Hi@shabilass,

Do you want to sum the real values rather than the default values in these 5 fields?

Follow your lead, you want to define if the current value within the field is a real one firstly. If it is, adds the number into the sum operation, otherwise, do not add.

I do not know that you have a form to submit, as such that you need to try the following workaround.

Set the Default property of the TextInput control corresponding to the 'Total Rating' column as below:

Sum(
    If(
        ThisItem.FRR1 <> "101",
        ThisItem.FRR1
    ),
    If(
        ThisItem.FRR2 <> "102",
        ThisItem.FRR2
    ),
    If(
        ThisItem.FRR3 <> "103",
        ThisItem.FRR3
    ),
    If(
        ThisItem.FRR4 <> "104",
        ThisItem.FRR4
    ),
    If(
        ThisItem.FRR5 <> "105",
        ThisItem.FRR5
    )
)

Set the Update property of the data card as below:

DataCardValue19.Text

Note: DataCardValue19 represents the TextInput control corresponding to the 'Total Rating' column.

111101GIF.gif

Hope it could help.

Best Regards,

Qi

Best Regards,
Qi

View solution in original post

Thank you so much for helping me. It's working well!

Helpful resources

Announcements
PA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

Power Query PA Forum 768x460.png

Check it out!

Did you know that you can visit the Power Query Forum in Power BI and now Power Apps

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Users online (1,106)