Sort SharePoint Lookup in PowerApp Form

(0) Share

Report

Posted on by adzocol

Hello,

Reealllly hoping someone can point me in the right direction as I am pulling my hair out with this.

I am trying to customise a SharePoint form using PowerApps. My issue is that in the main SharePoint List I have a lookup column that pulls in data from another List. When I load the default form in SharePoint I see the data as expected. However the same data in PowerApps is prefixed by a lot of blanks at the top of the dropdown. You have to scroll quite far down to see the data (Blanks in PowerApps.JPG).

The Lookup is taking data from a specific column called 'Active to View' within the Cost Centre List. The 'Active to View' column is a concatenation of 2 fields if a 3rd column, 'Active?' is TRUE (Active to view.JPG). I think what is happening in PowerApps is that all the data is being pulled through regardless of whether it meets the 'Active?' criteria or not. The formula in PowerApps is shown in 'PowerApps Formula.JPG'. Cost Centre 1 is the column in SharePoint.

So, I am not at all sure how I get PowerApps to either

1. Only pull through the Active records (i.e. no blank data)

2. Sort the data so the blanks go to the bottom (not ideal)

I have also tried adding the Cost Centre List as a datasource in PowerApps and getting the data directly but I cant get this to work either.

Many thanks in advance.

Categories:

Building Power Apps

All responses (12)

Answers (1)

Sort by

mdevaney 29,993 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@adzocol

I have two ideas to get rid of the blank data.

The first is to FILTER on all items that are not blank by putting this code in the Items property of the Dropdown.

Filter('APAC Requisitions', IsBlank(CostCentre1)=false)

Another idea is to use the DISTINCT function to remove all of the duplicates. The limitation of doing this is it cannot be delegated (essentially a max of 2,000 rows)

Distinct('APAC Requisitions', CostCentre1)

Note: if you use DISTINCT the column it returns is called "Result" instead of "Value"

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."
adzocol 51 on at

Like
a
(0)

Report
Copy link

Link copied!

@mdevaney

Thank you very much for the prompt response. I really appreciate it.

I recorded a gif to show what is happening. I tried both of your suggestions but on each occasion the list is empty. I must be doing something fundamentally wrong as I can understand what you provided should indeed work well.

Many thanks,
Adzoccol
mdevaney 29,993 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@adzocol
Did you see the little yellow warning symbol in your gif? Please share the complete error message.

Also, did you try my first suggestion with FILTER instead?

—-
Please Accept as Solution if this post answered your question so others may find it more quickly
. If you found this post helpful consider giving it a Thumbs Up.
adzocol 51 on at

Like
a
(0)

Report
Copy link

Link copied!

@mdevaney

Yes the Amber error message was a delegation warning. What I've done is amended the CostCentre 1 list. The Active to View field is a concatenation of 2 other columns. I've change the formula from

=IF(Active?,[Cost Centre]&" - "&Description,"")

to

=IF(Active?,[Cost Centre]&" - "&Description,"No")

The original formula would not have rendered an empty cell (Doh! my bad. ). I'm now looking at how to filter out the 'No' records

Thanks,
Adzocol
adzocol 51 on at

Like
a
(0)

Report
Copy link

Link copied!

@mdevaney

Error message attached when using Filter

Thanks,
Adzocol
mdevaney 29,993 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@mdevaney
Ok, I am now totally lost. Are you still trying to solve the same problem? Did you move onto a different problem? If you need help please ask and restate what is needed.

Or if the problem is solved please mark the post that led to the solution to close the thread.

—-
Please Accept as Solution if this post answered your question so others may find it more quickly. If you found this post helpful consider giving it a Thumbs Up.
mdevaney 29,993 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@adzocol
I am not sure what impact your concatenation column has on the delegation warning. Please try the FILTER with and without concatenation. Many times the IF statement and concatenate can cause delegation errors. You typically must make do without them.
adzocol 51 on at

Like
a
(0)

Report
Copy link

Link copied!

Apologies @mdevaney.

Same problem but I am a little further forward. So as I stated I changed the original CostCentre1 data. For the ineligible records in there these are now marked as 'No' as opposed to "" which is why the IsEmpty filter did not work. I now have records at the top but as you may imagine the 'No' records appear further down.

So, final question; How would I filter out the 'No' records?

Thanks,
Adzocol
adzocol 51 on at

Like
a
(0)

Report
Copy link

Link copied!

Thank you @mdevaney

I have no errors now. I did try using 'Distinct' in the formula as a Test, which would leave me with a single 'No' entry, but the dropdown is blank. You can see in the gif the Choices formula works so I just need to figure out how to filter out the 'No' records.

Thanks,
Adzocol
Verified answer

mdevaney 29,993 Moderator on at

Like
a
(1)

Report
Copy link

Link copied!
@adzocol

Now I feel like I am back to a place where I understand what problem we are trying to solve, LOL. Lets do this thing!!!

Currently you have this formula.

Choices([@'APAC Requisitions'].CostCentre1)

My first instinct would be to do this but it leads to a delegation error.

Filter(Choices([@'APAC Requisitions'].CostCentre1), Value <> "No")

Its because the does not equal (i.e. "<>") operator cannot be delegated. See the table below.

This does not leave many good options for filtering out the CostCentres that are equal to "No" that follow the delegation rules. The only one I can think of is STARTSWITH but it could eliminate some cost centres incorrectly.

Filter(Choices([@'APAC Requisitions'].CostCentre1), StartsWith(Value, "No"))

Here's what I think you should do. For any CostCentres with a value of "No" change them to Blank instead in your datasource. Then you can use the ISBLANK function to elminate them just like the other rows you did previously. There really is no workaround here that I know of so my best advice is to change how data is being entered.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."