Answered

New screen to view the hours filled in per day

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hello there!

I want my app users to be able to view back the amount of hours they filled in for each day. This is what I've already done:

Added a new screen where they can get to using a button. Whenever the screen is visible, I make a new collection that only takes their own tickets.

Code:

ClearCollect(
 UrenTerugKijken,
 Filter(
 UrenRegistratie,
 varcurrentuser = Werknemer
 )
);

The screen looks like this: https://gyazo.com/7fc87b9b27c716433ddc945610606af1

I have used the function Distinct to filter out the double dates & sorted the gallery based on the result. This did not sort it correctly though. As you can see the date 22/09/2020 is at the bottom when it should've been on the top.

Code:

Sort(
 Distinct(
 UrenTerugKijken,
 Datum
 ), Result)

What I now want is the following:

It needs to check the date and display the day of today in front of the date and then the sum of all of the hours they worked on that day, example; Friday - 18/09/2020 - 8,75 hours

To get the hours, we need to check in the collection named "UrenTerugKijken". Here are 2 columns that are important and we need to use. TijdTotaal & title. TijdTotaal is the hours they filled in per ticket. Title is the date of the ticket filled in. Picture of the collection: https://gyazo.com/63bd25a1f395bce3659269353fbe83a2

We need to make a formula that sums up all of the hours on the same day. On 18/09/2020 you can see there are 3 tickets. On the app gallery itself, it needs to display the following:

Thursday - 22/08/2020 - 5 hours
Tuesday - 15/09/2020 - 2 hours
Friday - 18/09/2020 - 9 hours

I want them to be able to look a month back only. So if they look today, they can still see a ticket of 18/08/2020 but not of 17/08/2020.

How can I accomplish this?

Thank you in advance for your help! Appreciate it!

Categories:

Building Power Apps

All responses (8)

Answers (1)

Sort by

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
@Anonymous

You might want to consider this approach.

On your Gallery, place the following formula on your Items property:

SortByColumns( AddColumns( GroupBy( AddColumns( Filter(UrenRegistratie, varcurrentuser = Werknemer) "dDatum", DateValue(Datum) ), "dDatum", "artikel" ), "gesamtstunden", Sum(artikel, TijdTotal) ), "dDatum", Descending )

Please note: Your columns name "TijdTot..." was truncated in your picture, so please correct that name in the formula above is it is inaccurate.

Now in your Gallery, place a label for the total hours for that day and set the Text property to ThisItem.gesamtstuden

You already have a label (it appears) for ThisItem.Dataum (if not add that also)

This should show you a list sorted by dates and with the totaled hours for each day.

I hope this is helpful for you.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hello there @RandyHayes

I've done it the following way:

I had to use the ForAll operator to get rid of the delegation error for the Sum operator. This works perfectly. I appreciate you helping me.

ThisItem.Result & Switch(Weekday( Date( Value(Right(ThisItem.Result ;4)); Value(Mid(ThisItem.Result ;4;2)); Value(Left(ThisItem.Result ;2)) ) ); 1; "Zondag"; 2 ; "Maandag"; 3 ; "Dinsdag"; 4 ;"Woensdag"; 5 ; "Donderdag" ; 6 ; "Vrijdag" ; 7 ; "Zaterdag") & Sum( ForAll( Filter( UrenRegistratie; varcurrentuser = Werknemer && Datum = ThisItem.Result) ; TijdTotaal);Value ) & " uur gewerkt"

The only thing that is not correct is the way it sorts on the date ( https://gyazo.com/768bd196d338267594a44afef1d14738 )

How can I make it sort correctly?
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
@Anonymous

The formula I provided for you should resolve all those issues. You are still trying to do this with a Distinct operation which means that you then have to ForAll the other records though yet another filter.

Plus, as you notice, the Sum function will not work directly on the datasource without delegation issues. However again, the formula I provided you will eliminate the delegation issue because the Sum is performed on the filtered and grouped data only, not the entire datasource.

There is no need to duplicate the data gathering. Here is the formula again (with corrected semicolons as I forgot to change them in the original):

SortByColumns( AddColumns( GroupBy( AddColumns( Filter(UrenRegistratie; varcurrentuser = Werknemer) "dDatum"; DateValue(Datum) ); "dDatum"; "artikel" ); "gesamtstunden"; Sum(artikel; TijdTotal) ); "dDatum"; Descending )

And this will sort as you want.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
@RandyHayes

There is no delegation error with the code I have right now. Everything works perfectly, as it needs to be. I turned the result into a datevalue and now it's sorting correctly! There is nothing wrong with my code right now, I think.

I get a lot of errors trying your code.

My full code, works perfectly now;

Gallery code (using distinct so I don't have the dates double):
Sort( Distinct( UrenTerugKijken; Datum ); DateValue(Result); Descending)

Textfield in the gallery;

ThisItem.Result & Switch(Weekday( Date( Value(Right(ThisItem.Result ;4)); Value(Mid(ThisItem.Result ;4;2)); Value(Left(ThisItem.Result ;2)) ) ); 1; " - Zondag - "; 2 ; " - Maandag - "; 3 ; " - Dinsdag - "; 4 ;" - Woensdag - "; 5 ; " - Donderdag - " ; 6 ; " - Vrijdag - " ; 7 ; " - Zaterdag - ") & Sum( ForAll( Filter( UrenRegistratie; varcurrentuser = Werknemer && Datum = ThisItem.Result) ; TijdTotaal);Value ) & " uur gewerkt"
Verified answer

RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!
@Anonymous

The formula I provided you would be a better choice as it does not involve multiple data gathering to the datasource - and it works perfectly, and involves no other collections. If not in your app, then it should be investigate as to why not and corrected.

What I don't understand from your original Formulas that you posted is, some were delimited by semicolons and some were by commas. So, not sure what your actual input language is.

So, assuming semicolons, your Items property should be:

SortByColumns( AddColumns( GroupBy( AddColumns( Filter(UrenRegistratie; varcurrentuser = Werknemer); "dDatum"; DateValue(Datum) ); "dDatum"; "artikel" ); "gesamtstunden"; Sum(artikel; TijdTotal) ); "dDatum"; Descending )

And your Text property in the Gallery should be:

Text(ThisItem.dDatum; ShortDate) & " - " & Text(ThisItem.dDatum; "dddd") & ThisItem.gesamtstuden & " uur gewerkt"

Please try it out.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

This one has worked indeed @RandyHayes

If your one is better, I will just use yours instead! Thank you. Not sure if it would be a problem the way I did it, as it still loaded very quickly.
RandyHayes 76,299 Super User 2024 Season 1 on at

Like
a
(1)

Report
Copy link

Link copied!

@Anonymous

Yes, it should have worked the first time - it looks like (as I retyped it in the last post) I missed a semicolon in the formula - oops!

Your way definitely works, as you saw, but it is data-heavy. Meaning that you are first hitting the data for the Item property and then hitting the data again in a (very low performant) ForAll. May not be an issue with a starting datasource, but as that grows, the performance will suffer.

Why I persisted was that the GroupBy on the Items property hits the data once and does all of the summing and conversions you need all in one shot.
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hello there @RandyHayes

I am having a new delegation issue. Are maybe to help me with this too? Here is the link to the question;

https://powerusers.microsoft.com/t5/Building-Power-Apps/Getting-rid-of-delegation-error-with-sum-using-addcolumns/m-p/721245#M230797

It would be really awesome!