cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JamFestival
Helper I
Helper I

LookUp: Applying a condition (IF statement?) to the returned result

I need to LookUp on a SharePoint list to return the Title of the most recent record for a specific user, then check if a Yes/No 'Complete' column for that most recent record in the SharePoint list is false, if it is, return its value, but if it's true, return a blank. If that user has other records not marked complete, but the most recent record isn't, it should return a blank.

"Return (Title) of (most recent record with matching ID) IF ('Complete' = false), ELSE IF (most recent record with matching ID 'Complete' = true),  Title =blank"

 

I currently have a working LookUp to store the result in a variable, but it's simply returning the most recent record that matches the user AND is marked complete. It needs to return only the most recent record for that user, check if it's not complete, and if it is, return its value, otherwise, return a blank:

Set(VarPreviousRecord,(LookUp(Sort(ResponsesNEW,DateLastAcccessed,Ascending), myID = unnID && COMPLETE = false,Title)))

i.e.

Set(New Variable,(LookUp(Sort(SharePoint List,SharePoint Date Field,Ascending), PowerApps Variable = SharePoint ID Field && SharePoint Yes/No Field = false,SP Title)))

Ideally, I would also like to populate a boolean global variable with whether the LookUp returned a result. 

Any help would be much appreciated 😀

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @JamFestival ,

In that case

Set(
   NewVariable,
   LookUp(
      Sort(
         ResponsesNEW,
         DateLastAcccessed,
         Ascending
      ), 
      myID = unnID 
   ).COMPLETE
)

This will return the value of the COMPLETE column if the latest record form the user, so will set the Variable to either true or false.

 

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.

Visit my blog Practical Power Apps

 

View solution in original post

4 REPLIES 4
WarrenBelz
Super User
Super User

Hi @JamFestival ,

Your first code is the correct syntax, but you need Descending to get the latest record.

Set(
   VarPreviousRecord,
   LookUp(
      Sort(
         ResponsesNEW,
         DateLastAcccessed,
         Descending
      ), 
      myID = unnID && 
      !COMPLETE
   ).Title
)

If you want a Variable for true it a result is returned

Set(
   VarPreviousFound,
   !IsBlank(
      LookUp(
         Sort(
            ResponsesNEW,
            DateLastAcccessed,
            Ascending
         ), 
         myID = unnID && 
         !COMPLETE
      ).Title
   )
)

 

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.

Visit my blog Practical Power Apps

JamFestival
Helper I
Helper I

Thanks very much for your advice. It is very helpful.


Unfortunately, it's returning the latest record of that user that's not complete. The '&&' in mine is wrong, as it's saying "Return the most recent record for that user that's not complete", as opposed to "Return the most recent record for that user, check if the complete column is false, and if so, return it, if not, do nothing"

 

To apply some context, my app is a skills survey. If people have partially completed the survey on their last visit, They can choose whether to resume or start afresh, creating a new record, but if their previous visit was completed, the app assumes they're redoing it and creates a new record. All of the functionality for this in the app works, apart from this LookUp.

 

Here is the scenario:

TitleUserDateCOMPLETE
ABCD_1ABCD1-1-2022N
BCDE_1BCDE1-2-2022N
ABCD_2ABCD1-3-2022Y

The user is ABCD. Their most recent record is the only one I need to look at. Here, it's 'complete', so I need a blank. 

 

Currently, it would return 'ABCD_1', but it shouldn't, as that's not the most recent record.

 

TitleUserDateCOMPLETE
ABCD_1ABCD1-1-2022N
BCDE_1BCDE1-2-2022N
ABCD_2ABCD1-3-2022N

Here, their most recent record is not complete, so I need it to return 'ABCD_2'

Hi @JamFestival ,

In that case

Set(
   NewVariable,
   LookUp(
      Sort(
         ResponsesNEW,
         DateLastAcccessed,
         Ascending
      ), 
      myID = unnID 
   ).COMPLETE
)

This will return the value of the COMPLETE column if the latest record form the user, so will set the Variable to either true or false.

 

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.

Visit my blog Practical Power Apps

 

JamFestival
Helper I
Helper I

Brilliant!

Thank so much for your help 🙂

Helpful resources

Announcements
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

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.

Users online (3,161)