LOBA: Find the record number from an ID (or other query search)

[EasyTrieve]

In a form, I can see how to select a record using the LOBA (LibreOffice BAsic) absolute method, like this:

 form.absolute(record#)

It causes the form to move to the given record.

But how is the best way to get a record# from something like a ...WHERE ID=x search?

---

Here’s what I know so far:

1. The ROW_NUMBER() function, can be used like this:

   SELECT ROW_NUMBER() FROM table WHERE ID=2

Unfortunately, my version of LO, the latest, 5.2.3.3, does not seem to support this HSQL function.

2. I imagine I could run a query of all ID’s, load the results into an array, search the array for the ID, and use array index+1 as the row number. But this seems slow, awkward, and ignores the index.

3. I’m looking for something like findFirst() in Access. I did find findFirst() in LOBA, but it appears to be for text searches.

---

For example, say you have a simple table of cities, with two fields, ID and City, and you have a simple form, which shows only the city and nothing else. Then using only Basic, how can I cause the form to display a given city if I know the city’s ID?

[EasyTrieve]

EDIT: For 3) above, I meant FindFirst in Access, i.e. capitalized and without quotes. Not sure why I can sometimes edit my own question, and sometimes not. And at the moment, I can’t even edit my comments. Strange.

[mikekaganski]

Just a note: the proper name is StarBasic, no need to add another strange abbreviation that would be completely unsearchable.

[EasyTrieve]

@mikekaganski, I see your point, but just look at the LO menu, where they call it “LibreOiffice Basic”: Tools > Macros > Organize macros > LibreOffice Basic

[peterwt]

You don’t need to use a Basic routine.

Create a Form showing the ID and City. Make the Form source a parameter query where the parameter required is the ID. When this Form is opened it will prompt for the ID and when entered will show the data for only that ID.

EDITED 9/1/2017

@Ratslinger There is a small problen in the database you uploaded. In the macro DisplayUsingSQL the first record is not displayed. As you have moved to the first record before the “While oResult.next” line this moves to the second record before displaying the records. Inserting “oResult.previous” before the While then displays all the records.

@EasyTrieve I have uploaded a database example which does as you asked. The Form source is the Table. The Macro, run on clicking the button, first saves the entered ID in a variable. The Query has no WHWER clause so the result set contains the complete Table. The result set is searched for a match on the ID Field using the entered ID. On a match the Row is found and used to move the Form to that Record. If there is no match a message is displayed.

Your reference to the MS Access FindFirst() is part of the DAO in VBA. It is one of the rich features of Access which are not replicated in LO Base.

MoveForm.odb

EDITED 10/01/2017

@EasyTrieve There is a better and simpler way using a Form Filter.
Attach the macro below to the button in the Form in my sample.

Sub Findit(oEvent)
Dim inp As Integer
oForm = oEvent.Source.Model.Parent
inp = oForm.getByName("Input").Text
sFilter="""ID"" = " & inp
oForm.Filter=sFilter
oForm.ApplyFilter=True
oForm.reload
End Sub

[EasyTrieve]

@peterwt, thanks very much, but hold on a bit, I’ve got something that’s more to what I was looking for working. There is a much better way to do this I think. …I need a little more time to work out a smaller detail of it, and will share it with you when I get it complete. Thanks.

[EasyTrieve]

I finally found a solution and would have posted it here sooner, but for the fact that ask.LibreOffice.org would crash when I tried to add this answer, till now. (Think the bug in ask.LibreOffice.org is now finally fixed.)

---

LO is missing the native function to do this, but there is a workaround if the dataset is not too big. Here’s a working example that uses it. It goes like this:

Assuming once I have the ID I’m seeking, I SQL a simple table of just ID, that I can quickly search. This table needs to be sorted as in the underlying dataset (table or query). Then I loop thru it and once I find the ID I’m seeking execute oResults.getRow() to get the row number. Then I can use that row# to move to that record in the underlying data set with Absolute(row#).

As noted in the code, this solution uses a loop rather than a normal fast, indexed lookup function. (Perhaps someday LO can add a findFirst type of function to support this procedure using indexes.)

---

Again, see the answer to this other related question

[BlueBike]

Why don’ t you use acces2base ?
Access2Base is a LibreOffice/OpenOffice Basic library of macros for (business or personal) application developers and advanced users.
Their syntax and their meaning are directly inspired by MSAccess.