cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
leslie_rivera
Resolver II
Resolver II

PowerApps, Collection, retreive data betwen 2 dates

I have a collection called "Codes" that is populated from a SharePoint list. In this collection there are 3 columns.

leslie_rivera_0-1630355998012.png

I want to send an email from the app that provides the correct code for the date they select. So If I select 9/15/21, I should get code 9513.

I tried :

leslie_rivera_2-1630356195347.png

 

I book 8/23, 8/24, 8/25, 8/26 I get 5555 then on 8/27 i get 7898.

 

I tried changing the days 6, 5 4 or 8, 9 .. but I either get the following week or the error of no code... anyone have any suggestions?

 

2 ACCEPTED SOLUTIONS

Accepted Solutions

@leslie_rivera ,

Please take a moment to look at the code and ask yourself why (this is a basic debugging skill you will need to learn). There is one operator not quite correct. Please see if you can see it before opening the below

Spoiler
Office365.SendEmailV2(
   User().Email, 
   "Building Access Code", 
   With(
      {
         wCode:
         LookUp(
            Codes,
            Start_x0020_Date <= StartDateTime &&
            End_x0020_Date >= StartDateTime
         ).Access_x0020_Code
      },
      If(
         !IsBlank(wCode),
         "See below building access code: <br>" & wCode & 
         "<br><br> This code is only valid Monday-Friday of the current week.",
         "No Code Found"
      )
   )
)

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.

View solution in original post

@WarrenBelz  After speaking with a colleague I figured out what the issue was. On the SharePoint list I have the Start and End column format settings to show Date only. What I didn't take into account is by default the time will have 12:00 AM. So the code was looking at Friday as if it was Saturday... Since the bldg codes are only active 8-5pm I have to adjust the time in the formula. Once I did that it worked perfectly!.

 

leslie_rivera_0-1630524457391.png

 

View solution in original post

9 REPLIES 9
WarrenBelz
Super User
Super User

Hi @leslie_rivera ,

Try this

LookUp(
   Codes,
   Start_x0020_Date >= StartDateTime &&
   End_x0020_Date < StartDateTime
).Access_x0020_Code

 

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.

No that didnt work.. I got the No code found message.

Hi @leslie_rivera ,

The filter is correct (think about it yourself - you are looking for the date to be between the start and end dates in the item) - I suspect your If log is not - try this

Office365.SendEmailV2(
   User().Email, 
   "Building Access Code", 
   With(
      {
         wCode:
         LookUp(
            Codes,
            Start_x0020_Date <= StartDateTime &&
            End_x0020_Date > StartDateTime
         ).Access_x0020_Code
      },
      If(
         !IsBlank(wCode),
         "See below building access code: <br>" & wCode & 
         "<br><br> This code is only valid Monday-Friday of the current week.",
         "No Code Found"
      )
   )
)

 

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.

leslie_rivera
Resolver II
Resolver II

Still getting No Code Found 😞

leslie_rivera_0-1630367017333.png

 

@leslie_rivera ,

Operators around the wrong way (please see amended post)

 

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.

leslie_rivera
Resolver II
Resolver II

No go 😞 It worked for Monday - Thursday then on Friday I get "No Code Found"

leslie_rivera_0-1630419768981.png

 

@leslie_rivera ,

Please take a moment to look at the code and ask yourself why (this is a basic debugging skill you will need to learn). There is one operator not quite correct. Please see if you can see it before opening the below

Spoiler
Office365.SendEmailV2(
   User().Email, 
   "Building Access Code", 
   With(
      {
         wCode:
         LookUp(
            Codes,
            Start_x0020_Date <= StartDateTime &&
            End_x0020_Date >= StartDateTime
         ).Access_x0020_Code
      },
      If(
         !IsBlank(wCode),
         "See below building access code: <br>" & wCode & 
         "<br><br> This code is only valid Monday-Friday of the current week.",
         "No Code Found"
      )
   )
)

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.

View solution in original post

@WarrenBelz  After speaking with a colleague I figured out what the issue was. On the SharePoint list I have the Start and End column format settings to show Date only. What I didn't take into account is by default the time will have 12:00 AM. So the code was looking at Friday as if it was Saturday... Since the bldg codes are only active 8-5pm I have to adjust the time in the formula. Once I did that it worked perfectly!.

 

leslie_rivera_0-1630524457391.png

 

View solution in original post

@WarrenBelz  After speaking with a colleague I figured out what the issue was. On the SharePoint list I have the Start and End column format settings to show Date only. What I didn't take into account is by default the time will have 12:00 AM. So the code was looking at Friday as if it was Saturday... Since the bldg codes are only active 8-5pm I have to adjust the time in the formula. Once I did that it worked perfectly!.

 

leslie_rivera_0-1630524457391.png

 

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!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

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