Jump to the row or line of a selection list

[Renel]

Hi all!

I was told that through ChatGPT, you can chat with God and Jesus.
After a number of questions, ChatGPT recommended that I discuss with a discussion group in order to find answers to my questions.

So here I am asking for your help!

I have a form named “Flowers”. This is associated with a table named “TFlowers” containing several data fields including one named “FlowerNames”. The form is sorted taking into account the alphabetical order of this field which contains several hundred records.

This form also contains a table control (or GridControl, I don’t know if the name is right) and this one contains a column of type ‘Text box’ which is associated with the field “FlowerNames”. The sorting of the form is reflected in this list. This column serves as a picklist for quickly accessing a record in the form.

I would like to improve the function of this selection list. Given the large number of records, I sometimes have to scroll the list for a while before reaching the selection and suddenly the record. So, with the help of a drop down list which would contain the letters of the alphabet, I would like to be able to select a letter, say “L” and that automatically the first line of the select list whose first word starts with “L” would be selected and then this record would be displayed. I don’t want to filter this list by alphabetical letter, although that might be interesting, but I just want to make scrolling easier and improve accessibility to selection.

Does this require the use of a Macro (Basic) or could an SQL command do the trick. Can you help me to implement this command? THANKS!

[Villeroy]

A database can hold billions of rows, so a “jump to” approach is not feasible. Use filters instead.
For small record sets there is a search function which actually follows a “jump to” approach (first icon on toolbar) and there are various filter methods, built-in ones and ones that are based on a form-subform approach.
https://ask.libreoffice.org/uploads/short-url/eYwLPHOoYKfQlnafn0MmP65UygQ.odb

[Renel]

@Villeroy!

It is true that the search function allows access to DB records without too much difficulty. But, it doesn’t really correspond to what I was describing in my question, because searching only with the letter M gives me access to all records whose name has an M. It’s a bit laborious.

I opened the “Filter Data 1” form of your DB. The Filter C1 reacts in the way that would meet my needs.

You use a button to refresh the form, I imagine that if I used a drop-down list to select a letter and that via an event it executed a macro (basic) to refresh the form, I would arrive at the same result. Is it correct!

[Villeroy]

have a look at the other filter form.

[Renel]

@Villeroy,

I took a look at the other filters of the various forms and indeed the possibilities of filtering via SQL are rather impressive. I’m sure it will come in handy eventually.
.
Even though the filtering system is probably faster with SQL rather than using macros, I managed to solve my problem and create a macro that acts as a sort of “jump to” function. As expected, the macro is executed via a drop-down list or the user selects a letter and the procedure finds the first recurrence matching that letter and accesses it.
.
Here is the basic code:
.

