cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
j_y
Frequent Visitor

Picking up the correct date from Sharepoint List onto Power Apps

Hello! I need some assistance on my formula so that the correct date can be picked up from my Sharepoint List onto Power Apps.

 

Here’s my context: I am trying to create an app for ticket reservations, and each person can only reserve the ticket once every 2 months. Eg: If I reserve the ticket today (17/09), I can reserve a ticket that is dated 2 months from today (17/11 onwards). My formula on Power Apps would check through 2 columns from my Sharepoint List: Status (= Reserved) and Date of Visit.

Here’s my problem:  My current formula can’t fulfil 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 because it is more than 2 months from 1 Oct 2021.

 

How should I go about refining my formula? Here's my 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.",""))

 

Have tried multiple ways but am still stuck at it. Appreciate your help, thank you!

3 REPLIES 3
WarrenBelz
Super User
Super User

Hi @j_y ,

You might try something like this - although I am wondering how the ID which is numeric is equal to the value in DataCard16.Text

With(
   {
      wList:
      Filter(
         SharepointList,
         ID=DataCardValue16.Text &&
         Status="Reserved"
      ),
      wDate:DataCardValue22.SelectedDate,
      wDate1:DataCardValue22.SelectedDate - 60,
      wDate2:DataCardValue22.SelectedDate + 60 
   },
   If(
      !IsBlank(
         LookUp(
            wList:
            DateofVisit =< wDate &&
            DateofVisit > wDate1
         ).DateofVisit
      ) ||
      !IsBlank(
         LookUp(
            wList:
            DateofVisit >= wDate &&
            DateofVisit < wDate2
         ).DateofVisit
      ),
      "You can only make a booking once in every 2 months.",
      ""
   )
)

 

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.

 

j_y
Frequent Visitor

The ID here is actually the Pass ID (alpha-numeric) that the individual will input, hence it is a text instead. I tried the formula, but it doesn't work.. I dont really get the LookUp formula, and I have no idea how to go about using With formula. Is there a way that I can use a For All formula? 

 

Also, I am aware that using Sort is the main reason that is causing my formula to fail, but I'm not sure how should I go about checking all dates on the sharepoint list..

@j_y ,

I simply answered your posted query Picking up the correct date from Sharepoint List onto Power Apps

"does not work" unfortunately will not let me assist you - is there an error (if so what) or does it not produce the desired result. ForAll() is not something relevant here.

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

PA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

This training provides practical hands-on experience in creating Power Apps solutions in a full-day of instructor-led App creation workshop.

PA.JPG

New Release Planning Portal (Preview)

Check out our new release planning portal, an interactive way to plan and prepare for upcoming features in Power Platform.

Top Solution Authors
Users online (1,088)