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

Get the time back from last filled in ticket.

Hello there! 

I have a time picker (I use 4 dropdowns for this) https://gyazo.com/c560a3ab9869a24df2fc1f4b239616a5

Now whenever the editmode is new, we need to do the following:

Database lay-out: https://gyazo.com/33df7a1969c3f7127026060e5410beb4

1. Filter it on the current user that is using the app. We will do this with their fullname. I have set a variabele named varcurrentuser to the users fullname. We can use this variabele to filter. The fullname of varcurrentuser should be equal to the column "Werknemer" 

2. After that we need to check if there are any tickets on the day of today (On the screenshot of the lay-out for the database this is 26/08/2020). If there are, take the latest filled in TijdEind. This column is not shown in the screenshot of the database because it doesn't fit, here is a screenshot: https://gyazo.com/e4fe4c2b214c31fe766157687c65c037

3. Example: 

I made 2 tickets, 1 on 26/08/2020 and 1 on 25/8/2020 (https://gyazo.com/895a8eb463407f6172bd954e53a66e41)
Here are the times: https://gyazo.com/b651e8716e3c1cb9ac47c8346e641e86

If today is 26/08/2020, it should take the 15:00 and fill it in all of these 4 dropdownboxes. If its the next day (27/08/2020) and there are no tickets, the default should be "00". 

Here is the code I am using right now. This just sets the default value to "00", if in editmode, it is the first 2 numbers of tijdbegin. If the mode is not new, this code should stay (The left function): 

 

If(
    EditForm1.Mode = FormMode.New;
    "00";


    
    Left(
        ThisItem.TijdBegin;
        2
    )
)

 

 

We might be able to use the code I have used to filter my gallery on the users full name & current day

Filter(
            UrenRegistratie;
            Datum = Text(
                Today ();
                "dd/mm/yyyyy"
            ) And varcurrentuser = Werknemer
        )


How can I do this?

2 ACCEPTED SOLUTIONS

Accepted Solutions

Hi @TimoMigchielsen 

If(
  IsEmpty(Filter(UrenRegistratie; Datum = Today(); Werknemer = varcurrentuser ));
  "00";
  Left(
    First(
      Sort(
        Filter(
          UrenRegistratie,
          Datum = Today();  //if Datum is a date format in your source, there should be no need make a string of it
          Werknemer = varcurrentuser 
        );
        TijdEind;
        Descending
      )
    ).TijdEind;
    2
  )
)

This should do it then. Glad I could be of help

View solution in original post

Hi @TimoMigchielsen 

Maybe you can try it like next. Go over all conditions in order of priority and return which one is true.

  1. check if edit form is in edit mode, if true => return TijdBegin, if not go next step
  2. are there no matching items in the UrenRegistratie, if true => return "00", if not go next step
  3. Get the last item from UrenRegistratie. This one is default and will always run if the above steps are not true
Switch(
  true;
  EditForm1.Mode=FormMode.Edit; //notice that I replaced 'New' by 'Edit'
  "01441"; 
  IsEmpty(Filter(UrenRegistratie; Datum = Text(Today();"dd/mm/yyyy"); Werknemer = varcurrentuser ));
  "00";
  Left(
    First(
      Sort(
        Filter(
          UrenRegistratie;
          Datum = Text(Today();"dd/mm/yyyy"); 
          Werknemer = varcurrentuser 
        );
        TijdEind;
        Descending
      )
    ).TijdEind; 2
  )
)

 I hope this helps.

View solution in original post

7 REPLIES 7
SkiDK
Solution Sage
Solution Sage

Hi @TimoMigchielsen 

To get the last TijdEind from all tickets assigned to the employee, you could use something like this:

 

 

If(
  EditForm1.Mode = FormMode.New || IsEmpty(Filter(UrenRegistratie; Datum = Today(); Werknemer = varcurrentuser ));
  "00";
  Left(
    First(
      Sort(
        Filter(
          UrenRegistratie,
          Datum = Today();  //if Datum is a date format in your source, there should be no need make a string of it
          Werknemer = varcurrentuser 
        );
        TijdEind;
        Descending
      )
    ).TijdEind;
    2
  )
)

 

 

 But I kind of wonder, what is the format of TijdEind? Is it a string? If it's a string, then you can use the Left() function as above. If it's a time format, you'll have to first change it to a string in the correct format and then take the 2 left characters.

Hope this helps!

@SkiDK  Thank you so much for your help! This is exactly what I needed! Although I made a small mistake. I do not need the value to be "00" if the form is new. It needs to be "00" when there are no tickets from this day. Are you maybe able to change this? @SkiDK 

Hi @TimoMigchielsen 

If(
  IsEmpty(Filter(UrenRegistratie; Datum = Today(); Werknemer = varcurrentuser ));
  "00";
  Left(
    First(
      Sort(
        Filter(
          UrenRegistratie,
          Datum = Today();  //if Datum is a date format in your source, there should be no need make a string of it
          Werknemer = varcurrentuser 
        );
        TijdEind;
        Descending
      )
    ).TijdEind;
    2
  )
)

This should do it then. Glad I could be of help

View solution in original post

Thank you! @SkiDK 

How can I do the same but with another textfield? For example the project number. Instead of the Left() function, we can just put the last filled in Projectnumber of that day 

Hi @TimoMigchielsen 

I suppose that the latest filled in project number isn't always necessarily the highest number, so we can't sort on that column. So it kind of depends on how you define which is the last project number of the day.

  1. The last project number is the item that was last created. Then I'd sort on the time the entries are created, and if we take the first one we'll have the last created item. We can sort on 'Created' for doing this since you're using a SharePoint list. It's a default column.
If(
  IsEmpty(Filter(UrenRegistratie; Datum = Today(); Werknemer = varcurrentuser ));
  "00";
  First(
    Sort(
      Filter(
        UrenRegistratie,
        Datum = Today();  //if Datum is a date format in your source, there should be no need make a string of it
        Werknemer = varcurrentuser 
      );
      Created;
      Descending
    )
  ).ProjectNummer
)

2. The item with the latest Eindtijd counts as the last item from which we should get the project number. Then it's basically the same formula as before without the left(), only we select a different property to show

If(
  IsEmpty(Filter(UrenRegistratie; Datum = Today(); Werknemer = varcurrentuser ));
  "00";
  First(
    Sort(
      Filter(
        UrenRegistratie,
        Datum = Today();  //if Datum is a date format in your source, there should be no need make a string of it
        Werknemer = varcurrentuser 
      );
      TijdEind;
      Descending
    )
  ).ProjectNummer
)

 It kinda depends on how you define the last item.  

I got another problem @SkiDK 

Now when I edit a ticket, it becomes 00 too, how can I fix this? (It should be Left(Tijdbegin;2) I tried to test it using this code but it did not work: 

(The "01441" would be replaced with TijdBegin)

 

 

 

If(EditForm1.Mode=FormMode.New;
If(
  IsEmpty(Filter(UrenRegistratie; Datum = Text(Today();"dd/mm/yyyy"); Werknemer = varcurrentuser ));
  "00";
  Left(
    First(
      Sort(
        Filter(
          UrenRegistratie;
          Datum = Text(Today();"dd/mm/yyyy"); 
          Werknemer = varcurrentuser 
        );
        TijdEind;
        Descending
      )
    ).TijdEind; 2
  )
); "01441")

 

 

 

Hi @TimoMigchielsen 

Maybe you can try it like next. Go over all conditions in order of priority and return which one is true.

  1. check if edit form is in edit mode, if true => return TijdBegin, if not go next step
  2. are there no matching items in the UrenRegistratie, if true => return "00", if not go next step
  3. Get the last item from UrenRegistratie. This one is default and will always run if the above steps are not true
Switch(
  true;
  EditForm1.Mode=FormMode.Edit; //notice that I replaced 'New' by 'Edit'
  "01441"; 
  IsEmpty(Filter(UrenRegistratie; Datum = Text(Today();"dd/mm/yyyy"); Werknemer = varcurrentuser ));
  "00";
  Left(
    First(
      Sort(
        Filter(
          UrenRegistratie;
          Datum = Text(Today();"dd/mm/yyyy"); 
          Werknemer = varcurrentuser 
        );
        TijdEind;
        Descending
      )
    ).TijdEind; 2
  )
)

 I hope this helps.

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

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.

Users online (1,386)