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

[rgarnett1955]

Hi

I have a text comments field in my sqLite database tables. The contents of the field are optional and so the fields are NULL unless they have at least a string of zero length in them i.e. ""

If you wish to use the search feature of b4xTables and you make the comments field searchable and high-lightable if records have a null in at least one of the searched fields nulls the program crashes in the b4xtable module in the setTextToCell function.

Could someone modify the code so that nulls are ignored by the search engine. The B4xTable displays the word "null" for empty items. This is very useful for displaying fields that optional.

Of course it is possible to replace the "null" entry with characters such as white space " ", or "empty" etc, but then the search engine may include these. Much better and faster if it just skips the entry and excludes it.

Best regards
Rob

 

[rgarnett1955]

Hi,

I have modified the b4XTable class to enable it to handle null text in the search engine. Not much to it see relevant code below:

'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

And

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

Perhaps you could consider this and use it as the basis for the mod I wish for in the library.

Best regards
Rob