cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
iioannou
Helper I
Helper I

Powerapps - SQL filter based on field

I need some help with this. I have setup a sharepoint form (edit/new) which basically I want to show the data if there is an email. 

I did manage to setup an sql and load my tables data but I want this data to be loaded only if there is an email in the email field and also to be filtered based on that email.

 

I am really looking forward to some ideas on how to do this

 

CaptureSQL.PNG

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @iioannou,

 

How do you display the filtered SQL data within your app? Using Gallery control?

Could you please show a bit  more about your SQL Table data?

 

If you use a Gallery control to display the filtered SQL data, please make sure that you have referenced proper property within the Gallery control. I have made a test on my side and don't have the issue that you mentioned. The screenshot as below:2.JPGSet the Items property of the Gallery control to following formula:

 

If(!IsEmpty(Reporting1),Filter(Reporting1,'{FilenameWithExtension}'="Beauty.jpeg"))

On your side, set the Items property of the Gallery control to following formula:

 

 

If(!IsBlank(txtPMSReference.Text),Filter('[dbo].[Reservations]', Reference= txtPMSReference.Text))

 

 

You could consider take a try to add some Label controls within the Gallery control to display the column value of the filtered data (that you want to display). If you want to reference the column value within the filtered data, please take a try with the following formula:

ThisItem.ColumnName

More details about the Gallery control, please check the following article:

Gallery control

 

If the issue still exists, please consider take a try to fresh your SQL Table data source within your app, and check if there are corresponding records existed within your SQL table based on the filter condition.

 

Best regards,

Kris 

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-xida-msft
Community Support
Community Support

Hi @iioannou,

 

Could you please share a bit more about your app's configuration?

Could you please show more details about the Item property of the Edit/New form?

Further, where do you load the SQL table?

 

If you want to load data based on the email within your Edit/New form, I think the If function and Filter function could achieve your needs.

 

I have made a test on my side, please take a try to modify your formula as below:

If(!IsBlank(DataCardValue7.Text),Filter(SQLTableData,Email=DataCardValue7.Text))

or

If(Not(IsBlank(DataCardValue7.Text)),Filter(SQLTableData,Email=DataCardValue7.Text))

 

Note: The DataCardValue7 represents the Text input control within the Email Data card of the Edit/New form. The SQLTableData represents the SQL Data that you retrieved.

 

More details about the Filter function in PowerApps, please check the following article:

Filter function

 

 

Best regards,

Kris

 

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you @v-xida-msft

 

Any ideas though once my filter is correct why the data is not showing ? 

 

This is the filter formula

 

 

If(!IsBlank(txtPMSReference.Text),Filter('[dbo].[Reservations]', Reference= txtPMSReference.Text))

 

If I type the reference wrong I get no data found which is correct

 

Capture2222.PNG

 

But if I type the reference correct then this is what happens

 

Capture11111.PNG

 

For your reference if it matters

SQL field is set to nvarchar 50

Sharepoint field reference is set to Single Line of Text

power apps form field is set to text

Hi @iioannou,

 

How do you display the filtered SQL data within your app? Using Gallery control?

Could you please show a bit  more about your SQL Table data?

 

If you use a Gallery control to display the filtered SQL data, please make sure that you have referenced proper property within the Gallery control. I have made a test on my side and don't have the issue that you mentioned. The screenshot as below:2.JPGSet the Items property of the Gallery control to following formula:

 

If(!IsEmpty(Reporting1),Filter(Reporting1,'{FilenameWithExtension}'="Beauty.jpeg"))

On your side, set the Items property of the Gallery control to following formula:

 

 

If(!IsBlank(txtPMSReference.Text),Filter('[dbo].[Reservations]', Reference= txtPMSReference.Text))

 

 

You could consider take a try to add some Label controls within the Gallery control to display the column value of the filtered data (that you want to display). If you want to reference the column value within the filtered data, please take a try with the following formula:

ThisItem.ColumnName

More details about the Gallery control, please check the following article:

Gallery control

 

If the issue still exists, please consider take a try to fresh your SQL Table data source within your app, and check if there are corresponding records existed within your SQL table based on the filter condition.

 

Best regards,

Kris 

 

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

No I am using the Data Table control

If I remove the filters the data is showing correctly... 

 

Also another strange thing is that the headers are not showing at all... Please see below and you can understand what I mean... 

Capturehq.PNG

That was crazy.... the problem was the theme... As soon as I changed the theme things started to show up... Unbeliebable. Many thanks for your help @v-xida-msft

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are Your Ready?

Test your skills now with the Cloud Skill Challenge.

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Top Solution Authors
Top Kudoed Authors
Users online (44,013)