I have a simple table in Base, including a field called ‘Keywords’. In this field I have keywords like ‘RED’ ‘GREEN’ and ‘BLUE’.
Sometimes just one of them, sometimes more like ‘RED BLUE’ or ‘GREEN BLUE RED’
I have a form where I can edit existing records or add new ones.
Sometimes I want to work with all the records with ‘RED’ in their ‘Keywords’ field. Or ‘GREEN’. Or ‘BLUE’.
I would like to have some buttons on my form to be able to filter all the records where the ‘Keywords’ field contains ‘RED’ or ‘GREEN’ or ‘BLUE’ - with one mouse click instead of manually entering the filtering criteria every time.
Is there a way to apply different filters with buttons?
Thanks.
If a set of colours is associated to a set of items, so that each item can have many colours and each colour may belong to many items, this is called a many-to-many relationship in database terms.
Demo with persons and teams: many2many.odb (50.6 KB)
See menu:Tools>Relationships…
- A list of persons with auto-ID
- A list of teams with auto-ID
- A 3rd table mapping the IDs of persons to teams. The 2 columns are foreign keys to each list and a combined primary key at the same time.
Both forms have one of the lists as main form and the mapping table as subform.
The subform consists of a grid control with a column of listboxes reflecting the content of the other list.
Since the mapping table is empty, there is no person belonging to any team yet. Just open a form and add teams to persons or vice versa.
With your current setup (lists of colour names in one field), you can filter by “Keywords” LIKE %BLUE% (for instance in the form based filter. The column should be VARCHAR_IGNORECASE to avoid case-sensitive comparison.
Thanks.
To be honest, I don’t want to make it more complex with extra tables, subforms, etc.
I can use the filters now by manually entering the search criteria every time, it would be a quality-of-life feature to be able to filter with buttons.
Is there any way to do it? With macros maybe?
To be honest, I don’t want to make it more complex with extra buttons, macros and endless explanations how to implement all this.
Very helpful and kind, thank you very much.
It is always a pleasure to find such a friendly community.