Can I move specific record in a form in Libreoffice Base via Macro coding?

English
, , ,
#1

Hi, nice to meet you, everyone.
I have a question about moving records in Libreoffice Base Form.
I understand moving the first or last record.
eg. oForm.last()
Then How can I move to a specific record by specific field (eg. ID number)
I assume It will be

oForm.Columns.getbyname(“ID”).set = ID value What I want.
oForm.move

or

oForm.Columns.getbyname(“ID”).setvalue(D value What I want)

then succinctly moving to that place What I Want.

How can I moving to the place what I want to move via field value?

#2

For moving to a record you will need the row number, which is shown in navigation bar. This row number will depend on sorting an filtering the content of the table.

Please add the database you are using. There are some functions available in internal Firebird, which couldn’t be used in internal HSQLDB for getting this row number …

#3

Thank you for your kind answer.
What I am doing is. There is a key linked its own ID .
For example, I put purchase information.

[ID][Name][Price][original ID for refund]
…
…
13 | Apple | 20 |
…
yeh I purchased an apple for $20. then… next day
I do return it and get a refund. so…

14|Apple Retruned | -20| 13

[The last original ID for refund ] is the original purchase record number “13”
In the form. There is a read-only text box for that field.
and If I click [13] ← this I move to the 13 record originally saved.
I don’t think the row value is not fit for my work. but Thank you for the information.

PS.
I thought of a simple and dummy logic a few minutes ago. If I move to previous data. then compare the ID and the value 13 - What I get and want to go. if It fits. then stop the loop. Do you think it will work?
If possible, Do you show me like ‘oForm.previous()’ ← this function?

#4

Would it always be the previous row?
I would prefer to look at the row number and move to this row number depending on the ID you have chosen:

SUB moveToRow(oEvent AS OBJECT)
oField = oEvent.Source.Model
oForm = oField.parent 'if tablecontrol add another "parent"
inValue = oField.CurrentValue
oConnection = oForm.activeConnection()
oSQL_Statement = oConnection.createStatement()
stSql = "SELECT COUNT( ""ID"" ) FROM ("+oForm.ActiveCommand+") WHERE ""ID"" <= "+inValue
oResult = oSQL_Statement.executeQuery(stSql)
oResult.Next
inRow = oResult.getInt(1)
oForm.absolute(inRow)
END SUB

This will only work for a form ordered by ID. Macro should be set to the field “original ID for refund”.
Again the question: Which database engine do you use?

#5

Thank you again. your code is helpful to my logic.
so absolute is the function right?
I’ll use this. I appreciate.
and I don’t exactly know. I’m a mere newbie on Libreoffice Base. I just have studied for 1 month and a half. Is there any additional database engine not standard one I’m using from the first?

#6

If you set experimental features in LO to “on”, there is Firebird available. You could create a Firebird database and set experimental features to “off” after creating this kind of database. With Firebird this code is possible:

SELECT "ID",
 ROW_NUMBER() OVER (ORDER BY "ID") AS "RowNumb"
 FROM "Table" AS "a"

So you could get all row numbers. You have only to set the sorting order as it is set in oForm.ActiveCommand.

1 Like
#8

Thank you so much for additional information.
I don’t know details about it yet. I’ll google it and try it.

#12

I would try LibreOffice: XRowLocate Interface Reference

#7

Is there any additional database engine not standard one I’m using from the first?

Embedded Base databases, whether HSQL or Firebird store everything (tables, queries, forms, reports, macros plus a copy of the database engine) in a zip archive with the .odb extension. That is a handy format and a good learning tool, but if anything interrupts the zip process (often an impatient laptop user who closes the laptop while the database is in the process of being saved) the .odb file can become corrupt and the tables lost. Frequent backups are highly recommended when using an Embedded database.

The potential for data loss can be minimized by using a JDBC connection (sometimes called a split database) where the tables and database engine are stored outside of the .odb file. This also allows newer versions of the database engine to be easily installed.

[Wizard] Create a new ‘split’ HSQL 2.x database

The downsides are

In addition to the .odb file the database is composed of several other files in a directory with two sub-directories

Once created, tables cannot be edited with the Base table GUI but they can be edited with SQL commands. After becoming familiar with using SQL and armed with a handful of skeleton tables and other commands I’ve found that creating and editing the tables with SQL is usually easier than using the GUI.

The HSQL database engine is a mature and and well maintained product that closely adheres to the ISO standard for SQL. Various versions can be downloaded from https://sourceforge.net/projects/hsqldb/files/hsqldb/

1 Like
#9

I appreciate concise information. I also have thought about using SQL accessible with other languages. not only Libreoffice base. Thank you very much.

#10

One of the reasons, why I’m using LibreOffice is not being locked. I’m using with Base

  • Sqlite via odbc on Windows ( most advantages of SQL, single “portable” file and accessible via Python, a direct CLI and programs like SqliteStudio

  • MariaDB on NAS ( local also possible ) or on rented Webspace. Also accessible via HeidiSQL and other tools working with MySQL/ MariaDB

  • Embedded HSQLDB for smaller project sent by mail or synced folder, as most users of LO can access this without help. Usually not accessible outside of LibreOffice, but could be converted to split HSQLDB.

  • Archived dbase and csv-files can be accessed and queried via ( limited ) SQL, after one accepted to connect to the folder with the .dbf or .csv-files as database. The files in the folder are tables of this database.

I recommend to check on the guide for Base at English documentation | LibreOffice Documentation - LibreOffice User Guides

1 Like
#11

What a precise explanation. Thanks a lot. I’m not so familiar with coding yet. I’ll find another DB system you mentioned step by step.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.