cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Julien2
Super User
Super User

Sort last record of the current user based on today's date

Hello everyone,

I want to sort in descending order the last entry of the current user signed in and based on today's date.

The data source that I am using is dataverse in my canvas app.

I have tried the below formula but it is always highlighted in red:
SortLastRecordIssue.png

 

Last(Sort(LookUp('Time Entries','User Name'.'Timesheet User' = varuserID).'Project Name','Time Entry ID',Descending))

 

The Time Entry ID is a field in the table Time Entries of type Autonumber.

Time Entry ID field.png
If I just use the below formula it is working fine. However, I need to do a lookup to add the conditions that should be validated before fetching the record and when I start with the LookUp logic it automatically throws the error.

The varUserID is a variable that I am using on app start and it has the right value.
LastSortWIthoutLookup.png

Can please someone provide the corrected formula to solve this issue? I am also stuck to check that if the entry is created on Today's date.

Any help is highly appreciated.

Best regards,
Julien

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Julien2 ,

You can also go this way (note the With() statement is only to keep it Delegable

With(
   {
      wNextDay:
      DateAdd(
         Today(),
         1,
         Days
      )
   },
   LookUp(      
      Sort(
         'Time Entries',
         'Time Entry ID',
         Descending
      ),
      'User Name'.'Timesheet User' = varuserID && 
      'Created On' >= Today() &&
      'Created On' < wNextDay
   ).'Project Name'
)

 

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.

Visit my blog Practical Power Apps

View solution in original post

3 REPLIES 3
WarrenBelz
Super User
Super User

Hi @Julien2 ,

Are you after this?

Last(
   Sort(
      Filter(
         'Time Entries',
         'User Name'.'Timesheet User' = varuserID
      ),
      'Time Entry ID',
      Descending
   ).'Project Name'
)

 

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.

Visit my blog Practical Power Apps

 

 

Hello @WarrenBelz ,

Thank you for providing the above formula.

I have added an extra condition to check if the record was created on Today's date.

Last(
   Sort(
      Filter(
         'Time Entries',
         'User Name'.'Timesheet User' = varuserID,'Created On' >= Today(),  
       'Created On' < DateAdd(Today(), 1, Days)
      ),
      'Time Entry ID',
      Descending
   ).'Project Name'
)

Why the LookUp is not working within the sort and last functions, knowing that I just need only 1 record from the table?
Example: First(Filter( can be replaced with LookUp but Last(Sort(LookUp it fails.

I look forward to your response.
Best regards,
Julien

Hi @Julien2 ,

You can also go this way (note the With() statement is only to keep it Delegable

With(
   {
      wNextDay:
      DateAdd(
         Today(),
         1,
         Days
      )
   },
   LookUp(      
      Sort(
         'Time Entries',
         'Time Entry ID',
         Descending
      ),
      'User Name'.'Timesheet User' = varuserID && 
      'Created On' >= Today() &&
      'Created On' < wNextDay
   ).'Project Name'
)

 

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.

Visit my blog Practical Power Apps

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Top Solution Authors
Top Kudoed Authors
Users online (2,151)