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

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
mdevaney
Super User
Super User

@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

v-xida-msft
Community Support
Community Support

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
mdevaney
Super User
Super User

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

kris_T
Frequent Visitor

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.

Yes, SQL views are read only right now so my approach does not work for writing
v-xida-msft
Community Support
Community Support

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.

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events that are happening this month - don't miss out!

Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Top Solution Authors
Top Kudoed Authors
Users online (3,162)