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

PowerApps: Build then filter collection to retrieve one value to put in an email

I have a powerapp for booking rooms. I created a collection that pulls from a SharePoint list that is maintained by my local tech. The collection includes access codes to the building. The columns are: Start Date / End Date / Access Code.

 

I need to be able to filter that collection to pull out the current access code, then include that code in an email to the user.

 

I was able to create the collection but I am stuck after that. Not sure how to filter to just pull the code that falls between the start and end date. Any suggestions?

3 ACCEPTED SOLUTIONS

Accepted Solutions
RandyHayes
Super User
Super User

@leslie_rivera 

It will if you put it in properly...your second line of your formula should be:

  "See below access code: <br>" &

And then at the end of the Coalesce statement closing paren, you should have a & "<br>....more text here"

Fill in that part as you need.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

View solution in original post

leslie_rivera
Resolver II
Resolver II

@RandyHayesHallelujah it worked!!

 OnSelect of my submit button:

leslie_rivera_0-1629911104941.png

 

Now I can edit the email and change the from .. it all worked. Thanks so much for your help!!!

View solution in original post

leslie_rivera
Resolver II
Resolver II

SOLUTION:

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-1630524891289.png

 

Complete formula:

 

leslie_rivera_1-1630524971084.png

 

View solution in original post

21 REPLIES 21
notj
Post Prodigy
Post Prodigy

The user selects the start/end date, and then you want the access code that falls between those dates? You would probably do a Filter using greater than and less than operators.

 

This is just a rough example, but something like this should work:

Filter(Rooms, StartDate>lblStartDate.text, EndDate<lblEndDate.text)

The list is pre populated with Start and End date of the access code. So we can provide the correct access code I want to be able automatically filter. so I log in today and the filter gives me the appropriate code. I want to then in the app email the user the right code.

notj
Post Prodigy
Post Prodigy

Change lblStartDate and lblEndDate in my example to be whatever values you need to filter on then. 

Like if you wanted to filter on Today, use the function Today().

Now, Today, and IsToday functions in Power Apps - Power Apps | Microsoft Docs

leslie_rivera
Resolver II
Resolver II

I tried this : Filter(Codes,
End_x0020_Date >= Today(),
End_x0020_Date < DateAdd(Today(),7,Days))

 

And it shows in the gallery .. now how do I pull value from the Code column to put in an email?

PG_WorXz10
Resident Rockstar
Resident Rockstar

Hi @leslie_rivera ,

 

How you are planning to trigger emails to the users by any button click inside the gallery or all codes to the users or something else ? 

If this post was helpful or you need more help please consider giving Thumbs Up and Tag me in your reply I'll be happy to help. If this post helped you solve your issue please click Accept as solution and provide Thumbs Up. This will help others find it more readily.

When the user books the room in the app (submit button) I plan on including an email to the user providing the current access code which will be based on the start and date column. Example below:

 

leslie_rivera_0-1629900740283.png

 

So If I book today (8-25-21), the email should provide the access code 14214.

 

I tried to put this in the Visible field of a label

LookUp(Codes,Access_x0020_Code = Filter(Codes,
End_x0020_Date >= Today(),
End_x0020_Date < DateAdd(Today(),7,Days)),Access_x0020_Code)

 

But got this error:

leslie_rivera_1-1629906912985.png

 

@RandyHayesanyway you can help me with above formula?

RandyHayes
Super User
Super User

@leslie_rivera 

If you're trying to change the Visible property of the control...it is a true/false value it wants.  You are trying to look up an actual value, which will not work.

 

What is it you are trying to do with the Visible property of the Label and what is it you want in the label?  I assumed you wanted an email based on your post?

 

Please clarify.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!
leslie_rivera
Resolver II
Resolver II

@RandyHayes I wasnt sure what to use .. I was using a label to test and  see if I can pull the data. What I need is when user click on a submit button an email is sent to the user providing the current access code which will be based on the start and date column which will pull from a collection I have called Codes. Example below:

 

leslie_rivera_0-1629908944668.png

 

So If I book today (8-25-21), the email should provide the access code 14214.

 

Not sure how to build the formula to pull the right code.

RandyHayes
Super User
Super User

@leslie_rivera 

So, in your email, you can simply supply this formula :  

Coalesce(
    LookUp(Codes,
        End_x0020_Date >= Today() &&
        End_x0020_Date < DateAdd(Today(),7,Days),
        Access_x0020_Code
    ),
    "No code found for " & Text(Today(), ShortDate)
)

 

The above formula adds a Coalesce to provide a default value if none is found in the lookup.

_____________________________________________________________________________________
Digging it? - Click on the Thumbs Up below. Solved your problem? - Click on Accept as Solution below. Others seeking the same answers will be happy you did.
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

Really want to show your appreciation? Buy Me A Cup Of Coffee!

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 (3,007)