Filtering of Fields

[BasilVSA]

I have 4 fields in a table that I use on a form via list boxes. The fields are named as follows:

Table Name = List box Name
1 Standard = lst_Standard
2 Part = lst_part
3 Test Level = lst_test_level
4 Pass Criteria = lst_pass_criteria

The table used to select the values is tbl_pass_criteria_01 and has all the records for each Standard, Part, Test Level, Pass Criteria in it i.e each record has all 4 fields populated.
Each list box selection stores the selected value in the table named field in the table tbl_Input_01.

Field 1 is independent and the value selected is unique
Field 2 is DEPENDENT/FILTERED on the value in Field 1
Field 3 is DEPENDENT/FILTERED on the value in Field 2 and
Field 4 is DEPENDENT/FILTERED on the value in Field 3

How can I set up fields 2 - 4 so that values for selection are filtered as per the value in the previous field?

Any assistance in this regard will be appreciated.

[Villeroy]

viewtopic.php?f=83&t=46470&p=214680
viewtopic.php?f=100&t=94393&p=449618

[BasilVSA]

Hi, Villeroy, I'm not sure how the macros are supposed to work and they seem complicated to me. If I use the macro as copied and pasted it fails. Obviously I need to make some changes to field names, tables, etc. but it doesn't work for a beginner like me.

I have tried the following macro and it seems to work partially:

Sub frm_Input_02_Standard_Change

oForm1 = ThisComponent.DrawPage.Forms.getByName ("frm_Input_02")
oField = oForm1.getByName ("lst_Standard")
sSelectedValue = oField.SelectedValue
sSql = "SELECT DISTINCT ""Part or Clause"" FROM ""tbl_pass_criteria_01"" WHERE ""Standard"" = '" & sSelectedValue &"' ORDER BY ""Standard"" ASC"
oField = oForm1.getByName ("Part or Clause")
oField.listsource = array(sSql)
oField.refresh()

End Sub

Not sure why but the value filtered and selected does not remain captured in the 2nd field and using the same macro in the remaining fields therefore doen't work either as there is no value selected and stored in the previous field.

This seems to be a very difficult action. Is there nothing easier for a real beginner like me?

[Villeroy]

The only code you need is [Base, Python] Tiny Macro Refreshing Forms, List/Combo Boxes together with a filter table ("Power Filtering"). This way you modify some record by saving a filter record with a modified listbox value. The macro is be set up to catch the event named "Form Action" and refreshes another list box that is specified by means of a hidden control.

See attachment in the second link in my previous answer, and the form named "Voyages_AutoRefresh2" (using my AutoRefresh macro). The first form "Voyages" does the same with some extra clicks instead of macros. The second form does not work. The last form demonstrates a completely different approach to the problem.

[BasilVSA]

I'm afraid the whole concept of trying to do the filtering as described in the tutorials and odb files is way above my knowledge base. I don't understand the 'power filtering' setup at all.
I have attached an example of the database with the tables and form that I'm trying to update by filtering the fields. I have highlighted the 4 fields that are dependent on each other.

There is 1 table containing all the information I need to extract the correct input per field and this is the table "tbl_pass_criteria_01". From this table I select the first field which is the Standard via the List Box 4 and this works perfectly.

The next field List Box 5 should now filter the "Part or Clause" data based on the List Box 4 content
The next field List Box 6 should now filter the "Test Level" data based on the List Box 5 contents and
The last field List Box 7 should now filter the "Pass Criteria" data based on the List Box 6 contents.

This seems quite logical to me and I found it relatively simple in the MS Access database programming via a simple query. This is the SQL code that I use in Access.

SELECT DISTINCT tbl_Pass_Criteria_01.[Part or Clause], tbl_Pass_Criteria_01.Standard, tbl_Pass_Criteria_01.ID
FROM tbl_Pass_Criteria_01
WHERE ((([tbl_Pass_Criteria_01]![Standard])=[Forms]![frm_Input_01]![Combo30]))
ORDER BY tbl_Pass_Criteria_01.ID;

Is there nothing similar in LibreOffice Base that does not require very long and difficult macro programming to do the same thing?

I apologise if I seem to be complaining, I'm not, I'm just a frustrated beginner finding it very difficult to do what I thought was a relatively simple job. This type of work seems to be for an advanced developer and not a beginner like me.

If anybody has any solution that would ease the difficulties I really would appreciate it.

[Villeroy]

Please use MS Access.

[BasilVSA]

Hi Villeroy,

As I stated right in the beginning I am using MS Access but I need a program that can run on a MAC OS X system and I chose LibreOffice because I though it was the best and the easiest.

I am a real beginner with this system and many people comment on how difficult the change from MS Access to LibreOffice Base is. I am not a computer boffin especially when it comes to SQL coding and/or macros. I also struggled with MS Access queries, filtering, etc. and only found solutions to some of my challenges online as well.

I did state that I was not complaining but you must appreciate that for a person who is over sixty years old, retired and grew up before computers were even invented, I would experience some challenges with what I see as quite difficult coding. Some of the macro samples/suggestions you posted for me were very long pieces of code, most of which I have no idea what they meant or did.

Thanks you very much for your past assistance on my other issues. I will endeavor to find the easiest solution for my LibreOffice Base challenges wherever I can.

[Villeroy]

The whole Base component with all the drivers weighs no more than 30 may be 35 MB. It is a tiny addition to this office suite. MS Access is a database development suite which weighs more than this whole f*n office suite.
A list box holds another table's primary key while displaying a set of corresponding strings so the user can enter the other table's primary key into a foreign key field of the current form's record set by choosing the corresponding string from the list box. No more, no less. Particularly a listbox can not filter any form's record set, let alone another list boxe's record set. This requires some work-arounds involving macros, preferably without any macros because Base macros are a nightmare. Since 15 years cascading list boxes are a never ending topic. Either you do as we tell you or you have to develop your own methods. If you find one, please share it with us.

[Villeroy]

SELECT DISTINCT tbl_Pass_Criteria_01.[Part or Clause], tbl_Pass_Criteria_01.Standard, tbl_Pass_Criteria_01.ID
FROM tbl_Pass_Criteria_01
WHERE ((([tbl_Pass_Criteria_01]![Standard])=[Forms]![frm_Input_01]![Combo30]))
ORDER BY tbl_Pass_Criteria_01.ID;

Create a form where you select a record from one table and a subform showing the corresponding rows of the other table. If this is too difficult because there are too many rows in the main form, then you can resort to a technique that we use to call "power filtering". It stores a set of filter criteria (or only one) in a dummy table and you put this dummy table in a main form with matching table data in subform(s).