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

Storing Date Values into variables

Hi everyone, I have this SharePoint list full of date values called 'Queue', and am storing the list into a collection called 'EarliestAval'. After that I store the latest date from each unique 'Title' value into a variable as shown in the code. I refer to these variables in another location in order to tell the user the earliest they can reserve a spot.

 

Refresh('Queue');
ClearCollect(EarliestAval,'Queue');
Set(Date1,First(Filter(EarliestAval,'Date Out'=First(Sort(Distinct(EarliestAval,'Date Out'),Result,Ascending)).Result,Title = "1").'Date Out').'Date Out');


Set(DateRB,First(Filter(EarliestAval, 'Date Out'=First(Sort(Distinct(EarliestAval,'Date Out'),Result,Ascending)).Result,Title = "Reference Bay").'Date Out').'Date Out');


Set(DateSL,First(Filter(EarliestAval,'Date Out'=First(Sort(Distinct(EarliestAval,'Date Out'),Result,Ascending)).Result,Title = "Service Lane").'Date Out').'Date Out');


Set(Date2,First(Filter(EarliestAval,Title = "2", 'Date Out'=First(Sort(Distinct(EarliestAval,'Date Out'),Result,Ascending)).Result).'Date Out').'Date Out');

All of this code seems identical, however only the variable 'Date1' actually stores info successfully, with all other variables being blank even if there is a match in the sharepoint list/collection. Help would be greatly appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
RandyHayes
Super User
Super User

@Nlsh 

With({_items: Sort(Queue, 'Date Out')},
    Set(Date1, LookUp(_items, Title="1").'Date Out');
    Set(DateRB, LookUp(_items, Title="Reference Bay").'Date Out');
    Set(DateSL, LookUp(_items, Title = "Service Lane").'Date Out');
    Set(Date2, LookUp(_items, Title = "2").'Date Out')
)
_____________________________________________________________________________________
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.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
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

3 REPLIES 3
RandyHayes
Super User
Super User

@Nlsh 

You have a lot of redundant and heavy-data interactive functions in that formula.

Please consider changing your Formula to the following:

With({_items: Sort(Queue, 'Date Out'),
    Set(Date1, LookUp(_items, Title="1").'Date Out');
    Set(DateRB, LookUp(_items, Title="Reference Bay").'Date Out');
    Set(DateSL, LookUp(_items, Title = "Service Lane").'Date Out');
    Set(Date2, LookUp(_items, Title = "2").'Date Out')
)

 

I hope this is helpful for you.

_____________________________________________________________________________________
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.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
Check out my PowerApps Videos too! And, follow me on Twitter @RandyHayes

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

When I paste it in, I get the following error 

Nlsh_0-1647299018251.png

 

RandyHayes
Super User
Super User

@Nlsh 

With({_items: Sort(Queue, 'Date Out')},
    Set(Date1, LookUp(_items, Title="1").'Date Out');
    Set(DateRB, LookUp(_items, Title="Reference Bay").'Date Out');
    Set(DateSL, LookUp(_items, Title = "Service Lane").'Date Out');
    Set(Date2, LookUp(_items, Title = "2").'Date Out')
)
_____________________________________________________________________________________
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.
NOTE: My normal response times will be Mon to Fri from 1 PM to 10 PM UTC (and lots of other times too!)
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
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.

Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on June 15, 2022 at 8am PDT.

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
Top Kudoed Authors
Users online (2,050)