Data filtering using a query that is editable in a form

English
#1

Hello clever people,
I would be grateful for you advice. As you will no doubt realise, I am a simple non-progammer trying to work out ways to make my life a little more organised.
I have tried to use page 198 of LibreOffice 4.0 Base Handbook (https://documentation.libreoffice.org/assets/Uploads/Documentation/en/BH4.0/PDF/BH40-BaseHandbook.pdf) to set up a form that enables me to select a filtered list of my appraisees according to the year I am appraising them. And to be able to edit the fields in the form.

The structure of the database is:
TblAppraisee - a table containing a list of people and their details. They have an annual appraisal by me for 3 consecutive years (eg 2021/22, 2022/23, 2023/4) and then they move on.
TblYrActive - a table with 3 fields: yearactiveID (an autocounter); appraiseeID (linked to TblAppraisee.appraiseeID); year (linked to TblYrList.yrID)
TblYrList - a table with 2 fields: yrID (an autocounter); Yr (a text field with values 2021/22, 2022/23 etc)
TblFilterYr - a table to do the filtering. Contains 2 fields: Filter_ID (a boolean field and the unique index); Yr_ID (holds the value of TblYrList.yrID)

An SQL query named “qryFilterAppreeByYr”:

SELECT “TblAppraisee”.* FROM “TblAppraisee”, “TblFilterYr”, “TblYrActive” WHERE “TblYrActive”.“appraiseeID” = “TblAppraisee”.“Appraisee_ID” AND “TblYrActive”.“year” = “TblFilterYr”.“Yr_ID”

A form that contains 2 forms, one with a drop down list of the fields of TblYrList and linked to TblFilterYr; the other a table linked to qryFilterAppreeByYr (this form also has a “Refresh form” button).

I assumed from what I had read online that providing all the fields in a query come from one table, the output would be editable. But although the filtering works in my form as it should do, the output in form linked to the query is not editable.

Any pointers are gratefully received.
Thank you.

Version: 7.2.5.2 (x64) / LibreOffice Community
Build ID: 499f9727c189e6ef3471021d6132d4c694f357e5
CPU threads: 4; OS: Windows 10.0 Build 19043; UI render: Skia/Raster; VCL: win
Locale: en-GB (en_GB); UI: en-GB
Calc: threaded

#2

Hello,

A couple of notes first. There is more current (although not much has changed) documentation. See → LibreOffice Base Guide

It is typically more helpful if you post a sample instead of images or text descriptions.

You failed to note that the SQL you provided produces an error:

Screenshot at 2022-02-06 13-05-35

That is because of comparing a link field to the filter text field.

Also you are trying to base you creation upon a very different example. Yours includes retrieving data through a link just for comparison purposes. Not in the Base documentation pointed to.

In the future please provide this essential information.

This SQL works (based upon the content of the post):

SELECT "TblAppraisee". *
FROM "TblAppraisee"
WHERE "Appraisee_ID" IN (SELECT "appraiseeID" "Selected"
                         FROM "TblYrActive"
                         WHERE "year" = (SELECT "yrID"
                                         FROM "TblYrList",
                                              "TblFilterYr"
                                         WHERE "Yr" = "Yr_ID"))

Note:
This is based upon HSQLDB embedded as the database used. Not noted in post.

#3

Hi Ratslinger,
Thank you very much for your comments. I’m very grateful for your time. Thank you for pointing me to the current Base Guide. And thank you for pointing out the correct etiquette for posting queries.
I would like to upload two versions of the database, one with my old SQL query and one with your updated query. Please could you let me know if the confidential rows I have deleted from the tables are recoverable once I have saved the database?
If there is a risk of recovering the confidential data I would rather not upload the databases. Suffice to say, I wasn’t getting an error pop up with the old SQL, but I had the following popup with the new SQL:

firebird_sdbc error:
*conversion error from string “2021/22”
caused by
‘isc_dsql_fetch’

Thank you again for your much appreciated help.

#4

Typically deleted information is just that - deleted. However, you have now uncovered another issue you failed to mention - using a Firebird (guessing embedded) database. Different databases will potentially require different SQL.
.
Will attempt to test with Firebird.

#5

Ratslinger, thank you for your advice regarding the deleted information. In my simple way I tend to be a bit suspicious that old data can be recovered. Here are the two databases.

Thank you again.
Appraisee Details_newSQL.odb (51.6 KB)
Appraisee Details_oldSQL.odb (51.5 KB)

#6

Hi again Ratslinger
I had a look at your SQL and I think worked out what you were advising. I have modified it to the following and it is producing the lists by year and the fields on the form are editable. Result!
Thank you very much for the pointer to what I was meant to be doing. I see you are a frequent and reliable contributor to this forum and I have learnt much from reading your advice. Do you have a coffee/beer collection pot please?
Thanks again!

SELECT “TblAppraisee”.* FROM “TblAppraisee” WHERE “Appraisee_ID” IN ( SELECT “appraiseeID” FROM “TblYrActive” WHERE “year” = ( SELECT “Yr_ID” FROM “TblFilterYr” ) )

#7

Ratslinger, you mentioned in your first post that you assumed I was using HSQLDB. I use Firebird in my Base databases because this is the default I am offered. Is HSQLDB a superior design?
Thank you for your advice.

#8

Not the HSQLDB embedded version offered. And your default is only because you have Experimental features enabled.

#9

It sounds like it is best for me to stick to Firebird. Unless there is different database back end that you recommend (I use Win10)?

#10

Choosing a database to use is entirely dependent upon the needs of the person implementing it.
.
Have examined the SQL fix. The issue was due to me using Yr instead of yrID as result of selected item. This is also the reason I generated the SQL error where may not have.

#11

Just keep in mind to mention it, when you ask for help. Firebird was integrated to replace HSQLDB sometime, and even set as the “new default”, but this was reverteted as there were several problems to solve. So most peple will assume HSQLDB, if you ask without giving your own information.

#13

Ratslinger, I noticed you had “Selected” in your SQL query. I took this out because I thought it did not seem relevant. Having it in or out makes no difference to the successful working of the form. Are you able to explain the purpose of “Selected” please?
Thank you for your advice.

#26

It is an alias. Had other intentions (avoid a possible conflict) when first writing this SQL and that changed as I progressed. Forgot to remove and as you noted it makes no difference for this purpose.

#12

SELECT “TblAppraisee”.* FROM “TblAppraisee”, “TblFilterYr”, “TblYrActive” WHERE “TblYrActive”.“appraiseeID” = “TblAppraisee”.“Appraisee_ID” AND “TblYrActive”.“year” = “TblFilterYr”.“Yr_ID”

There are 3 tables in the query: “TblAppraisee”, “TblFilterYr”, “TblYrActive”

But: There is only one table, which should be shown with all fields: “TblAppraisee”.*.

You will get an editable query. This will work when all primary keys of all tables are part of the shown fields. So try:

SELECT “TblAppraisee”.* , “TblFilterYr”.* , “TblYrActive”.* FROM “TblAppraisee”, “TblFilterYr”, “TblYrActive” WHERE “TblYrActive”.“appraiseeID” = “TblAppraisee”.“Appraisee_ID” AND “TblYrActive”.“year” = “TblFilterYr”.“Yr_ID”

By the way:
HSQLDB is a very old integrated database. It will work well with the GUI.
Firebird will offer more functions but the integration into LO is experimental at this moment. I prefer Firebird if I want to use functions like LIST or DATEADD.

#14

Thank you @RobertG , your SQL query works as well. It is interesting that two different SQL sentences can have the same output.

Regarding using Firebird I guess as I am successfully using it I may as well contnue to use it.

Thanks again.

#27

Appears to be unnecessary as it works without this in my example. Selecting all from all table can become burdensome if the other tables contain more data than presented here.

#28

There is only one table in your code, which is written in the direct SELECT: “TblAppraisee”. There are two subselects which couldn’t be shown as output of the query. The primary key of TblAppraisee is shown as output of the query.

You could also add subselects and correlated subselscts, which are shown as output. The main SELECT won’t change the possibilty to be editable, because it only interprets … FROM “TblAppraisee” WHERE … and expects only the primary key of “TblAppraisee”. Only the fields of this table will be editable in the query.

#30

From the post that is all that is wanted:

#15

https://ask.libreoffice.org/uploads/short-url/1aku0FLh18y7SXNaiucswNsIMhr.odb
This is based on a parameter query. Notice that the filtered record set is editable.
HSQL works way better than Thunderbird and it is very easy to convert the embedded database into a real one with a recent driver version.
Embedded databases are risky. Sooner or later the existence of the database depends on your backup strategy.

#16

It rather begs the question as to why Firebird was chosen as the successor to HSQLDB?

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