Using LOOKUP in FILTER - syntax

[kris_T]

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

[mdevaney · Accepted solution]

@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]

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.

[mdevaney]

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

[v-xida-msft · Accepted solution]

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,