Wish B4xTable Search - Ignore null entries in fields when using the search feature.

'Builds the SQL query based on the current table state. You can use it to export the table filtered and sorted table data.
'Returns an array of objects. The first item is the query and the second item is the arguments.
Public Sub BuildQuery (WithPageLimit As Boolean) As Object()
    Dim SortColumn As String
    Dim sb As StringBuilder
    sb.Initialize
    sb.Append("SELECT rowid")
    For Each c As B4XTableColumn In VisibleColumns
        If c.ColumnType = COLUMN_TYPE_VOID Then Continue
        sb.Append(",").Append(c.SQLID)
        If c.InternalSortMode <> "" Then
            SortColumn = " ORDER BY " & c.SQLID & " " & c.InternalSortMode
        End If
    Next
    sb.Append(" FROM ").Append(SQLTableName).Append(" ")
    Dim args As List
    args.Initialize
    Dim TotalCount As Int
    If FilterText = "" Then
        TotalCount = CountAll
    Else
        For Each c As B4XTableColumn In VisibleColumns
            If c.Searchable Then
                If args.Size = 0 Then
                    sb.Append(" WHERE ")
                Else
                    sb.Append(" OR ")
                End If
                
                sb.Append("(").Append(c.SQLID).Append(" IS NOT NULL AND ").Append(c.SQLID).Append(" LIKE ?) ")
                
                If PrefixSearch Then
                    args.Add(FilterText & "%")
                Else
                    args.Add("%" & FilterText & "%")
                End If

            End If
        Next
        TotalCount = -1
    End If
    If SortColumn <> "" Then sb.Append(SortColumn)
    If WithPageLimit Then
        Dim limit As Int = mRowsPerPage
        If TotalCount < 0 Then limit = limit + 1
        sb.Append($" LIMIT ${mFirstRowIndex}, ${limit}"$)
    End If
    #if B4A
    Dim aargs(args.Size) As String
    For i = 0 To args.Size - 1
        aargs(i) = args.Get(i)
    Next
    #else
    Dim aargs As List = args
    #End If
    Return Array(sb.ToString, aargs, TotalCount)
End Sub

Private Sub SetTextToCell (Text As String, lbl As B4XView, Searchable As Boolean)
    If Searchable = False Or HighlightSearchResults = False Or Text = Null Then
        lbl.Text = Text
    Else
    #if B4J
        Dim parent As B4XView = lbl.Parent
        If parent.GetView(parent.NumberOfViews - 1).Tag = TextPaneTag Then
            parent.GetView(parent.NumberOfViews - 1).RemoveViewFromParent   
        End If
    #End If
        If FilterText = "" Then
            lbl.Text = Text
        Else
            Dim x As Int = Text.ToLowerCase.IndexOf(FilterText)
            If x = -1 Or (PrefixSearch And x > 0) Then
                lbl.Text = Text
                Return
            End If
        
        #if B4A or B4i
            Dim cs As CSBuilder
            cs.Initialize.Append(Text.SubString2(0, x)).Color(HighlightTextColor).Append(Text.SubString2(x, x + FilterText.Length)).Pop
            cs.Append(Text.SubString(x + FilterText.Length))
            #if B4A
            lbl.Text = cs
            #else if B4i
            Dim l As Label = lbl
            l.AttributedText = cs
            #End If
        End If
        #Else
            TextFlow.Reset
            If x > 0 Then
                TextFlow.Append(Text.SubString2(0, x)).SetColor(TextColor).SetFont(LabelsFont)
            End If
            TextFlow.Append(Text.SubString2(x, x + FilterText.Length)).SetColor(HighlightTextColor).SetFont(LabelsFont)
            If x + FilterText.Length < Text.Length Then
                TextFlow.Append(Text.SubString(x + FilterText.Length)).SetColor(TextColor).SetFont(LabelsFont)
            End If
            Dim TextPane As B4XView = TextFlow.CreateTextFlow
            TextPane.Tag = TextPaneTag
            lbl.Text = ""
            parent.AddView(TextPane, 0, parent.Height / 2 - 12, parent.Width, parent.Height / 2)
        End If
        #end if
    End If
End Sub