Not necessary. Macros can be in the .odb and the file is placed in a folder wich is labeled as a safe place.
.
I wrote “you”, because the same happens on your system. Assume my photo-database is in folder EXIF, then I can register this folder as trusted place and will not be asked before starting macros from there (but I have also to set the security level accordingly). If I wish to distribute my database, a similiar setting needs to be done once on the system of every user.
.
Maybe check the settings yourself (start at Tools>Options and go to security, then macro-security)
https://help.libreoffice.org/latest/en-US/text/shared/optionen/macrosecurity.html
I could not resist, to add a lengthy comment. Following this video, you can’t get a satisfying filter form.
Ok, got it.
I’m not interested in telling other people to deal with security warnings, so I’m going to stay away from macros for now. And I think I can do what I need without them. But I appreciate your clarification in any case. Thanks.
IMHO, creating usable database solutions for “other people” requires a sane use of macro code.
1 Like
I managed to get the form to work with a refresh button, following the all the help here and the Bike demo db. I’m not using any macros, only a refresh button.
The only problem now is that I need to press the refresh button twice to actually see updated data in the Subform. The form still takes time to get the data, but it is still assuming the previous selected value in the List box, not the new / current one.
This doesn’t happen in the Bike demo even after messing around with it for a bit.
I checked after making a selection in the List box that the filter table field is indeed updated.
If I for example make a selection in the List box, press the refresh button, make another selection and press the refresh button again, I get the data corresponding to the first selection. If I continue like this the refresh button always shows info from the previous selection, not the current one.
What could be the reason for this and how do I fix it?
Hum… what video and what lengthy comment? I don’t see any… in any case your help is very much appreciated and I thank you for your patience, as well as the other people offering help as well.
the one below https://www.youtube.com/watch?si=j7O8XqtM1kKSbA32&v=Jy9CV1yD7ew&feature=youtu.be
Not reproducible.
PowerFilter_AutoRefresh.odb (240.4 KB)
The first form, which you already know, updates properly.
The second form has some macro support. It updates on Enter key, and you can easily add more items without refreshing each listbox manually.
I was using the ‘Repeatable: yes’ setting for the button. At the very start of me trying to figure out how this works, the buttons would work once and then become inactive, so I changed this setting and never changed it again. I went back to the bike example and compared the settings, that’s how I noticed this difference.
A save button becomes inactive after a record has been saved. It becomes active again when the current recotd has been modified. Same with a cancel button (action: “Undo edit”). A refresh button is always active. If you refresh while there are unsaved data, the application prompts for saving before refresh, not saving or cancel refresh.
The OK button on my first form document refreshes the subform where it belongs to. When you click on it, it takes away the focus from the main form. Taking away the focus from the main form to the subform automatically saves any unsaved main form data before the refresh action is executed on the subform.
The OK button on my second form document (the one with macro support) saves the main form where it belongs to. The save action triggers a macro that refreshes the subform. The user doesn’t need to grab the mouse.
The form navigator (menu:Form>Navigator) reveals the hierarchy of forms, subforms and controls. I started the macro form as a copy of the initial form document. In the form navigator, I dragged the OK button from the subform to the main form and changed button properties “take focus” from yes to no and the action from “refresh” to “save”. A hidden control named “AutoRefresh” (visible in the navigator only) stores the macro configuration. A form event triggers the macro. The 2 additional forms adding new items are set up in the same way with a different configuration in the hidden field.
Quick recipe for “power filtering” by 4 optional criteria ignoring empty criteria values.
Create a filter table “FLT” with a bunch of various columns.
CREATE TABLE "FLT"(ID SMALLINT PRIMARY KEY, DESCR VARCHAR(100), TXT1 AS VARCHAR(100), TXT1 AS VARCHAR(100), D1 AS DATE, D2 AS DATE, T1 AS TIME, T2 AS TIME, DEC1 DECIMAL(6,2), DEC2 DECIMAL(6,2), F1 FLOAT, F2 FLOAT)
Just enough columns to compose many different “power filter” forms in the same database. Therefore, I use generic column names. Since only one row is loaded at a time, there is no reasonable column limit. The filter form is a little bit like a spreadsheet range, addressing single values by row number and column name.
Insert a first record with primary key and description:
INSERT INTO F (ID, DESCR) VALUES (0,'Criteria set for employees form in row #0')
Properties of the filtering main form on the “Data” tab:
- SQL command
SELECT * FROM "FLT" WHERE "ID"=0 - Analyse SQL: Yes
- Disallow everything except modification.
- No navigation toolbar.
With this setup, the user can’t tab into any record other than the one with ID=0. Tabbing into some other record creates trouble and confusion.
Form controls for optional criteria have “Input required”=No.
Add a cancel button (action: “Undo data entry”) to the fitering main form.
Filtered form for editable records from a single table “Employees”, filtered by optional parameters which are substituted by the parent form.
SELECT * FROM "Employees"
WHERE ("BirthDate">= :paramDate1 OR :paramDate1 IS NULL)
AND ("BirthDate" <= :paramDate2 OR :paramDate2 IS NULL)
AND ("FK_Grade" = :paramINT1 OR :paramINT1 IS NULL)
AND (LOWER("Surname") LIKE LOWER('%' || :paramTXT || '%') OR :paramTXT IS NULL)
With this query, we can search for a range birth dates, a grade, and we can search case-insensitively for parts of the surname. Missing values are ignored.
Mapping of master/slave in the subform properties:
D1 → paramDate1
D2 → paramDate2
INT1 → paramINT1
TXT1 → paramTXT
Foreign key “FK_Grade” is a listbox control with a source like SELECT "NAME","ID" FROM "GRADES" ORDER BY "NAME".
Add a refresh button to the filtered form.
For a report or a form where you don’t need to edit filtered data, you may use this similar query:
SELECT "Employees".* FROM "Employees","FLT"
WHERE "FLT"."ID"=0
AND ("BirthDate">= "FLT"."D1" OR "FLT"."D1" IS NULL)
AND ("BirthDate" <= "FLT"."D2" OR "FLT"."D2" IS NULL)
AND ("FK_Grade" = "FLT"."INT1" OR "FLT"."INT1" IS NULL)
AND ("Surname" LIKE LOWER('%' || "FLT"."TXT1" || '%') OR "FLT"."TXT1" IS NULL)
For an aggregation of filtered record sets, replace "Employees".* with SUM("Wage")AS "Total", AVG("Wage") As "Average"), MIN("Wage") AS "Minimum Wage"), MAX("Wage") AS "Max Wage")
When using the still experimental embedded Firebird database, you have to write optional critria like this
("FLT"."D1" IS NULL OR "BirthDate">= "FLT"."D1")
instead of
("BirthDate">= "FLT"."D1" OR "FLT"."D1" IS NULL)
Further filter forms start with the same filter table and a filter record like this:
INSERT INTO F (ID, DESCR) VALUES (1,'Criteria set for table "Services" form in row #1')