IfError in Filter for SQL Server connection

[jjacquet]

I have a connection to SQL Server 'rds.RPTINSTNC'. I am trying to use this with a combo box. I am pre-filtering the Items property using delegable functions because the table is large-ish, and then returning a sorted list with an added column.

When a user enters text, they expect to be able to search by either RPTINSTNCNAM (text field, name of instance), or RPTINSTNCID (numeric ID of instance). Therefore, I'm trying to pass an "or" filter.

For the numeric filter, I can't do a straight conversion like Value(Self.SearchText) because when a user types "customer" - that is an error. Enter my attempt to use IfError().

Here is what I'd like to do:

With(
    {wStart:
    Filter(
        'rds.RPTINSTNC',
        (Self.SearchText in RPTINSTNCNAM || IfError(Value(Self.SearchText),-1) = RPTINSTNCID) && ACTVIND = "Y"
        )
    }
,
Sort(
      AddColumns(wStart, "RPTINSTNCIDTXT", Text(RPTINSTNCID)),
      RPTINSTNCIDTXT
    )
)

The issue: When I do this, I can search by RPTINSTNCNAM no problem. However, when I enter a number like "8909" it returns zero rows (this is a valid result). I'm receiving zero warnings/errors in the editor.

My question: Is the formula trying to pass IfError() to the server? I would have assumed it would resolve that on the app side, then pass the result+filter to the server.

Any help here is greatly appreciated, thank you.

[jjacquet]

I worked around this for now, but it's ugly & repetitive. If anyone has thoughts on why the workaround was necessary I'm all ears.

I could have gone a number of ways, but here was my result: 

IfError(
/* Try to do the conversion... */
With(
    {wStart:
    Filter(
        'rds.RPTINSTNC',
        (Self.SearchText in RPTINSTNCNAM || Value(Self.SearchText) = RPTINSTNCID) && ACTVIND = "Y"
        )
    }
,
Sort(
      AddColumns(wStart, "RPTINSTNCIDTXT", Text(RPTINSTNCID)),
      RPTINSTNCIDTXT
    )
),
/* If converstion fails, just skip that filter */
With(
    {wStart:
    Filter(
        'rds.RPTINSTNC',
        Self.SearchText in RPTINSTNCNAM && ACTVIND = "Y"
        )
    }
,
Sort(
      AddColumns(wStart, "RPTINSTNCIDTXT", Text(RPTINSTNCID)),
      RPTINSTNCIDTXT
    )
)
)

[v-jefferni]

Hi @jjacquet ,

You can user Search function to get the filtered table:

With(
    {wStart:
        Filter(
            Search(
                'rds.RPTINSTNC',
                Self.SearchText,
                "RPTINSTNCNAM",
                "RPTINSTNCID") 
            )
            ACTVIND = "Y"
        )
    }
,
Sort(
      AddColumns(wStart, "RPTINSTNCIDTXT", Text(RPTINSTNCID)),
      RPTINSTNCIDTXT
    )
)

Best regards,

[jjacquet]

@v-jefferni -- That was a clever approach, thank you.

I'm receiving a delegation warning regarding the Search, since RPTINSTNCID is a numeric field in the source.

This is where I wanted to pass through a numeric conversion of Self.SearchText, but needed to do error handling at this level.

Short of the workaround above (which is working for now) or a change on the source data side, I'm not sure there's another elegant solution.