filter and sum

[RamyEdrees]

Hi Everyone,

First allow me to thank everyone in this community as I have learnt a lot from every post I read. I am new to Power Apps but I used to play around with MS Access for quite sometime long time ago. We have a childcare as a family business and I am like the inhouse developer 😊I am working on the core of our business which is the roster. I have successfully built the roster on SP along with the another one for the Staff and created the Canvas App for it.

My Roster SP list is the main one where all the data is patched on. All of this is going fine but I am stuck on the following.

Each record is a week and I have several columns:

Name columns: Staffname1, staffname2,.. etc

Status columns: staffstatus_1, staffstaus_2,…etc

Hours columns: staffhours_1, staffhours_2,…etc

Payment columns: staffpay_1, staffpay_2,…etc

NB: Names can be repeated, status can be repeated, everything can be repeated.

Here is my request if you can help me.

I want to have a new screen where all names will be filtered based on status and to sum the hours based on that and then multiply each hours by the rate.

End screen should be something like that:

Thanks in advance for your help 🙂

[Bilakanti]

@RamyEdrees You can use group by name and get the table and calculate the sum of the hours with rates.

[RamyEdrees]

Thanks @Bilakanti but it did not work out for me. 

[WarrenBelz]

Hi @RamyEdrees ,

For the hours and payment (you did not say where the Rate was coming from or how the Status was chosen) 

AddColumns(
   AddColumns(
      GroupBy(
         Filter(
            SPList,
            Status = YourValueChosen
         ),
         "Name",
         "Data"
      ),
      "Hours",
      Sum(Data, Hours)
   ),
   "Payment",
   Hours * YourRate
)

The Totals would be

Sum(GallereName.AllItems, FieldName)

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications)   Visit my blog Practical Power Apps

[RamyEdrees]

Hi @WarrenBelz ,

Thanks for your reply and help. I tried to plug in the suggested solution but it did not work. I am getting the hourly rate from another SPlist for the staff details. For the hours, PowerApss calculates them based on the difference between start time and finish time. 

Each record in my Roster list is:

• Staff Name
• Start Time
• Finish time
• Hours
• Status

In the power apps I calculate the difference between the start and end time and get the hours. For the Hourly rate I use lookup based on the staff name and get the rate.

(N.B.: I repeat that for many staff in the same record. I am expecting the number of columns to be around 1000)

All what I want is to have a table like where for each staff I will have the total hours based on the status as per the photo in my original post.  

If you can help me with that I will really appreciate it 😊

[WarrenBelz]

Hi @RamyEdrees ,

I can see some potential issues here already - there are several "hidden delegation" limits (AddColumns, GroupBy and ForAll) in what I posted (so it will only work up to your Delegation limit), however I need to know firstly:-

• What does not work ? Are there errors (if so what) or it simply does not produce the desired result ?
• I assume Hours is a Numeric column and the calculation is already done in Power Apps. If this is a SharePoint calculated column, none of this will work.
• How do you choose the status or are you wanting to group by that too ?
• Do all staff have the same rate, or are you expecting to look up each one for their rate. If so, this is a potential "show stopper" as relational queries are completely not Delegable.

[RamyEdrees · Accepted solution]

Hi  ,

First I would like to thank you for your time to reply to my message. Your questions actually made me change my approach and I have found a solution from a different way. I have gathered all the input value into a new SP List and managed to filter and sum them by Name, status and rates. Problem Solved 🙂

On the other hand I faced another problem which is the limitation of the number of Text columns in SP. I need way more than 255. I am currently rebuilding my app in Dataverse for Teams as it gives 1024 columns. Once again thank you for your reply.