Filters in Libreoffice Base

DaviCaldas · June 17, 2021, 2:38pm

Hey, guys! how to filter the same field twice in form in Libreoffice Base?
I found a way to do it, but this way makes the application slow.

Ratslinger · June 17, 2021, 3:38pm

Hello,

Somewhat unclear. Please provide basic information (OS, DB used LO version), an example of what you mean and what method you already found.

DaviCaldas · June 17, 2021, 7:48pm

Operating system: Linux Mint 20.1 Ulyssa base: Ubuntu 20.04 focal
Database used: Built-in Firebird
Version LO: 7.1.4.2

I have a form that has a primary table “Diseases” and a secondary table “Symptoms”. I want to create two filter fields (symptom01 and symptom02) that, when filled in, show certain diseases (where the filter field ‘symptom02’ only looks for the diseases already filtered by the filter field 'symptom01). If the 2 filters are empty, all diseases will appear.

DaviCaldas · June 17, 2021, 7:49pm

This was the solution I found, but it slows down the form:

SELECT DISTINCT "3. Etiologia"."Etiologia", "3. Etiologia"."ID_Etiologia", "3. Etiologia"."ID_Doenca" FROM "3. Etiologia", "4. Sintomatologia", "4. Sintomatologia" "4. Sintomatologia_1", "Filtro" WHERE ( "3. Etiologia"."ID_Etiologia" = "4. Sintomatologia"."ID_Etiologia" AND "4. Sintomatologia"."ID_Etiologia" = "4. Sintomatologia_1"."ID_Etiologia" AND UPPER ("4. Sintomatologia"."Sintomatologia") LIKE UPPER ('%' || "Filtro"."Sintoma/Sinal" || '%') AND ( UPPER ("4. Sintomatologia_1"."Sintomatologia") LIKE UPPER ('%' || "Filtro"."OUTRO" || '%') OR "Filtro"."Sintoma/Sinal" IS NULL OR "Filtro"."OUTRO" IS NULL ) OR "Filtro"."Sintoma/Sinal" IS NULL ) ORDER BY "3. Etiologia"."Etiologia" ASC

Longi · June 17, 2021, 10:41pm

You can use two list box as filters or a form an a subform

DaviCaldas · June 18, 2021, 2:15am

Yes Longi, but I found combo boxes effective because of the option to insert a new record.

Ratslinger · June 17, 2021, 11:02pm

Hello,

First let me mention you should avoid spaces and punctuation (and probably numbers) in naming. Very confusing.

The query you have is taking long because it needs to read the copy of the table for each comparison. Don’t see reason for this ~~and not even certain the results are correct. My tests show an incorrect result but I also have different data and probably table structures.~~ Found my problem. Results appear OK.

There are many way to do this but with your tables try:

SELECT DISTINCT "3. Etiologia"."Etiologia",
       "3. Etiologia"."ID_Etiologia",
       "3. Etiologia"."ID_Doenca"
FROM "3. Etiologia",
     "4. Sintomatologia",
     "Filtro"
WHERE ("3. Etiologia"."ID_Etiologia" = "4. Sintomatologia"."ID_Etiologia"
   AND UPPER("4. Sintomatologia"."Sintomatologia") LIKE UPPER('%' || "Filtro"."Sintoma/Sinal" || '%')
   AND (UPPER("4. Sintomatologia"."Sintomatologia") LIKE UPPER('%' || "Filtro"."OUTRO" || '%')
    OR "Filtro"."Sintoma/Sinal" IS NULL OR "Filtro"."OUTRO" IS NULL)
     OR "Filtro"."Sintoma/Sinal" IS NULL)
ORDER BY "3. Etiologia"."Etiologia" ASC

DaviCaldas · June 18, 2021, 2:21am

Ratslinger works perfectly. I believe I can use this same logic to make more than 2 filters. Thank you very much!