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

[leslie_rivera]

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?

[notj]

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)

[leslie_rivera]

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]

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]

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?

[PriyankaGeethik]

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 ? 

[leslie_rivera]

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:

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:

@RandyHayesanyway you can help me with above formula?

[RandyHayes]

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

[leslie_rivera]

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

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]

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