Macro to filter table column

English
, , ,
#1

Hi People,

Here is my TV Series database. At least an example of that, because the original one is hosted on a MariaDb Server.
series.odb (14.9 KB)

I would to use the given form to input de series data in the database.

Therefore, in the first form we type in the serie’s details (title, first aired, …)

In the second form, linked by the ‘seriesId’ to the first one, we input the crew member. (Actors, Directors, …)
The actors have ‘roleId = 2’

The third form is the casting. It is linked by the ‘seriesId’ and the ‘actorId’ to the ‘Crew’ form.
I tried this SQL query to display the actors in the ‘Actors’ column :

SELECT P.PERSONNAME, P.PERSONID FROM PERSON AS P JOIN CREW AS C ON C.PERSONID = P.PERSONID JOIN ROLE AS R ON R.ROLEID = C.ROLEID JOIN SERIES AS S ON S.SERIESID = C.SERIESID

But, the list displays the whole crew.

I would like to filter this list to display only the actors.

Therefore, I need a macro. But, I don’t know about macros.

Could someone help me please ?

#2

as I don’t see a WHERE-clause, there is nothing filtered.
.
Try to add a WHERE R.ROLEID=2 as first step to your SQL.
.
I don’t think a macro is really necessary to filter.
.
PS I didn’t check your file, as I’m using my mobile device today…

#3

Tested. With this, all actors from all series in the database are displayed.

#4

If you wish to narrow down the selected data you have to add more contitions with AND to the WHERE-clause.
.
Maybe check the chapter to filter data in the guide for Base:
https://books.libreoffice.org/en/BG73/BG7308-DatabaseTasks.html#toc6

Or for the complete guide:
https://books.libreoffice.org/en/BG73/BG73.html

#5

No Macro needed, just add
AND "R"."ROLETYPE" = 'Actor'
to the end of the Query.you already have.

#6

As I said before, with ‘ROLETYPE’ in the WHERE clause, It ends up with all the actors in the database. But I would only the actors of the current show to be displayed in the list.
The only way I know, were to add “AND S.SERIESID = :serid” in the WHERE clause, but, that only work with queries created in the query editor, not with forms.

I looked at Wanderer’s links, but I don’t see how I could use such table to filter the actors list, especially in a table control.

#7

You will need an additional table, you may call it FILTER to hold your values for the filter.

Maybe start here:
https://forum.openoffice.org/en/forum/viewtopic.php?t=98539

It works, if you use sub-forms on refresh. (One point, where a macro called “on change” is quite convenient, but having a button as “next” element works also nicely.)

#8

Hi,

What follows is a Form example either with direct dependency or with filtered one.

Do hope you’ll see the point…
Example of filtered cv information.odb (19.4 KB)

#9

Using the sample database you provided

Another small modification so your query reads

SELECT "P"."PERSONNAME", "P"."PERSONID", "R"."ROLEID", "S"."SERIESID" FROM "PERSON" AS "P", "CREW" AS "C", "ROLE" AS "R", "SERIES" AS "S" WHERE "P"."PERSONID" = "C"."PERSONID" AND "C"."ROLEID" = "R"."ROLEID" AND "C"."SERIESID" = "S"."SERIESID" AND "R"."ROLETYPE" = 'Actor'

Yes, the query displays all the Actors but Save the query so that it has a name.

Open your form in the Edit/Design mode.

Right click on the table control for the SubForm you call CrewForm

Select Form Properties from the list to open the Form Properties dialog

On the Data tab change Content Type from Table to Query

For Content choose your saved query from the list of queries

Save the Form document and return to Live Mode.

Because the query limits the selection exclusively to Actors and the Main and SubForms are linked by the "SERIESID”, when you select a Series on the Main Form only the Actors associated with that Series will be displayed on the SubFrom.

The Filter Table option recommended by others would result in a more flexible Form where the user can choose Actors or one of the other ROLES if they prefer.

#10

Thank you very much people for spending time on my problem, but nothing work for me.
I’m spending to much time for something useless.

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