Answered

Specifying a value when a complex AddColumns result will be blank

(0) Share

Report

Posted on by zuurg

546 Super User 2024 Season 1

I have a collection that is being created that has a pretty convoluted search for an AddColumns statement (due to the use of a Sharepoint Person field:

ClearCollect(
 colWednesdayNight,
 AddColumns(
 'AES Global Portfolio',
 "Team Name",
 First(Filter(Search(AddColumns('Hot Add Controls',"DisplayName",Resource.DisplayName),
 'Executive Sponsor'.DisplayName,"DisplayName"),Title="Executive Sponsor")).Team
 )
)

The column addition is working properly, the only issue is that if the Executive Sponsor column has nobody listed in it, the search returns "Operations", which is the first team in the Hot Add Controls list that has the Title "Executive Sponsor". If the Executive Sponsor field is empty, I'd like it to park a "N/A" in the Team Name field.

I've tried all kinds of combinations of If(!IsEmpty...) and If(!IsBlank...) but can't seem to figure out something that works.

Would love some help!

Categories:

Building Power Apps

All responses (4)

Answers (1)

Sort by

SudeepGhatakNZ 14,396 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

How many items do you have in your list? The Not function <> / isBlank / isEmpty are not delegable queries. Can you check if you are hitting delegation limits?
zuurg 546 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

I'm not hitting delegation limits. The Global Portfolio list has 263 records in it, the Hot Add Controls has 23 records in it.
SudeepGhatakNZ 14,396 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Are you checking this entire formula for IsBlank?

If(IsBlank(First(Filter(Search(AddColumns('Hot Add Controls',"DisplayName",Resource.DisplayName),'Executive Sponsor'.DisplayName,"DisplayName"),Title="Executive Sponsor")).Team),'N/A')
Verified answer

zuurg 546 Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
I guess I must have been thinking about this issue in my sleep because I woke up at 4:00 with the solution:
My approach was wrong. Originally, I was checking to see if the results of a search for an item I knew didn't exist was blank. My new approach was: if the original field is blank, don't even bother doing the search and just fill it with "N/A" instead.
Here's the new code:
ClearCollect( colWednesdayNight, AddColumns( 'AES Global Portfolio', "Team Name", If(!IsBlank('Executive Sponsor'),First(Filter(Search(AddColumns('Hot Add Controls',"DisplayName",Resource.DisplayName), 'Executive Sponsor'.DisplayName,"DisplayName"),Title="Executive Sponsor")).Team,"N/A") ) )