cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Wedgels
Frequent Visitor

Making a filter work with a SQL server data source

I have the following filter attached to the default property of a gallery control: Filter('[dbo].[tblJobMain]',"ClaimNumber" > 0) but it doesn't work. 

 

I am guessing I have some syntax wrong. All I want to do is return all records in the JobMain table that have a ClaimNumber value greater than 0.

 

What am I doing wrong?

8 REPLIES 8
Meneghino Community Champion
Community Champion

Re: Making a filter work with a SQL server data source

Hi @Wedgels

In the Filter function column names are without "", so

Filter('[dbo].[tblJobMain]',ClaimNumber > 0)

Please let me know if this resolves your issue.

Meneghino Community Champion
Community Champion

Re: Making a filter work with a SQL server data source

PS You probaly need to set the Items property of the gallery to this code and not the Default property.

Wedgels
Frequent Visitor

Re: Making a filter work with a SQL server data source

Thank you you are right. Is there anyway I can use it as a default property? I want to have the seach box wired to my items as follows (which works): 

 

SortByColumns(Search('[dbo].[tblJobMain]', TextSearchBox1.Text, "ClaimNumber","ContactName","ContactTelNo"), "ClaimNumber", If(SortDescending1, Descending, Ascending))

 

but also default the view to the filter so that only records with a ClaimNumber greater than 0 show on first load.

Meneghino Community Champion
Community Champion

Re: Making a filter work with a SQL server data source

Hi @Wedgels

The Default property of a gallery does not determine what items are initially shown, the Default property determines which item of those shown is initially selected (although there are some issues with this property, so it does not fully work that way).

The Items property solely determines which items are shown.  Hence if the desired behaviour is for only records with ClaimNumber greater than zero be initially shown, then that needs to be what is in the Items property.  You can implement this for example by using your TextSerchBox1's Default property.  This property determines the initial text of the text input.

However, your gallery's current Items property is not set up to deal with that.  I am also confused since "ClaimNumber" appears to be a text type field and not an integer.

Assuming it is an integer, then set the Default property of TextSearchBox1 to "" (empty string) and set the gallery's Items property to this:

 

SortByColumns(
If( Len(TextSearchBox1.Text)=0, Filter([dbo].[tblJobMain]', ClaimNumber>0), IsNumeric(TextSearchBox1.Text), Filter([dbo].[tblJobMain]', ClaimNumber=Value(TextSearchBox1.Text), Search('[dbo].[tblJobMain]', "ContactName","ContactTelNo") ),
"ClaimNumber", If(SortDescending1, Descending, Ascending)
)

Please let me know

 

 

Wedgels
Frequent Visitor

Re: Making a filter work with a SQL server data source

Apologies. Yes you are correct. The ClaimNumber field is a text field so I need it to show all values that are not null or "". 

Meneghino Community Champion
Community Champion

Re: Making a filter work with a SQL server data source

Hi, then you will not be able to use delegation.

Wedgels
Frequent Visitor

Re: Making a filter work with a SQL server data source

Is delegation just a PowerApps way of saying 'filtering'? So if I want to show all values that are not null on the first call of the form it cannot be done?

Meneghino Community Champion
Community Champion

Re: Making a filter work with a SQL server data source

Hi @Wedgels

I have found that almost anything can be done in PowerApps with the appropriate work-around.

In regards to it being a text column, it seems inconsistent with then trying to filter by ClaimNumber>0

Delegation is explained here:

https://powerapps.microsoft.com/en-us/tutorials/delegation-overview/

Helpful resources

Announcements
secondImage

Community User Group Member Badges

FIll out a quick form to claim your community user group member badge today!

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Apps Community!

Top Solution Authors
Top Kudoed Authors
Users online (7,611)