cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Lookup formula not returning correct value

I am performing a lookup on a datasource (SharePoint document library) to see if a record exists that has a specific column value tied to it. For example, my column name would be Category and I am checking to see if an item exists with a category value of "Products". I put a label on my screen to test and my lookup keeps returning "false". I have three items that exist in this library that are tied to my submission ID. Whenever I use "Products" or "Photo", the lookup returns 'false'. If I use "Poster", the lookup returns 'true'. The item with the category value of "Poster" is the first one that was submitted which tells me that the Lookup is stopping there. If I read this correctly, the Lookup should loop through all items until it finds my information. Here is the formula I'm using.

 

LookUp('SP Library',varSubmissionID=SubmissionID,Category="Products")

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Lookup formula not returning correct value

Hi @BigE ,

Based on the LookUp formula that you mentioned, I think there is something logic issue with it. The LookUp function finds the first record in a table that satisfies a formula.

 

So based on the LookUp formula you mentioned, it would search first record whose SubmissionID column value = varSubmissionID. If there are three records existed in your SP List with same SubmissionID value:

|SubmissionID | Category | ID |
| ----------  | ---------|----|
|     1001    | Poster   | 1  |
|     1001    | Products | 2  |
|     1001    | Photo    | 3  |

The LookUp function that you mentioned could only retrieve the first record in above table, because, you have specified the varSubmissionID =SubmissionID as filter condition. No matter how many records with same SubmissionID value in your SP List, it could only retrieve the first record with Category = "Poster", so your LookUp formula result would always return false.

 

As an fixed solution, please consider modify your formula as below (set Text property of a Label to following😞

If(
   IsBlank(LookUp('SP Library', varSubmissionID=SubmissionID && Category = "Products")),
   false,      // Record with specific Category value not exist
   true        // Record with specific Category vlaue exists
)

 

Please consider take a try with above solution, then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Highlighted
Resolver I
Resolver I

Re: Lookup formula not returning correct value

Lookup provides for multiple filtering options, comma delineated. Everything after the collection is a filter, so you are filtering on Category="Products" AND varSubmissionID=SubmissionID

 

Is that maybe the issue here?

Highlighted
Helper II
Helper II

Re: Lookup formula not returning correct value

@cchannonI don't believe this is the issue. This is a filtered lookup based on the logged in user which is what the varSubmissionID is. This ID matches the logged in users' submission in the main datasource with the SubmissionID of the document library.

Highlighted
Resolver I
Resolver I

Re: Lookup formula not returning correct value

OK, seems reasonable to me.

 

I would run some sanity checks because Lookup works exactly the way we have discussed; there is no hidden magic. If it is returning false, there is a reason it doesn't find a match and it is either the category or the submission id.

 

Try putting the submissionid filter on a gallery and display the names and categories. That is a quick, easy sanity check. If you can't find any "Photo" category items in the gallery, it is because of the submissionid. If you find them, double-check the spelling: is there a whitespace issue or something else hiding there that might cause them to not match ("Photo" versus " Photo" for example).

Highlighted
Helper II
Helper II

Re: Lookup formula not returning correct value

@cchannonI've checked again and again. I cannot find a reason why this is happening other than the Lookup is possibly only looking at the first record it finds and then it stops. This is not for a gallery control by the way. The ultimate goal for this lookup is to check that column value and to patch a record if that particular value exists.

Highlighted
Helper V
Helper V

Re: Lookup formula not returning correct value

The issue is LookUp doesn't allow multiple options.  The formula is LookUp(datasource,condition,result).  You just have two conditions.  I'd suggest filtering the data then doing a single condition.  An example would be something like...

 

Lookup(Filter(SPLibrary, Category = "Product"),varSubmissionID=SubmissionID,whateverfieldyouwant)

 

That or create a new field in sharepoint that combines the two columns you want to lookup together and perform a single lookup.  

Highlighted
Resolver I
Resolver I

Re: Lookup formula not returning correct value

Ah. There we go!

 

I was conflating topics before because of also commenting in a similar thread about Filter, which specifically provides for equivalent, comma delineated conditions. The real answer is in the space between what I commented and what @dyee4614  noted:

 

You can chain together operations on Lookup, similar to the way you do with Filter, BUT the chained operation is known as a REDUCTION FORMULA and is sepcifically designed to narrow the result to a single returned row. So, it seems that what you really need to do is NEST your Lookup function with an additional FILTER function so you can apply both criteria without accidentally forcing the return count to 1.

 

Great call, @dyee4614 - I would have missed this without your input!

Highlighted
Community Support
Community Support

Re: Lookup formula not returning correct value

Hi @BigE ,

Based on the LookUp formula that you mentioned, I think there is something logic issue with it. The LookUp function finds the first record in a table that satisfies a formula.

 

So based on the LookUp formula you mentioned, it would search first record whose SubmissionID column value = varSubmissionID. If there are three records existed in your SP List with same SubmissionID value:

|SubmissionID | Category | ID |
| ----------  | ---------|----|
|     1001    | Poster   | 1  |
|     1001    | Products | 2  |
|     1001    | Photo    | 3  |

The LookUp function that you mentioned could only retrieve the first record in above table, because, you have specified the varSubmissionID =SubmissionID as filter condition. No matter how many records with same SubmissionID value in your SP List, it could only retrieve the first record with Category = "Poster", so your LookUp formula result would always return false.

 

As an fixed solution, please consider modify your formula as below (set Text property of a Label to following😞

If(
   IsBlank(LookUp('SP Library', varSubmissionID=SubmissionID && Category = "Products")),
   false,      // Record with specific Category value not exist
   true        // Record with specific Category vlaue exists
)

 

Please consider take a try with above solution, then check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Find your favorite faces from the community presenting at the Power Platform Community Conference!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors
Users online (5,991)