Filter subform control tables

[Renel]

In my database, I have a form, say ‘Movies’, which I can consider as the main database form is associated with the ‘TFilms’ table. All the fields of this table are represented in the main form ‘frm-FilmsMain’ in order to enter and catalog information related to a film. This main form contains a control table called “cntr-Selection” allowing you to select the title of a film from a list in order to quickly access its recording. The main form contains two sub-forms “sfrm-Actors” and “sfrm-Actresses” also containing a control table (“cntr-SelectionActrices” and “cntr-Selectionacteurs”) allowing, in a first column, to select and catalog the name of all the actors and actresses who participated in the film for the current registration. Also, the type of role played by them in a film appears in a second column of the control table for each actor or actress. The role type cannot be selected in this column. The selection of this must be done in the ‘Actors’ or Actresses’ forms, at least for now.

I would like to be able to filter the control tables (“cntr-SelectionActrices” and “cntr-Selectionacteurs”) of the subforms “sfrm-Actresses” and “sfrm-acteurs” of the main form “frm-FilmsMain” using 'a drop-down list that would contain the following list of roles to select: Main, Secondary, Cameo, Guest and All roles. The purpose of this filtering is to display only the names of the actors and actresses of the film corresponding to the role selected in the list. This filtering must be executed or active for all the records. I would also like a single select list to be able to control the filtering of these two control tables.

Could someone guide me or give me some solutions to achieve this task? Can SQL alone suffice to achieve this filtering or do I need to resort to designing Macros? I am familiar with LibreOffice Basic.

Thank you for your attention to this request! I can provide more information if needed.

[RobertG]

You will need a query/view, which contains all the data: Data from main form and data from all subforms. This query (better: view) you could use for setting a filter. This filter will show all primary keys for main form, which could be shown.

Will work without any macro, because you could use this view as main form and the current used main form will be the subform. The 2 existing subforms are now subsubforms.

[cpb]

@Renel,
.
it’s not easy to explain the required steps using text.
you really need to use one or two little tricks for this.
data-source for cast member forms are queries (not tables).
a filter table is used to store the chosen role.
a small macro is used to update cast member forms when list box value is changed.
.
I am not a film buff so have kept things simple.
give the attachment a try, hopefully it will shed some light.
the form contains no embedded SQL (I prefer queries).
Roles.odb (16.6 KB)

[Renel]

@cpb,

I opened your DB Roles.odb to look at your proposal and it looks like exactly what I was looking for. I can also notice that your suggestion is different from the one proposed by @RobertG. You are using a new form located at the same level as the “frm-FilmsMain” form to exercise fisting rather than nesting my current main form in a new form in order to exercise filtering.

I guess both approaches are okay as long as the result is satisfactory. However, I chose your approach. I analyze the content of your example in order to be able to insert it and use it in my database.

Thank you for your solution!

[Renel]

I seem to be having a bit of trouble with queries that throw errors that the command is not a SELECT statement. Could this error be related to the relational structure of my DB? Here is the diagram of the existing relations in my DB.

[cpb]

@Renel,
.
queries beginning ‘qf’ are data-source for internal forms and must be saved in parsed mode.
queries beginning ‘ql’ are used by list boxes, all are saved in direct mode.
I suspect the error is thrown by the SQL from the query ‘ql_RolesWithAll’, this is the only query which must be saved using direct mode.
.
I suggest using direct mode for all list box queries.
right click your list box queries, select ‘Edit in SQL View’, activate the SQL icon on the toolbar and save.
.
any problems, copy the SQL and post it.
.
EDIT:
if you embed your list box SQL in the form then ‘type of contents’ should be SQL[Native].

[Renel]

Hello @cpb,

I still encounter the same error and can’t figure out what could be causing the problem.

I post the DB in which I want to add this filter. Maybe it will be easier for you to help me on this.
FilterRoles Example Test.odb (481.9 KB)

This DB is a simplified example reduced to five records for the “FilmsSeries” form. It retains the main fields. Some Macro-related features might not work given this relief.

[cpb]

this seems to run ok, try ‘fForm’.
.
libreoffice base adds a null string item to a list box when the bound field is set nullable in the source table so set these constraints:
“TRoles”.“Role” set not null.
“JTActeursFS”.“jIDRole” set not null.
“JTActricesFS”.“jIDRole” set not null.
.
added my form and queries with updated SQL where necessary to complement your db.
added ‘Module1’ to standard.
ExampleFixed.odb (491.9 KB)

[Renel]

Thanks @cpb,
.
I’m analyzing all of this and I’ll get back to you later today about it. Thank you for your time and generosity.

[cpb]

the bugs in the form editor are a nightmare.
some of this stuff is likely new to you, it will take time to understand, absorb and integrate so I add ‘WithUsersForm.odb’.
it includes a copy of your form ‘FilmsSeries’ named ‘FilmsSeriesCopy’ and works with my queries.
I also updated the queries ‘qf_Acteurs’ and ‘qf_Actrices’ in order to incorporate your sort order i.e. replace(“NomActeur”,’ ‘,’.’) and replace(“NomActrice”,’ ‘,’.’) respectively.
.
EDIT:
replaced original attachment because I stupidly overwrote a list box.
WithUsersForm2.odb (543.9 KB)

[Renel]

@cpb,

There is no doubt that your approach is the right one, but so far things are not working very well. With your fFom form everything works perfectly, but when trying to replicate your work in my “FilmsSeries” form a lot of errors are generated and this is directly related to the sub-forms and the queries linked to them. I’m trying to figure out what’s wrong with SQL statements, but it’s not straightforward.

I’ll see with your “WithUsersForm” file if that helps. I do not despair, far from it, your help is invaluable. I’ll let you know soon.

[cpb]

really sorry! I stupidly hijacked your categories list box to show roles.
please delete ‘WithUsersForm.odb’ and download ‘WithUsersForm2.odb’ from my previous post.
.
the issues you are experiencing are the result of:
in your initial post you named the inner forms as ‘frm-FilmsMain’, ‘sfrm-Actors’ and ‘sfrm-Actresses’, I therefore used these names in ‘Roles.odb’.
in your uploaded db those same forms are named as ‘frm-FilmsSeries’, ‘sfrm-Acteurs’ and ‘sfrm-Actrices’.
my form ‘fForm’ and the macro referenced the initial form names.
your db needs to use my queries and macro but in order for them to function properly all references to the inner form names have to be updated.
.
in my most recent upload ‘WithUsersForm2.odb’ I added the form ‘FilmsSeriesCopy’ (a copy of your form), I made all necessary alterations and it appears to work flawlessly.

[Renel]

@cpb,
.
Now things are working perfectly. I was able to make the necessary changes in the forms and sub-forms of my database as well as make all the appropriate assignments according to the indications and requests that you proposed to me.
.
One of the major errors that were occurring were caused by the SQL statement - replace(“ActorName”,’ ‘,’.’) and replace(“ActorName”,’ ‘,’.’) - which I forgot to remove from the Filter field of the data tab of the subforms. The fact that this command was already present in the queries qf_Actors and qf_Actresses, the existence of this command present in both places at the same time posed a real problem.
.
I am very grateful for the work you have done. Everything is now resolved. Again, thank you very much!
.
Rene_L