LookUp formula not working

(0) Share

Report

Posted on by aah

126

I have a formula that needs to LookUp into multiple SharePoint lists to get the value 'Department Name'.

This is the formula I am using and I can't work out why it's not working.

Any help on this issue would be great.

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Pstork1 68,717 Most Valuable Professional on at

Like (0)

Report
Try putting the Department ID column inside the lookups as the third optional parameter. That will return just the column value instead of a record.

Lookup(Departments, 'Department ID' = Lookup(People, 'Employee ID' = ThisItem.'Employee ID','Department ID'),'Department Name')

Was this reply helpful? Yes No
aah 126 on at

Like (0)

Report

@Pstork1 I've copied that across and the only error it's giving me is a delegation warning which should be one as it's only for a small data set. Any ideas?

Was this reply helpful? Yes No
iAm_ManCat 18,228 Most Valuable Professional on at

Like (1)

Report
Hi!

So, first of all we should find a way to avoid nesting those lookups as they can cause excessive network traffic - can I assume that your Departments list is not a huge list? less than 2000 items? If Yes, we should collect that into a local collection during the App's OnStart or the first screen's OnVisible, like this:

ClearCollect(colDepartments, Departments);

This means that when our gallery is doing a lookup, it is looking at a local collection for each item and not doing multiple network lookups to the same list for each row.

Then your DepartmentName label can be the following, as I am guessing the error - if you hover over the red line on the equals - is that its a type mismatch, cannot compare type text to number or something like that, so assuming the People list has a Department ID of type Text rather than number:

LookUp(colDepartments, 'Department ID' = Value(LookUp(People, 'Employee ID'=ThisItem.'Employee ID').'Department ID') ).'Department Name'

Otherwise if it's the other way around and your Departments has a Department ID column of type Text:

LookUp(colDepartments, Value('Department ID') = LookUp(People, 'Employee ID'=ThisItem.'Employee ID').'Department ID' ).'Department Name'

Was this reply helpful? Yes No
aah 126 on at

Like (1)

Report

@iAm_ManCat Thanks for this reply. Unfortunately, I'm still getting errors.

- So yes my Departments list is less than 2000 items (much less).
- Both my Department & People lists have the Department ID as a choice column (should this be changed to a number column?).
- The only error that I'm getting now off of that first formula is on the first "="

Was this reply helpful? Yes No
Verified answer

iAm_ManCat 18,228 Most Valuable Professional on at

Like (1)

Report
If you can change the column to type number at this point I would heavily advise it.

We can alter the formula to work with choices like this: (but its easier to keep track of and filter/etc by number/text)

LookUp(colDepartments, 'Department ID'.Value = LookUp(People, 'Employee ID'=ThisItem.'Employee ID').'Department ID'.Value ).'Department Name'

Was this reply helpful? Yes No
aah 126 on at

Like (1)

Report

@iAm_ManCat This has fixed the problem, thank you! 😁

Was this reply helpful? Yes No
Pstork1 68,717 Most Valuable Professional on at

Like (0)

Report

Delegation warnings are just warnings. If its a small data set you can ignore it, The error with the equals suggests that the Department ID field in People is an object and not a simple value. So as @iAm_ManCat points out you need a .Value after it to get the value you are comparing to.

Was this reply helpful? Yes No