Delegation inside filter function for SQL tables

[HFirm]

Hi everyone!

I have a filter formula for a SQL datasource in my app that depends on radio values, so for example having a radio with options A, B, C i would need to filter by different criterias like:

IF "radio = 'A'" => filter records where column 1 string equals 'A';
IF "radio = 'B'" => filter records where column 1 string equals 'B'

in the docs🔗 it says that IF doesn't work when delegating queries, i don't entirely understand if it depends if the IF function is using to evaluate a condition IN the table i'm filtering or also applies in a case where the IF evaluates to a value of an element inside the app, so for example,

this wouldn't be delegatable, since i'm evaluating a column in the table:

Filter(
'mySQLTable';

( If(Column1 = 'A' && 'radioInApp'.Selected.Value = 'A' ; Column1 = 'A')   )
)

Otherwise, this would be delegatable, because it's evaluating internally with an element inside the app:

Filter(
'mySQLTable';

( If('radioInApp'.Selected.Value = 'A' ; Column1 = 'A')   )
)

------------

If it isn't delegatable in both cases, a solution would be to wrap the filter in multiple IFs like:

If('radioInApp'.Selected.Value = 'A' ; 
Filter(
'mySQLTable';
Column1 = 'A')
);;
If('radioInApp'.Selected.Value = 'B' ; 
Filter(
'mySQLTable';
Column1 = 'B')
)
(...) // Repeats for each radio option

But that isn't the best option, first of all because it is hard to scale and also a problem when having multiple radios that affects the filtering.

So wrapping up the two main questions are:

A) Is the IF non-delegatable when used to evaluate column values or is it non delegatable always regardless on the use (using it inside a Filter())?

B) Is there a clean and mantainable way to filter conditionally on radio values, specially when having multiple-cascade radio options?

Thank you in advance!

[cwebb365]

I think I'm a little confused by your question. Wouldn't you just use Filter(sqldata, Column1 = radioInApp.Selected.Value) ? 

[HFirm]

Hi @cwebb365 thank you for answering !

---

@cwebb365 wrote:

 Wouldn't you just use Filter(sqldata, Column1 = radioInApp.Selected.Value) ? 

---

No, because i need to filter by multiple conditions depending the radio value, when i check a radio, it should apply 'a pack' of filters. real example from my app (that right now is throwing a delegation warning) in green, elements in app, orange, columns in my table:

Filter(
// When option 1 is selected
If('radioButtonInApp' = "Celo"; fecha_proxima = Today() || fecha_proxima <> Today() && ultimoCeloFecha <= Today() - 14 && ultimoCeloFecha >= fechaUltimaRevisacion; estado = "PLANTEL" );
// When option 2 is selected
If('radioButtonInApp' = "Sin Celo"; fecha_proxima = Today() || fecha_proxima <> Today() && fechaUltimoparto <= Today() - 14 && fechaUltimoparto >= fechaUltimaRevisacion; estado = "PLANTEL" );
// (...) more conditions depending on radio value
)

I have 2 problems here:
1) i have a delegation warning but i don't know if it mean's that MAYBE there is a problem on delegation or that it WON'T delegate
2) inside both Ifs i have an ELSE statement (underlined) that are the same, i had to do this because i think that Powerapps is evaluating something like:

"If condition is true, send filter condition, else Null", and the table returns 0 records, the solution i found is to send a common true parameter to all filters (could be also something like id <> Blank()). 

The thing is that i suspect that there could be performance issues since the SQL recives the same filter condition for each IF :

SELECT *
FROM tableName
WHERE categoria = "PLANTEL" AND categoria = "PLANTEL" AND categoria = "PLANTEL" AND categoria = "PLANTEL" AND (...)

[v-bofeng-msft]

Hi @HFirm ,

Maybe you could try:

Filter(
  'mySQLTable';
  ('radioButtonInApp' = "Celo" && (fecha_proxima = Today() || fecha_proxima <> Today() && ultimoCeloFecha <= Today() - 14 && ultimoCeloFecha >= fechaUltimaRevisacion)) ||
  ('radioButtonInApp' = "Sin Celo" && (fecha_proxima <> Today() && fechaUltimoparto <= Today() - 14 && fechaUltimoparto >= fechaUltimaRevisacion)) ||
  ('radioButtonInApp' <> "Celo"  && 'radioButtonInApp' <> "Sin Celo" && estado = "PLANTEL")
)

Best Regards,

Bof 

[HFirm]

Hi @v-bofeng-msft Bof! that seems to work! I dont get why these two gives back different results (the first part of the filter) since both filters by the same parameters. The one with the || and && operators returns 42 items while the one with IF statements returns 68. I'm testing the filter function with this query only.:

A)

(tableFilter.filtroRev = "Celo" && ( ( fecha_proxima = Today() || fecha_proxima <> Today() ) && ultimoCeloFecha <= Today() - 14 && ultimoCeloFecha >= fechaUltimaRevisacion))

---------
B)
If(tableFilter.filtroRev = "Celo"; ( fecha_proxima = Today() || fecha_proxima <> Today() ) && ultimoCeloFecha <= Today() - 14 && ultimoCeloFecha >= fechaUltimaRevisacion )


Apart from that, what does this part suppose to do? does it 'cancel' sending the radio value query to SQL ?

  ('radioButtonInApp' <> "Celo"  && 'radioButtonInApp' <> "Sin Celo" && estado = "PLANTEL")