Specific search macros

English
,
#1

Hello, I used to use Microsoft Access. Recently, I switched to LibreOffice, but the problem is that I don’t know anything about LibreOffice macros. (In Access, the macros and buttons were predefined).
I would like to know how to create macros in the LibreOffice database that search for specific text in a given field.

Let me explain:
I have a form with several records (from a query) containing various fields (see image No. 1), including a ‘COUNTRY’ field and a ‘SUPPORT’ field. I would like to create different buttons that will search the ‘COUNTRY’ field for the text FRA for the first button, the text DEU for the second button, the text ESP for the third button, etc. And another series of buttons that searches the ‘SUPPORT’ field for the text CD for one button, the text LP for another button, the text CDm for another…

For your information, I definitely do not want to create a filter, just a search for specific text in a specific field.

Image N °1

Image N°1
Image N°11218×670 140 KB

#2

Ok. But what shall your macro do after finding something?
Obviously not showing a list, because that would be a filter…
.
The actual “search” would be passing a sql-query to the database.
Someting like

SELECT Id from SearchedTable WHERE "COUNTRY"='FRA'

.
Another question is, what to do with more than one hit in the database. (Database have not necessarily a “natural” order unless you assume ordered by Id as default.)

You could start with the guide for Base, available here:
https://documentation.libreoffice.org/en/english-documentation/

#3

I think this should follow along this line:

  1. There is somewhere instructions from @Villeroy on how to pass parameter to code via button.
    So for the button FRA your “parameter” would be the SELECT SUPPORT, […] FROM SearchedTable WHERE COUNTRY = ‘FRA’;
    as said by @Wanderer
  2. For the button CD parameter would set the Filter property of the reloaded Form after (1).
    And of course in this code also
    oForm.reload
#4

I see roughly what you’re getting at, but I feel like the formula mixes the two types of search.
Let me summarise.

Given that the form is sorted by type of support and then by country, my initial search is: instead of using the search magnifying glass by typing the search text in the “SUPPORT” field each time and then using the magnifying glass again to search for text in the “PAYS” field,

This first button, named, for example, ‘LP’, which would search for the first text “LP” in the “SUPPORT” field, would have the following formula: SELECT SUPPORT, […] FROM SearchedTable WHERE SUPPORT = ‘LP’

Then, for the country, the button named, for example, ‘FRA’, which would search for the first text “FRA” in the “PAYS” field, would have the formula: SELECT PAYS, […] FROM SearchedTable WHERE PAYS = ‘FRA’

If this is indeed the type of formula, I need to figure out how to create it in a macro so that I can apply it in a form [FORM_ENTRY].

I hope my English is understandable. :grimacing:

#5

So just the reverse:

  1. 1st. serie of buttons “parameter” will be the SELECT […] WHERE SUPPORT = ‘LP’ (and the other choices in the other buttons of course).
  2. For the buttons PAYS just the names or codes of them: France or FRA… as per your design.
    Then just set the form’s .filter property.
    No SELECT.
    oForm.filter = […]
    But I’m on travel for now, just with the phone. So unable to help you with concrete examples.
    But people like @Villeroy @RobertG […] will show you.
     
    EDIT
    By the way I think a much more elegant solution would be 2 ListBoxes.
#6

PowerFiltering.odb (305.3 KB)

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