cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Prodigy
Post Prodigy

New screen to view the hours filled in per day

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

Re: New screen to view the hours filled in per day

@TimoMigchielsen 

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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

8 REPLIES 8
Highlighted
Super User III
Super User III

Re: New screen to view the hours filled in per day

@TimoMigchielsen 

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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Post Prodigy
Post Prodigy

Re: New screen to view the hours filled in per day

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?

 

Highlighted
Super User III
Super User III

Re: New screen to view the hours filled in per day

@TimoMigchielsen 

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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Post Prodigy
Post Prodigy

Re: New screen to view the hours filled in per day

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

 

 

Highlighted
Super User III
Super User III

Re: New screen to view the hours filled in per day

@TimoMigchielsen 

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.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.

View solution in original post

Highlighted
Post Prodigy
Post Prodigy

Re: New screen to view the hours filled in per day

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.

Highlighted
Super User III
Super User III

Re: New screen to view the hours filled in per day

@TimoMigchielsen 

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.

 

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up. Solved your problem? - Click on Accept as Solution. Others seeking the same answers will be happy you did.
Highlighted
Post Prodigy
Post Prodigy

Re: New screen to view the hours filled in per day

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

 

It would be really awesome! 

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Top Solution Authors
Top Kudoed Authors
Users online (5,152)