cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BigE
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
v-xida-msft
Community Support
Community Support

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
cchannon
Continued Contributor
Continued Contributor

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?

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

cchannon
Continued Contributor
Continued Contributor

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

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

dyee4614
Helper V
Helper V

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.  

cchannon
Continued Contributor
Continued Contributor

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!

v-xida-msft
Community Support
Community Support

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
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (40,921)