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?

[leslie_rivera]

@RandyHayes  not sure if putting that in the body of the email would work

Maybe I get the value I need populated in some other field, then provide that field information in the email?

[RandyHayes · Accepted solution]

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

[leslie_rivera · Accepted solution]

@RandyHayesHallelujah it worked!!

 OnSelect of my submit button:

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

[RandyHayes]

@leslie_rivera 

Happy to help!

[leslie_rivera]

@RandyHayesran into an issue.. the formula works if the user books for today (same day), but if the user pics a day in the future then it doesnt pull the right code. 

On the landing screen of the app onVisibile I have Set(DateSelected, Today());

Can I use that variable "DateSelected" in the formula? Basically how can I adjust the date to whatever day the user picks?

[RandyHayes]

@leslie_rivera 

Why are you setting a variable??  If the user is selecting a date, then you need to reference the date selection.

So, if you have a datepicker or something for selecting the date, but you just need to reference that...

With({_accessKey:
    Coalesce(
        LookUp(Codes,
            End_x0020_Date >= yourDatePicker.SelectedDate &&
            End_x0020_Date < DateAdd(yourDatePicker.SelectedDate,7,Days),
            Access_x0020_Code
        ),
        "No code found for " & Text(Today(), ShortDate)
    )
    },

    Office365.SendEmailV2(
        "lriver@westmoreland.com",
        "Building Access Codes", 
        "See below access code: <br>" & _accessKey & "<br>This code is only valid for Monday - Friday."
    )
)

Note: I only separated the _accessKey out of the sendemail function for clarity in my response.  

You will note the reference to the datepicker...just fill in with the correct name.

[leslie_rivera]

@RandyHayesIDK if you remember this app but its using galleries and buttons. I am trying to figure out what to use..

[RandyHayes]

@leslie_rivera 

Yes...feels like it was about 1000 apps ago since I looked over that one.

Take a look at your Save action and see how it is getting the selected date value...you can just use that.

[leslie_rivera]

@RandyHayes This is what I see in the Save button

[leslie_rivera]

@RandyHayes  .. I got it .. it was 'StartDateTime' .. works perfectly now! Thanks!!