Answered

Having trouble looking up data from a different Sharepoint list

(0) Share

Report

Posted on by bobgodin

380

I have a basic Gallery to display my data - the source is Sharepoint. I have a collection which I created to overcome the delegation issues in my app called GalleryDataNew

I have the following on my Gallery Items property:

If(
 varWHS = "false",
 SortByColumns(
 Filter(
 Filter(
 colGalleryDataNew,
 Substitute(
 Substitute(
 Lower(varUserMail),
 "@rdmw.qld.gov.au",
 ""
 ),
 ".",
 " "
 ) in Lower(officers) || Substitute(
 Substitute(
 Lower(varUserMail),
 "@rdmw.qld.gov.au",
 ""
 ),
 ".",
 " "
 ) in Lower(approver) || Substitute(
 Substitute(
 Lower(varUserMail),
 "@rdmw.qld.gov.au",
 ""
 ),
 ".",
 " "
 ) in LookUp(
 BusinessUnits,
 unitname in ThisRecord.unitname,
 contact3name
 )
 ),
 searchbox_3.Text in officers || searchbox_3.Text in Text(tripid)
 ),
 "tripid",
 SortOrder.Descending
 )
 )

I have a text box above the Gallery which allows searching on the officers and tripid fields

My query is trying to say "display all trips where the logged on user is any of the following":

The officer on the trip
The approver of the trip
The escalation contact for the business group that the officer is associated with

I am ok with the 1st 2 dot points as the fields are directly within the collection "GalleryDataNew" it's when I come to try the LookUp that it fails:

//this is showing the first contact3name in the Business Units list
LookUp(
 BusinessUnits,
 unitname in ThisRecord.unitname,
 contact3name

I think I am missing something obvious but I can't figure out what it is! any help much appreciated

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Sort by

v-jefferni Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @bobgodin ,

There is a part of criteria in the inner Filter function which I cannot understand very well:

Filter( colGalleryDataNew, Substitute( Substitute( Lower(varUserMail), "@rdmw.qld.gov.au", "" ), ".", " " ) in LookUp( BusinessUnits, unitname in ThisRecord.unitname, contact3name ) )

Could you please share more details about the BusinessUnits list? What types are unitname and contact3name columns?

Basically, if you have a unitname column in the collection of type text, and single line of text contant3name column in BusinessUnits list, this part should be:

LookUp( BusinessUnits, unitname = ThisRecord.unitname, contact3name )

Best regards,

Was this reply helpful? Yes No
bobgodin 380 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @v-jefferni The unitname and contact3name fields are both single line of text in the BusinessUnits List and the unitname column in GalleryDataNew collection is also single line of text
thanks

Was this reply helpful? Yes No
Verified answer

WarrenBelz 156,454 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @bobgodin ,

I was working on saving you a heap of code here as below (which also contains a suggestion)

With( { _UserMail: Lower( Substitute( First( Split( varUserMail, "@" ) ).Value, ".", " " ) ) }, Filter( colGalleryDataNew, ( _UserMail in Lower(officers) || _UserMail in Lower(approver) || _UserMail in LookUp( BusinessUnits As _Units, _Units.unitname = unitname ).contact3name ) && ( searchbox_3.Text in officers || searchbox_3.Text in Text(tripid) ) ) )

the issue here is that LookUp returns the first matching record, so you would simply need equals as it only has one value to match. If you are wanting to return several matching records and use the in statement, you need Filter instead of LookUp

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.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
bobgodin 380 on at

Like
a
(0)

Report
Copy link

Link copied!

@WarrenBelz thanks again Warren - that did the trick!

Was this reply helpful? Yes No