cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
dmaples
Level: Powered On

Query Based on Criteria from Another Form

I am trying to set up a basic "query by form" where I input three criteria, click a submit button, and the returned records appear on a new screen in tabular form. See screenshots.

 

P1.pngP2.png

 

I want the returned table to be limited to that specific Job #, F/M and Work Pkg. The data resides in an Azure SQL Server database.

I could do this in VB/C#/Access, but I haven't figured out the way to do this in PowerApps. What's the best way to go about doing this? Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Query Based on Criteria from Another Form

Hi @dmaples 

 

That is because on visible of the second screen you are setting collection to the data source

remove that onvisible logic for the second screen

 

 

9 REPLIES 9
Super User
Super User

Re: Query Based on Criteria from Another Form

Hi @dmaples 

 

In the Items property of your data table use Filter to filter data based on the attributes

 

Filter(datasource, StartsWith(JobNumbercolumn,JobNumbertextfield.Text) || StartsWith(fieldormfgcolumn,fieldormfgtextfield.Text) || StartsWith(workpkgcolumn,workpkg.Text))

 

 

You can replace || with && for AND operation

 

check this video on how to filter data source

https://www.youtube.com/watch?v=pn50AKn3Q1Y

 

Regards,

Reza Dorrani

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

dmaples
Level: Powered On

Re: Query Based on Criteria from Another Form

Thank you for the response. This is a great start. Our current requirements are for a separate lookup/filter screen (per the screenshots), but I think I could utilize this as long as the gallery or table shows no records until the filter is executed.

 

I will work with this after viewing the video and report back my results.

 


@RezaDorrani wrote:

Hi @dmaples 

 

In the Items property of your data table use Filter to filter data based on the attributes

 

Filter(datasource, StartsWith(JobNumbercolumn,JobNumbertextfield.Text) || StartsWith(fieldormfgcolumn,fieldormfgtextfield.Text) || StartsWith(workpkgcolumn,workpkg.Text))

 

 

You can replace || with && for AND operation

 

check this video on how to filter data source

https://www.youtube.com/watch?v=pn50AKn3Q1Y

 

Regards,

Reza Dorrani

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly


 

dmaples
Level: Powered On

Re: Query Based on Criteria from Another Form

So let's frame this problem a bit differently. I have the following single form:

 

P3.png

 

I have inserted three search criteria fields called jobno, workpkg and FM. I want them to map to job_no, work_package and field_or_mfg in a table called dbo.tblWorkPackage. How do I structure the filter so that when I execute it with the Submit button, it returns the records I need in the table at the bottom of the screen?

 

Again, thanks in advance.

Super User
Super User

Re: Query Based on Criteria from Another Form

Hi @dmaples 

 

In this case what you can do is when user hits submit (on click)

 ClearCollect(colData,Filter(datasource, StartsWith(JobNumbercolumn,JobNumbertextfield.Text) || StartsWith(fieldormfgcolumn,fieldormfgtextfield.Text) || StartsWith(workpkgcolumn,workpkg.Text)))

 

create another button call clear filter (on click)

ClearCollect(colData,datasource)

 

and on visible of the screen do

ClearCollect(colData,datasource)

 

for the data table items property use colData

 

Regards,

Reza Dorrani

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

dmaples
Level: Powered On

Re: Query Based on Criteria from Another Form

So we've made some progress (much obliged to you!) I've entered the following function into a Submit button:

 

ClearCollect(colData,Filter('[dbo].[tblWorkPackage]', job_no = Value(jobno.Text), work_package = Value(workpkg.Text), field_or_mfg = FM.Text))

 

This works!

 

However, I'm trying to do the same with two forms (one to originate the query criteria, and one to display the collection). I've applied the same formula to that form (changing the form elements' names and binding the ClearCollect function to them. I'm then using a Navigate() function to show the new form. The form displays, but it shows the unfiltered contents of the collection. I expect one record, and it displays all. I'm referring to the collection in the new form, so shouldn't it display the filtered contents?

 

Thanks!

Super User
Super User

Re: Query Based on Criteria from Another Form

Hi @dmaples 

 

sorry I did not understand

 

Can you explain with screenshots

dmaples
Level: Powered On

Re: Query Based on Criteria from Another Form

I'm essentially breaking the first solution up into two screens:

 

P1.png

 

And...

 

P4.png

 

The first screen is my criteria. The second screen is the results. However, the second screen displays the whole table rather than the filtered collection (which works when I combine both into a single screen. I should just be able to pass the collection from the query screen to the displayed table, but it's not working. I've verified that the data source of the table control is the collection, and not the table itself. Ideas?

Super User
Super User

Re: Query Based on Criteria from Another Form

Hi @dmaples 

 

That is because on visible of the second screen you are setting collection to the data source

remove that onvisible logic for the second screen

 

 

dmaples
Level: Powered On

Re: Query Based on Criteria from Another Form

This appears to work as desired. Thanks for the help!

Helpful resources

Announcements
firstImage

Microsoft Business Applications Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

firstImage

Watch Sessions On Demand!

Continue your learning in our online communities.

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

FirstImage

Power Platform World Tour

Coming to a city near you

thirdimage

PowerApps Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

FourthImage

Join PowerApps User Group!!

Connect, share, and learn with your peers year-round

Top Kudoed Authors
Users Online
Currently online: 210 members 5,184 guests
Please welcome our newest community members: