Answered

Filter with Search and Sorting Power App

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hello,

I created an app using data from CDS (the generic auto created one). I want to filter the list based on the status of the entity (Active) and if certain fields have data. Every time I try to put the Filter function in the below but no matter where I put it, I get the red lines of errors. Basically I need to know if a record is active and if there are 2 fields with data in them.

SortByColumns(Search([@Contacts], TextSearchBox1.Text, "gtwuk_membershipid","fullname","emailaddress1","telephone1"), "gtwuk_membershipid", If(SortDescending1, Descending, Ascending))

Any help would be great.

Thanks

Categories:

Building Power Apps

I have the same question (0)

All responses (9)

Answers (1)

Sort by

eka24 20,925 on at

Like
a
(0)

Report
Copy link

Link copied!

With the 3 criteria, I think you should insert two additional comboboxes.
In combobox1 items put: Contact.gtwuk_membershipid

In combobox2 items put:
Contact.emailaddress1

Then filter as follows:
SortByColumns(Filter(Search([@Contacts], TextSearchBox1.Text,"fullname","telephone1")&& emailaddress1=Combobox2.Selected.emailaddress1&&gtwuk_membershipid=
Combobox1.Selected.gtwuk_membershipid)),"gtwuk_membershipid", If(SortDescending1, Descending, Ascending))

------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@eka24
Thanks but after seeing this and thinking more about it, I really want to filter out the inactive records. When I try to do it with a Filter function I can't get it to work.
Basically I need Filter(Contacts, 'Status Reason (Contacts)'.Active) but I can't figure out how to get it in the given string and for it to work. Everything I have tried has failed.

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Anonymous

I would take a different approach and create or modify some views using the entity's properties. (Middle screen shot and first screen shot) You can then select those views when you are creating the Items property of gallery. PowerApps will automatically create the correct filter in the gallery. The ability to create prefiltered and presorted views is one of the great advantages of CDS (and SQL too).

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@Drrickryp

Sorry still learning my way around PowerApps. Can I still use a search function (obviously a text input box) with a view?

The primary purpose is to be able to lookup and edit contacts. I just don't want to query over a million contacts.

Thanks

Was this reply helpful? Yes No
eka24 20,925 on at

Like
a
(0)

Report
Copy link

Link copied!

Can you show sample of your table with columns?
If you are to filter for active only, you could try:
Filter(Contacts, 'Status Reason (Contacts)'="Active")
If it doesnt work, a sample of your table can help.
------------

If you like this post, give a Thumbs up. Where it solved your request, Mark it as a Solution to enable other users find it.

Was this reply helpful? Yes No
Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous

Sure, Just wrap the search around the filtered dataset. By using the entity's view you are already limiting the number of records that will need to be searched. This will really simplify writing the formula for the gallery too. When CDS introduced this feature, I thought it was too good to be true.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

@Drrickryp and @eka24

So this is what I have been trying and it is failing. I keep getting invalid arguments.
SortByColumns(
Search(Filter(Contacts,'Status Reason (Contacts)' = "Active"), [@Contacts], TextSearchBox1.Text, "gtwuk_membershipid","fullname","emailaddress1","telephone1"), "gtwuk_membershipid", If(SortDescending1, Descending, Ascending))
OR
Search(Filter(Contacts, 'Contacts (Views)'.'Active Members'), [@Contacts], TextSearchBox1.Text, "gtwuk_membershipid","fullname","emailaddress1","telephone1"))

I have a screenshot of some of the data we are using. It doesn't have the status listed but it is the generic status option set of active inactive.

Was this reply helpful? Yes No
Verified answer

Drrickryp Super User 2024 Season 1 on at

Like
a
(0)

Report
Copy link

Link copied!
Not sure why you have [@Contacts] in your formula.

Search( Filter( Contacts, 'Contacts (Views)'.'Active Members' ), TextSearchBox1.Text, "gtwuk_membershipid", "fullname" , "emailaddress1", "telephone1" )

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

That works! Thank you

Was this reply helpful? Yes No