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

Using LOOKUP in FILTER - syntax

Hi all,

 

Using SQL tables, one is HOTEL table, the second one is the PLANNING table, with a 1 to many relationship.

 

I have a gallery based on PLANNING.

I was hoping to filter the PLANNING records to ONLY show when HOTEL field STATUS = "Customer"

 

In Data Source, I tried :

Filter([@'[dbo].[Planning]'], LookUp('[dbo].[hotel]', id_FK = Hotel_id,Customertype) = "Customer")

 

but I get double-blue-delegation warning and my gallery only shows one record, and this one record is not even of the correct customertype.

 

any advice appreciated.

 

Kris

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Using LOOKUP in FILTER - syntax

@kris_T 

I feel the easiest way to avoid delegation warnings here would be to create a new SQL View in your database that joins the PLANNING table with the Status column in the HOTELS table.  From there you could also decide whether its best to FILTER on Status="Customer" in the SQL View vs. the PowerApp.  Pre-filtering this in the SQL view would give the best performance.

 

Are you open to giving this a try?

 

---
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."

View solution in original post

Community Support Team
Community Support Team

Re: Using LOOKUP in FILTER - syntax

HI @kris_T ,

Could you please share a bit more about your scenario?

Do you want to filter your PLANNING records based on the Customertype field in your HOTEL table?

How many records existed in your PLANNING table? More than 2000 records?

 

I have made a test on my side, please consider take a try with the following formula:

Filter(
[@'[dbo].[Planning]'],
Hotel_id in Filter('[dbo].[hotel]', Customertype = "Customer").id_FK
)

Above formula may cause a Delegation warning issue, if the amount of your PLANNING records is not more than 2000, you could ignore this warning issue.

Note: Please set the "Data row limit for Non-delegable queries" option to maximum value -- 2000 within Advanced setting of App settings of your app.

 

If the amount of your PLANNING records is more than 2000, you could consider bulk-load your PLANNING records into your app. Please check and see if the solution mentioned within the following thread would help in your scenario:

https://powerusers.microsoft.com/t5/General-Discussion/Pulling-in-large-ish-SQL-tables/m-p/243777#M7...

 

In addition, as an alternative solution, I agree with @mdevaney 's thought almost, I think SQL View could fix your Delegation warning issue. On your side, you could create a SQL View based on the relationship, then use the SQL View as data source in your app.

You could add a Gallery control in your app, then connect it to the SQL View, to list all available records from your PLANNING table. Although, the SQL View is Read-Only within PowerApps app, you could use the Primary Key column in SQL View to update the records in the separated tables (PLANNING table & HOTEL table) through the Patch function.

Please take a try with the following workaround:

Set the Items property of the Gallery to following:

'[dbo].[SQLView]'

If you want to update the records in your PLANNING table, please take a try with the following formula:

Patch(
          '[dbo].[PLANNING]',
           LookUp('[dbo].[PLANNING]', PlanIdKey = Gallery1.Selected.PlanIdKey),
           {
               Column1: "xxxx",
               Column2: "xxxx",
               Column3: "xxxx",
                 ...
           }
)

Please consider take a try with above solution, check if the issue is solved.

 

Best regards,

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

4 REPLIES 4
Super User
Super User

Re: Using LOOKUP in FILTER - syntax

@kris_T 

I feel the easiest way to avoid delegation warnings here would be to create a new SQL View in your database that joins the PLANNING table with the Status column in the HOTELS table.  From there you could also decide whether its best to FILTER on Status="Customer" in the SQL View vs. the PowerApp.  Pre-filtering this in the SQL view would give the best performance.

 

Are you open to giving this a try?

 

---
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."

View solution in original post

kris_T
Level: Powered On

Re: Using LOOKUP in FILTER - syntax

Thanks for your suggestion,

I tried with VIEWS before, which works wonders of course,

however I want to update fields in my PLANNING table, and that is currently not possible on an SQL-View.

The VIEW would indeed give me the correct records, but then I am stuck when trying to update some fields ....

Unless I missed something.

Super User
Super User

Re: Using LOOKUP in FILTER - syntax

Yes, SQL views are read only right now so my approach does not work for writing
Community Support Team
Community Support Team

Re: Using LOOKUP in FILTER - syntax

HI @kris_T ,

Could you please share a bit more about your scenario?

Do you want to filter your PLANNING records based on the Customertype field in your HOTEL table?

How many records existed in your PLANNING table? More than 2000 records?

 

I have made a test on my side, please consider take a try with the following formula:

Filter(
[@'[dbo].[Planning]'],
Hotel_id in Filter('[dbo].[hotel]', Customertype = "Customer").id_FK
)

Above formula may cause a Delegation warning issue, if the amount of your PLANNING records is not more than 2000, you could ignore this warning issue.

Note: Please set the "Data row limit for Non-delegable queries" option to maximum value -- 2000 within Advanced setting of App settings of your app.

 

If the amount of your PLANNING records is more than 2000, you could consider bulk-load your PLANNING records into your app. Please check and see if the solution mentioned within the following thread would help in your scenario:

https://powerusers.microsoft.com/t5/General-Discussion/Pulling-in-large-ish-SQL-tables/m-p/243777#M7...

 

In addition, as an alternative solution, I agree with @mdevaney 's thought almost, I think SQL View could fix your Delegation warning issue. On your side, you could create a SQL View based on the relationship, then use the SQL View as data source in your app.

You could add a Gallery control in your app, then connect it to the SQL View, to list all available records from your PLANNING table. Although, the SQL View is Read-Only within PowerApps app, you could use the Primary Key column in SQL View to update the records in the separated tables (PLANNING table & HOTEL table) through the Patch function.

Please take a try with the following workaround:

Set the Items property of the Gallery to following:

'[dbo].[SQLView]'

If you want to update the records in your PLANNING table, please take a try with the following formula:

Patch(
          '[dbo].[PLANNING]',
           LookUp('[dbo].[PLANNING]', PlanIdKey = Gallery1.Selected.PlanIdKey),
           {
               Column1: "xxxx",
               Column2: "xxxx",
               Column3: "xxxx",
                 ...
           }
)

Please consider take a try with above solution, check if the issue is solved.

 

Best regards,

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

Helpful resources

Announcements
thirdimage

Power Apps Super User Class of 2020

Check it out!

thirdimage

New Badges

Check it out!

thirdimage

Power Apps Community User Group Member Badge

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

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

SecondImage

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors
Users online (5,725)