Gallery. How to set view permission based on two lists?

[Australia]

I would like to achieve the following in my Gallery property:

If you are a sys-administrator, you see all invoices displayed in Gallery. Otherwise, if you are just an end-user, the Gallery will only display invoice(s) that associates with your name or your nominee's name(s).  Note that the nominee info is stored in a separate SharePoint list.

How do I add the following nominees line to my existing script?

LookUp('Invoice Nominees','Invoice Number'=Gallery.Invoice Number).NomineeName

Here's my existing script of the Gallery:

If(
!IsBlank(
LookUp('Sys Admin List', StaffIDEmail = varUserEmail).StaffIDEmail),

Sort(
Search(
'Invoice List',
TextSearchBox.Text,
"Invoice Number",
"Invoice_x0020_Title",
"Primary_x0020_Holder_x0020_Name"
),
Invoice Number,
Descending
),
Sort(
Filter(
'Invoice List',
Primary_x0020_Holder_x0020_Name = varUserName
),

Title,
Descending
))

________

Thank you for your assistance.

[CNT]

@Australia Try this,

If(
	!IsBlank(
	LookUp('Sys Admin List', StaffIDEmail = varUserEmail).StaffIDEmail),
		Sort(
			Search('Invoice List',
				TextSearchBox.Text,
				"Invoice Number",
				"Invoice_x0020_Title",
				"Primary_x0020_Holder_x0020_Name"
			),Invoice Number,Descending
		),
		Sort(
			Filter('Invoice List',
			Primary_x0020_Holder_x0020_Name = varUserName ||
			Primary_x0020_Holder_x0020_Name = LookUp('Invoice Nominees','Invoice Number'=ThisItem.'Invoice Number').NomineeName
			),Title,Descending
		)	
)

[Australia]

@CNT Many thanks for helping me out here. I think we are almost there, I am just getting a red line under ThisItem.'Invoice Number'
Do you know how we can resolve this?

[CNT]

@Australia Try removing ThisItem.

Let me know how it's going.

[Australia]

Hi @CNT ,

Yay no more red error but now the Gallery does not display anything 😞 

The App seems forever loading (has been loading for over 5 minutes now) 

[CNT]

@Australia It shouldn't do that, Anyway, we'll tweak it a bit like this,

If(
	!IsBlank(
	LookUp('Sys Admin List', StaffIDEmail = varUserEmail).StaffIDEmail),
		Sort(
			Search('Invoice List',
				TextSearchBox.Text,
				"Invoice Number",
				"Invoice_x0020_Title",
				"Primary_x0020_Holder_x0020_Name"
			),Invoice Number,Descending
		),
		Sort(
			Filter('Invoice List',
			Primary_x0020_Holder_x0020_Name = varUserName ||
			Primary_x0020_Holder_x0020_Name = LookUp('Invoice Nominees','Invoice Number'='Invoice Number').NomineeName
			),Title,Descending
		)	
)

[Australia]

@CNT 

Success! You are a legend.

It is actually the previous line that has eventually worked for me:

Primary_x0020_Holder_x0020_Name = LookUp('Invoice Nominees','Invoice Number'= 'Invoice Number').NomineeName

Must be my internet connection yesterday causing the false alarm so apologies.  JFYI, the tweaked line did not work.

Just before we wrap up this thread if you don't mind CNT,  what happens if I have two or more nominees against an invoice? My understanding is that the LookUp function only takes the first line of search.

Many thanks.

[CNT]

@Australia Glad to help!

Regarding 2 or more nominees, it depends on how this info is captured in the SP List. We can always alter the query from a LookUp to a Filter.

Please remember to give a 👍 and accept the solution as it will help others in the future.

[Australia]

Thanks @CNT 

The current nominee SP list is as follows. Hope you can help me solve the last piece of this puzzle.

Invoice Number	Nominee Name	Nominee Email
2021/001	Adam Smith	Adam.Smith@test.com
2021/002	Joe Bloe	Joe.Bloe@test.com
2021/002	Ricky Jackson	Ricky.jackson@test.com
2021/002	Mary Fendi	Mary.Fendi@test.com
2021/003	Vanessa Andrews	Vanessa.Andrews@test.com
2021/004	Tom Anderson	Tom.Anderson@test.com
2021/004	Jimmy Hughes	Jimmy.Hughes@test.com

[CNT]

@Australia Try this,

If(
	!IsBlank(
	LookUp('Sys Admin List', StaffIDEmail = varUserEmail).StaffIDEmail),
		Sort(
			Search('Invoice List',
				TextSearchBox.Text,
				"Invoice Number",
				"Invoice_x0020_Title",
				"Primary_x0020_Holder_x0020_Name"
			),Invoice Number,Descending
		),
		Sort(
			Filter('Invoice List',
			Primary_x0020_Holder_x0020_Name = varUserName ||
			Primary_x0020_Holder_x0020_Name in ShowColumns(Filter('Invoice Nominees','Invoice Number'='Invoice Number'),"NomineeName")
			),Title,Descending
		)	
)