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

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

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

View solution in original post

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

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

PA Community Call

Power Apps Community Call

Next call is happening on April 21st at 8a PST.

MBAS Carousel

Sign up for our May 4th event!

May the fourth be with you, join us online!

secondImage

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors
Users online (53,149)