For situations looking up a code and description i want an exact code match to show in preference to a full text search.
Accordingly this is a first attempt which shows full text search matches until there is an exact code match if it exists.
It needs a tidy but seems to work.
Code:
Procedure SuggestionsFindFullText String sSearch tSuggestion[] ByRef aSuggestions
Boolean bContinue bCase bOldRelate bNeedsRelate
Integer iFile iColumn iDescriptionColumn iIndex iLen iRow iMax
Handle hoServer
String sDescription sValue sValueToSearch sLowSearch
Get Server to hoServer
Get Data_File to iFile
Get Data_Field to iColumn
If ((hoServer <> 0) and (iFile <> 0)) Begin
Get piMaxResults to iMax
Get pbCaseSensitive to bCase
Get piFindIndex to iIndex
If (iIndex <= 0) Begin
Get_Attribute DF_FIELD_INDEX of iFile iColumn to iIndex
End
Get No_Relate_State of hoServer to bOldRelate
Get DDOConstraintNeedsRelate of hoServer iIndex to bNeedsRelate
Set No_Relate_State of hoServer to (not(bNeedsRelate))
Move (Lowercase(sSearch)) to sLowSearch
Move True to bContinue
Move (Length(sSearch)) to iLen
Move 0 to iRow
Get piDescriptionField to iDescriptionColumn
// Find first record
Send Request_Read of hoServer FIRST_RECORD iFile iIndex
While (Found and (iRow < iMax))
Get_Field_Value iFile iColumn to sValue
Move (Trim(sValue)) to sValue
If (iDescriptionColumn) Begin
Get_Field_Value iFile iDescriptionColumn to sDescription
Move (Append(svalue,Trim(sDescription))) to sValueToSearch
End
If ((not(bCase) and Lowercase(sValue) = sLowSearch) or (bCase and sValue = sSearch)) Begin
// Test exact match first
// Clear all full text values
Move (ResizeArray(aSuggestions,0)) to aSuggestions
Move 0 to iRow
Move (SerializeRowID(GetRowID(iFile))) to aSuggestions[iRow].sRowId
Move (Trim(sValue)) to aSuggestions[iRow].aValues[0]
Move (Trim(sDescription)) to aSuggestions[iRow].aValues[1]
Else Begin
// Else full text search
If ((bCase and sValueToSearch contains sSearch) or (not(bCase) and Lowercase(sValueToSearch) contains sLowSearch)) Begin
Move (SerializeRowID(GetRowID(iFile))) to aSuggestions[iRow].sRowId
Move (Trim(sValue)) to aSuggestions[iRow].aValues[0]
If (sDescription) Begin
Move (Trim(sDescription)) to aSuggestions[iRow].aValues[1]
End
Increment iRow
End
End
Send Request_Read of hoServer GT iFile iIndex
Loop
Set No_Relate_State of hoServer to bOldRelate
End
End_Procedure