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

IfError in Filter for SQL Server connection

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.

3 REPLIES 3
jjacquet
Frequent Visitor

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
    )
)
)

 

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,

Community Support Team _ Jeffer Ni

If this post helps, then please consider Accept it as the solution to help the other members find it.

jjacquet
Frequent Visitor

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

 

 

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Community Call Conversations

Introducing the Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

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