Sub SetNameID(oEvent As Object)
    Dim oForm As Object, oResultSet As Object, oModel As Object
    Dim sSQL As String, sFormName As String, sLetter As String
    Dim aNoms() As String ' Création d'un tableau pour stocker les noms des acteurs
    Dim aIDs() As Integer ' Création d'un tableau pour stocker les IDs des acteurs
    Dim i As Integer, j As Integer, iID As Integer
    
    oModel = oEvent.Source.Model
    oForm = oModel.Parent    				' Cueillette du formulaire
    sFormName = oForm.Name    				' Cueillette du nom du formulaire
    sLetter = oEvent.Source.SelectedItem	' Cueillette de la lettre sélectionnée dans la liste déroulante
    
    ' Construction de la requête SQL pour extraire les données de la table selon le formulaire
    If sFormName = "frm-FilmsSeries" Then
        sSQL = "SELECT ""TitreAnglais1"", ""FilmSerieID"" FROM ""TFilmsSeries"" ORDER BY ""TitreAnglais1"" ASC"
    ElseIf sFormName = "frm-Acteurs" Then
        sSQL = "SELECT ""NomActeur1"", ""ActeurID"" FROM ""TActeurs"" ORDER BY ""NomActeur1"" ASC"
    ElseIf sFormName = "frm-Actrices" Then
        sSQL = "SELECT ""NomActrice1"", ""ActriceID"" FROM ""TActrices"" ORDER BY ""NomActrice1"" ASC"
    End If
    
    oResultSet = oForm.ActiveConnection.createStatement().executeQuery(sSQL)    ' Exécutio de la requête SQL
    
    ' Itération sur les enregistrements résultants
    i = 0
    Do While oResultSet.next()
        ReDim Preserve aNoms(i)				' Redimensionner le tableau et ajoute le nom
        ReDim Preserve aIDs(i)				' Redimensionner le tableau et ajoute l'ID
        aNoms(i) = oResultSet.getString(1) 	' Champ correspondant aux "Noms"
        aIDs(i) = oResultSet.getInt(2)    	' Champ correspondant Aux "IDs"
        i = i + 1
    Loop
    
    oResultSet.close()    ' Fermeture du résultat de la requête
    
    ' Boucle permettant de rechercher un nom qui commence par la lettre sélectionnée (sLetter) dans le tableau (aNoms).
    ' Lorsque le nom est trouvé, l'ID correspondant est stocké dans iID, et la boucle est interrompue.
    For j = LBound(aNoms) To UBound(aNoms)
        If Left(aNoms(j), 1) = sLetter Then
            iID = aIDs(j)
            Exit For
        End If
    Next j
    
    ' Si aucun nom ne correspond à la lettre sélectionnée, iID reste à 0, aucun nom ne commence par cette lettre.
    If iID = 0 Then
        Beep
        MsgBox("Aucun nom dans le formulaire ne commence par la lettre " & sLetter & ".", 48, "Sélection")
        Exit Sub
    End If
    
    FindRecord(oForm, iID) 		' Permet d'afficher un enregistrement selon une recherche effectuée.
End Sub

.
This macro is used in others procedures.

Sub FindRecord(oForm As Object, iTarget As Integer)
	Dim iRowNumber As Integer
	Dim oColumn As Object, oResultSet As Object
	Dim sFormName As String

	sFormName = oForm.Name
	
	If sFormName = "frm-Acteurs" Then
		oResultSet = oForm.createResultSet
		oColumn = oResultSet.columns.getByName("ActeurID")
	ElseIf sFormName = "frm-Actrices" Then
		oResultSet = oForm.createResultSet
		oColumn = oResultSet.columns.getByName("ActriceID")
	Else
		oResultSet = oForm.createResultSet
		oColumn = oResultSet.columns.getByName("FilmSerieID")
	End If
		
	oResultSet.Absolute(1)
	
	Do
		If oColumn.getInt = iTarget Then
			iRowNumber = oResultSet.Row
			Exit Do
		End If
		oResultSet.Next
	Loop
	
	oForm.Reload()
	oForm.Absolute(iRowNumber)
End Sub

.
It may not be the most elegant of macros, but it works perfectly.
.
Thank you for giving me your time, it’s always appreciated.

[cpb]

EDIT: sorry replaced demo, wrong db was uploaded.
.
@Renel,
looking at your first post I have a suggestion.
I assume that you use hsqldb embedded.
.
this demo contains 1 table, 1 query (list box), 1 form and 1 small macro. the macro simply refreshes the data form.
as usual for this type of demo I used the table “tContinentCountryCapital”, the column “Capital” is the equivalent of your column “FlowerNames”.
.
you click on an upper case letter in a list box and the record pointer in the table control moves to the first item in the column “Capital” which begins with the selected value.
.
every letter in the list box appears at least once in the table i.e. no dummies.
the list box is not actually bound to a column but its bound field does carry a calculated value which is equivalent to a row number in the data form.
.
I am unsure as to how you use your form, my data form in which the control table resides is read only.
if your form is insert/update/delete enabled then the the list box will require a refresh following each action.
JumpToRow2.odb (24.7 KB)

[Renel]

