Answered

Lookup formula not returning correct value

(0) Share

Report

Posted on by BigE

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")

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Sort by

cchannon 4,702 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

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?

Was this reply helpful? Yes No
BigE 81 on at

Like
a
(0)

Report
Copy link

Link copied!

@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.

Was this reply helpful? Yes No
cchannon 4,702 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

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).

Was this reply helpful? Yes No
BigE 81 on at

Like
a
(0)

Report
Copy link

Link copied!

@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.

Was this reply helpful? Yes No
dyee4614 283 on at

Like
a
(1)

Report
Copy link

Link copied!

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.

Was this reply helpful? Yes No
cchannon 4,702 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

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!

Was this reply helpful? Yes No
Verified answer

v-xida-msft Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
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,

Was this reply helpful? Yes No