Unanswered

Can't pick up the latest record from my sharepoint list on power apps

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi! My power apps formula can’t pick up the latest record from my sharepoint list. Here’s my context:

When an individual submits the form on power apps, the information will be recorded in my Sharepoint list. Hence, there can be multiple entries on sharepoint list by the same individual.

So my question is: How should my formula on power apps look such that it will pick up the latest record by the individual? My current formula looks like this:

If(LookUp(SharepointList, ID = DataCardValue16.Text, Status) = "Reserved" And DataCardValue22.SelectedDate - LookUp(SharepointList, ID = DataCardValue16.Text, DateofVisit)<60, "You can only make a booking once in every 2 months.","")

Thank you!

Categories:

Building Power Apps

All responses (3)

Answers (0)

Sort by

BCLS776 8,994 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
This version may work better:
If( LookUp(SharepointList, ID = DataCardValue16.Text, Status) = "Reserved" && DateDiff(DataCardValue22.SelectedDate, LookUp(SharepointList, ID = DataCardValue16.Text, DateofVisit),Days)<60, "You can only make a booking once in every 2 months.", "" )
This only works if the DateofVisit column is properly formatted as a date/time value and the selected date is the same or later than the last booking.

Hope that helps,
Bryan
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you for your reply! I further refined my formula as I needed to check through 2 columns on my Sharepoint list. Also, in my sharepoint list it will contain multiple entries from the same individual. However, I have a problem with it:

My current formula can’t fulfill the condition to book once every 2 months. For eg: I first make a booking for 1 Jan 2022 and make a second booking for 1 Oct 2021. If I make a third booking for 31 Dec 2021, instead of stopping this reservation from happening since my next booking is on 1 Jan 2022, my formula would allow this reservation to be submitted instead. Here's my current formula:
If(And((LookUp(Sort(SharepointList,DateofVisit,Descending), ID=DataCardValue16.Text,Status="Reserved") And (LookUp(Sort(SharepointList,DateofVisit,Descending), ID=DataCardValue16.Text,DateofVisit)-DataCardValue22.SelectedDate)<=60),((LookUp(Sort(SharepointList,DateofVisit,Descending), ID=DataCardValue16.Text,Status)="Reserved") And (DataCardValue22.SelectedDate-(LookUp(Sort(SharepointList,DateofVisit,Descending), ID=DataCardValue16.Text,DateofVisit))<=60))),"You can only make a booking once in every 2 months.",""))

Do you have an idea on how should I refine it? Thank you!
BCLS776 8,994 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
OK, let's simplify things a bit using a With() to reduce repeating statements, Abs() to simplify the logic, and DateDiff() to correctly subtract date values from one another:

With({ aSort: Sort(SharepointList,DateofVisit,Descending)}, If( LookUp(aSort, ID=DataCardValue16.Text,Status="Reserved") && Abs( DateDiff( LookUp(aSort, ID=DataCardValue16.Text,DateofVisit), DataCardValue22.SelectedDate, Days ) ) <= 60, "You can only make a booking once in every 2 months.", "" ) )
This is free-typed, so the potential for some syntax issues, but this should be closer to working for you.

Bryan