Hello @cpb,
.
Yes, I use HSQLDB Embedded and my form is Insert/Update/Delete/ enabled.
.
Your demo does exactly what I managed to do using Basic macros, but with query (SQL) and a very simple macro. It seems much faster to me as an execution.
.
You mention that all the letters in the list box are present at least once as the first letters of a Capital. Am I to understand that this is a requirement for your query to work properly?
.
In my DB there are some letters which are in the list box which does not exist in the table field of the table used. A record whose first letter of a name that is not currently present in the table could possibly be added. The table updates as needed.
.
So if I understand correctly, if no name beginning with X was existing in the table, your query might not work or absolutely nothing would happen. What would happen?
.
Currently, the macro I use takes this fact into account and returns a message telling me that there is no name starting with the selected letter. If I could achieve the same result with an SQL query, it would probably be much more efficient than using macros.
.
Your example is still very convincing.

[cpb]

so you use embedded hsqldb, a very poor choice for many reasons.
.
the query is dynamic.
the letters in the list box are sourced from the table, if a letter is missing from the list then it is not in the table.
2 letters are missing from the list, ‘E’ and ‘X’ because the table contains zero capital cities beginning with E or X.
.
the thing to understand is that we are using row numbers.
sort order dictates row number, therefore it’s imperative that both the list box and form are sorted in an identical manner.
.
there are 204 rows in the form.
assuming that we enable the add/edit/delete options in the form then input ‘Europe’, Scotland’, ‘Edinburgh’.
the number of rows has increased by 1 and ‘Edinburgh’ now sits at row number 205.
if we now reload the form then ‘Edinburgh’ will occupy row number 64 and all those records at or above 64 will have moved down a row.
after reloading the form we must refresh the list box so that row numbers can be synchronized and ‘E’ is available for selection.
.
we can input/edit/delete any number of records but BEFORE reselecting a list box item we must reload the form and refresh the list box. I think that a tiny macro fired by a button would be the most practical way to do this.
.
hsqldb embedded lacks functions and contains many bugs, one of those bugs is to do with sorting.
we have 2 countries without capital cities, “Capital” is referenced as ‘no capital’ and ‘none’, both lower case.
even though they are both VARCHAR, embedded db both sorts and compares them as VARCHAR_IGNORECASE which places them with the Ns when all other databases position them after the Zs.
we do actually want to position them amongst the Ns but never the less should code in a generic manner.
we can get around this by using dummy fields in the query and the form can be sourced by SQL, remember synchronization is critical.
.
what I am proposing is extremely quick and efficient only the list box query requires computation.
in due course I will apply the required adjustments and upload the results.
.
@Renel
here you go:
JumpToRow3.odb (25.0 KB)

[Renel]

@cpb,
.
For now I’m working with hsqldb embedded, maybe I might consider something else. What would be the most functional and easily accessible and free alternatives to LibreOffice Base’s built-in HSQLDB engine?
.
Dynamic query, this way of doing is really very brilliant, having this type of query to manage a list box with a table as source, I would not have thought of that.
.
I have one last question, if the table field contained names beginning with a number or a symbol like ‘#’ or others, would the query behave the same way or could this cause errors?

[cpb]

@Renel ,
.

embedded databases are great for demos and sharing but will always be susceptible to sudden failure which may well result in total loss of data.
on my home pc I have PostgreSQL, Firebird 3.x server, HSQLDB 2.2.4 to 2.6.0 and MariaDB.
I do not rate Firebird it’s just not user friendly and some of the things I have always taken for granted are impossible or require tedious workarounds.
HSQLDB is easy to use and maintain with plenty of well tested, reliable functions but it lacks the window functions which any modern database should have.
I have not used MariaDB a great deal and lack the knowledge to give an informed opinion but it does deserve consideration.
PostgreSQL is mature, reliable and well maintained with loads of functions and delivers everything I require, it’s great and there are plenty of very good informative tutorials available online.
.
in truth the database you use is simply down to personal choice.

@Renel

would the query behave the same way

yes exactly the same way, you have the demo so why not try it? just add a few rows of data but bear in mind that it’s not usually a good idea to include special characters or spaces in fields, field names or table names etc.
.
my perception was that the objective was quick and easy navigation and that the list should contain up to 26 items i.e. the letters of the alphabet.
that’s the reason lower and upper case items are merged, having a large number of list items to choose from shatters our aims.

[Renel]

Thanks for everything @cpb, you are very knowledgeable and I really appreciate your help.