[Solved] Filter form by query - match fields between tables

[Doranwen]

I've learned how to compare two tables with SQL and update a Boolean field on one for each record where a particular field matches a field in a different table: viewtopic.php?f=13&t=103568
(At some point I want to figure out how to have it simultaneously fill in a particular numeric field from a corresponding one on the 2nd table, but that's a side matter and not urgent.)
I was also able to figure out (fairly easily) how to filter a form by that Boolean field (displaying only those records where that field was TRUE).

What I have NOT been able to figure out from searches and wouldn't have a clue how to figure out on my own, is if it's possible to filter a form with just the comparison SQL, *without the intermediate step of updating a Boolean field*. There are a considerable number of possible lists that I could dump into various tables for comparison, and that could end up involving the creation of far too many Boolean fields, which I've already been told is a bad idea. And in this case, I can't see any benefit to it OTHER than making it easy to filter the form by. If I could skip that step and simply have the form check the other/second table to see if the one field matches somewhere, and only display it if it does… that would be more useful. But I don't know if it's possible at all…

[UnklDonald418]

Perhaps a compound query would do what you want. Something like

Code: Select all

SELECT "Field01", "Field02", "Field03" FROM "Table1" WHERE "Table1"."Field1" = (SELECT "Somefield" FROM "Table2") 

[Doranwen]

Does that let me filter a form? The idea is, given Table1 has thousands upon thousands of records, and Table 2 with a much smaller set, that I would be able to set up a copy of my main form to filter so only the records would display where Field1 in Table1 matches Field2 in Table2. Not just for a query, but so that I can make *edits* to said records without having to search one by one to find them in the thousands of records… So the goal isn't to end up with a query/report but to be able to navigate a form without encountering records where Field1 doesn't match Field2 for any of the records in Table2.

[UnklDonald418]

Put "Table2" on MainForm and "Table1" on a SubForn and Link them with the common fields

[Doranwen]

Currently the form I've been using has MainForm = Table1 and multiple subforms for all the junction tables with attributes that go with the records in Table 1. My previous tests with filtering were where I filtered MainForm by the Boolean field in that same table.

I don't need (or want) to edit any of the records in Table2 - that table is ONLY there to provide a comparison to filter by. It doesn't have most of the fields associated with it that Table1 does, so if I assigned Table2 to MainForm instead I don't think it would work at all.

Are you suggesting creating a different subform and moving all of the objects on the form down one subform level (because the current subforms would still need to be under Table1) to set Table2 as the MainForm? That's the only thing I can see that you might be suggesting, wanted to clarify before I go through all of that work to test it, lol.

[Villeroy]

Search this forum for "Power Filtering".

[Villeroy]

General recipe for a power filter form:
Tools>SQL...

Code: Select all

Create table "Filter" ("ID" INT PRIMARY KEY, "DESC" VARCHAR(200), "INT1" INT, "INT2" INT, "D1" DATE ,"D2" DATE, "B1" BOOLEAN, "B2" BOOLEAN);
INSERT INTO "Filter" VALUES(1, 'This row takes creteria for the form named "Foo"', NULL, NULL,NULL, NULL, NULL, NULL);

menu:View>Refresh Tables

This creates a new table "Filter" with a primariy key, a desccription field, and up to 6 optional criteria (2 integers, 2 dates, 2 booleans). The table is populated with one row with ID=1 and a description.

Now you create a filter form with the fields you need to take the criteria and bind the form to SQL statement:

Code: Select all

SELECT * FROM "Filter" WHERE "ID"=1

rows other than ID=1 may be used for other filter forms.

We have to avoind the user tabbing into any (new) record other than the one where ID=1, therefore we disallow new records and deletion. We only allow modification. ANd no, we do not really need a navigation bar for this record.

Now you can link a subform to this distinct criteria row and link parameters or fields to the criteria row. The filtered subform will be editable if the row set comes from a single table including its primary key.

Suggestion for a filtered data form with optional criteria:

Code: Select all

SELECT * FROM "Data"
WHERE ("Date">= :pD1 OR :pD1 IS NULL)
AND  ("Date" <= :pD2 OR :pD2 IS NULL)
AND  ("XID" = :pX OR :pX IS NULL)
AND  ("YID" = :pY OR :pY IS NULL)
AND  ("BOOL" = :pB OR :pB IS NULL)

Each of the WHERE ... AND ... conditions returns True if the respective parameter is NULL (not entered into the parent form).
The assignment between master and slave fields goes like this::
param name (slave) <--> filter field (master)
pD1 <---> D1
pD2 <--> .D2
pXID <--> INT1
pYID <--> INT2
pB <--> BOOL

Edit: Forgot something

You need a push button with action = Refresh Form attached to the subform. It saves the criteria by switching the focus from the criteria form to the subform before reloading the subform. And you should attach another push button to the filtering parent form with action = Undo Entry

[UnklDonald418]

It isn't entirely clear what you want, but look at these examples..
[Example #1] Filter/Search with Forms (leveraging SubForms)
It's a long post, but there are two example databases that might help you.
Students2.odb uses listboxes. A modified version of the query suggested earlier could be used for the ListBox
LIKE_user_input_filter.odb uses a filter-table like the one Villeroy has suggested.

[Doranwen]

It isn't entirely clear what you want

Sadly, now that I've converted it to a split database and started dumping words in, it's far too large to be attached (4.4 mb now) or I would have done so at the start. I did upload the current version to my Mega account here, if that's OK: https://mega.nz/file/RBFD0QJI#W4Xyn6mzP ... NyuDK4b-60

Basically, I have a Words table (the main/master one) which has a bunch of junction tables associated with it already using the listboxes (Words_Consonants, Words_Vowels, Words_Combo, and Words_Meta). Those allow me to mark attributes on each word. There are also some Boolean and other fields in the Words table.

The main form I use is Words, but the DolchWords form tested filtering by the Boolean field Dolch under Words. I would like to create a form that behaves similarly (limits to a subset of the Words records) but that is filtered by a *separate* table (such as Fry, for instance, or any other table I create). Said extra table, like Fry, has a set of words, all of which will match records in the Words table (not all have matches at this point, but it's really a matter of "show only the records in Words that match records in Fry" so that shouldn't be a problem), so that I can edit just that subset if I want.

Fry has a matching Boolean field already, so I *could* do the steps above (though I'm not sure how to fill the FryFreq field with the numbers from the corresponding field on the Fry table) but I might create a table called BasicEnglish, for instance, with the words from that list. I would like to not have to go through the step of creating a Boolean field for BasicEnglish and running the SQL from here to update it on the Words table, before setting up a form like DolchWords that will filter based on that Boolean field.

I'd like to find a way to enter SQL into the Filter box under Form Properties that would filter a version of the Words form (which uses the Words table for the MainForm) based on a table like Dolch or Fry *without* using a Boolean field at all, just by comparing the Word field on the Words table with the Word field in the other table and displaying only those records which match so I can edit them. I hope that is a little clearer!

I'll read through the power filter stuff, it's a bit overwhelming at a first glance and I find it difficult to generalize instructions for a generic database to my situation, so it'll be a bit for me to wade through it.

[Doranwen]

…I may have accidentally figured it out, and it was along the lines of what I thought you suggested, UnklDonald418:

- create new subform and move EVERYTHING underneath that, assign that to my main Words table
- set the main form to the smaller Fry table (that was my test case)
- set "Allow additions" (and "Allow deletions", because I don't want to delete any there) to NO, but keep "Allow modifications"
- in the Words subform, link Master/Slave fields using the "Word" field in both tables
- in the Words subform, set Navigation Bar to "Parent Form"
- move the navigation buttons I had set for the Words table up to the main form

One or more of those steps may not be necessary, but they worked for what I wanted. Saved that, and suddenly I'm browsing through only the words that were on the Fry table and able to edit their records on Words.

Only minor quirk is if for some reason the table used on the main form has a record that does NOT match any of the records in the subform, the whole record is grayed out when I browse to it, but that's not a problem. I won't be using this fully until I've confirmed that all of the records in the smaller tables have matches in the big Words table.

[UnklDonald418]

I'm glad to hear you found a solution.
This query should list all the words in table Fry that don't have matches in the table Words

Code: Select all

SELECT * FROM "Fry" 
WHERE "Word" NOT IN (SELECT  "Word" FROM "Words")

[UnklDonald418]

By the way, looking at your database tables I recommend that you normalize those tables.
Normalization is a process where you apply some basic rules of relational database design to your tables.
In this case the tables "Dolch" and "Fry" should not have a text column "Word" because they duplicate what is in the table "Word". Instead "Dolch" and "Fry" should have an integer column "WordID" linking them to the text column in the table "Words".
That would eliminate missing words, it would ensure that a typing error would not generate mismatches between tables, misspelled words need only be corrected in one table and comparisons and links would be more efficient. It is more efficient to subtract one integer from another than to compare variable length strings of characters to determine equivalency.

[Doranwen]

The tables weren't actually meant to be linked, and the secondary list tables weren't meant to be permanent - I only ended up creating them as a way of isolating a small subset of the main Words table for editing and analysis. The Dolch table could be deleted at this point since there's a Dolch Boolean field in Words that is marked for every word on that list, and I can thereby filter the form by that. (I just hadn't gotten around to actually deleting it.) The same will happen with Fry when I figure out how to import the frequency numbers from there to the main Words table (I know the SQL to mark the Boolean field TRUE but not to also copy the frequency number from the one SMALLINT Frequency field on Fry to the FryFreq one on Words). But there are a couple other (also relatively short) lists I plan on temporarily running through a filtering form (similar to the way FryWords is set up) just to make sure I've edited the corresponding records in Words, since the Words table will have far more records than I can edit or would even need to initially.

Typing errors are not a concern here - the words are being imported from already available lists online that I am not typing myself at all, only batch processing. It's mainly prioritization of editing the entries on Words that is the key. But I do recognize that it is less efficient to do comparisons this way. Given the temporary nature of the secondary list tables and the fact that none of them would be more than a couple thousand records in all… I thought this might be the best solution with the least amount of work and setup. Once the corresponding words are marked Edited (thereby allowing me to limit queries to only Edited words, an important factor) and have been assigned attributes with the junction tables, the secondary tables can be deleted, having served their purpose.

My current process is going through a massive wordlist to remove unsuitable words and nonword strings, then dumping them into the Words table. (That single wordlist is the source of every word that's been added to the Words table so far, which removes any chance of duplicates since that wordlist has already been made unique. I'll be using Linux command-line tools such as comm to make sure any further words added after that are unique - and they will also be derived from text lists rather than from raw typing except in rare circumstances. I've had to do those types of comparisons recently and extensively for another project so I'm very familiar with how to use them.) Once that's finished, I'll be going through and editing batches of words by list to make sure all the words in the list have been edited (might even filter by "Edited = FALSE" to make it easier to find the words that still need editing, once I've done one or two lists) - this is where most of the secondary word list tables will be created and later deleted - and then I have some queries to run to analyze which attributes are the most common for the edited words, in preparation for using that in designing something.

[UnklDonald418]

(I know the SQL to mark the Boolean field TRUE but not to also copy the frequency number from the one SMALLINT Frequency field on Fry to the FryFreq one on Words)

at Tools>SQL execute

Code: Select all

UPDATE "Words"
SET "FryFreq" = (SELECT "Frequency" FROM "Fry"
WHERE  "Words"."Word" = "Fry"."Word")

[chrisb]

hello Doranwen,
i downloaded your db & for what it's worth i think that you have done a good job.
initially i was a little sceptical about the use of boolean fields in the table "Words" but having researched Dolch & Fry sight words i now believe it to be a sound decision.

just three things you may find helpfull:
1) you are using HSQLDB 2.3.2.jar & should upgrade to HSQLDB 2.5.1.jar which at this time is the most recent version.

2) the code to update table "Words" with data from table "Fry". will work with HSQLDB 2.3.2.jar onwards, i did not check previous versions.

Code: Select all

update "Words" set("Fry","FryFreq")=
	(select true, "Frequency" from "Fry" where "Word"="Words"."Word")
	where
		1 in
		(select 1 from "Fry" where "Word"="Words"."Word")

3) you can filter your table "Words" by using the filter options in the navigation bar.
3a) hit nav-bar "Form-Based Filters". all form fields now show empty.
3b) hit form "Dolch" checkbox. look in the "Filter navigator" window ("Dolch"=1(true)). hit again & see "Dolch"=blank(null). hit again & see "Dolch"=0(false). i am sure you get the picture.
3c) hit nav-bar "Apply Form-Based Filters". the form is now updated.
3d) hit nav_bar "Reset Filter/Sort" to remove filter.
NOTE: remember that sort/filter options will also be saved when form is saved in edit mode, probably not what you want so clear filters before saving.

[Doranwen]

Thanks! I'm hoping to be able to use it to help with designing a combination reading/handwriting/spelling curriculum. There are very very few out there (two or three that I know of) that actually are based on solid linguistics & reading research (surprisingly - most programs mix bits of the right sort of instruction with other things that they think "are part of any good reading program" when said other things actually confuse matters, teach bad habits, and are especially detrimental to the lowest students), so I'm working on a program of my own. The database will let me pull out the words that can be used to write fully decodable fluency readers (not to be confused with most programs' "decodable readers" which are not aligned with a carefully designed systematic sequence) at any stage in the program. But it's going to take a lot of editing words to get there, hence wanting to concentrate on the most common and useful ones to start with. I have a few further word lists I plan on doing comparisons with my current lists to find which I still need to add, etc. Lots and lots of prep work.

What's the best way to upgrade the jar file? I used the wizard that was linked in this post and it was only 2.3.2. Is there an updated wizard to use in the future (should I create another database, lol), or is it a case of "use the 2.3.2 wizard and then replace the jar file"?

Thanks for the SQL, both of you! I think I understand it now, looking at it. The more examples I see, the more I'm picking up how it works.

Interesting! My filtering will be pretty temporary so I'd figured I'd just create a version of the form that would have it filtered the way I wanted, edit the words in the filter, then delete the forms when I'm done with them. But it's good to know there are other ways to do it.

[UnklDonald418]

What's the best way to upgrade the jar file?

If you used the wizard then the directory where your database is stored will have a sub-directory called driver
Open the driver folder and for safety's sake, rename the hsqldb.jar file to something like hsqldb232.jar
Download the zip file from
https://sourceforge.net/projects/hsqldb ... t/download
You can either extract the full zip archive, or if your zip software allows. open the archive and drill down to the /lib sub-directory and extract the hsqldb.jar file.
Copy or move the extracted hsqldb.jar file into the driver directory of you database.
When you open your database it should use the new version of HSQL
You can verify the version by running a query

Code: Select all

SELECT * FROM "INFORMATION_SCHEMA"."SQL_IMPLEMENTATION_INFO"

[chrisb]

hello Doranwen,

in addition to the advice given by user UnklDonald418:

your jar is located in the folder called "driver".

i normally download my jars from 'Maven Repository' here>> https://mvnrepository.com/artifact/org. ... qldb/2.5.1
a user guide can be downloaded here>> http://www.hsqldb.org/doc/

it's usually just a case of removing/renaming the old jar, drag/drop the new jar into the same folder & then rename the new jar to "hsqldb.jar"
HOWEVER
HSQLDB version 2.5.1 is a major upgrade & it would be wise to follow the guidance given by forum user Sliderule here>> viewtopic.php?f=13&t=102412

[Doranwen]

It was easy to find and add (after renaming the old one), but I'm slightly confused on the guidance - all of it seems to be for upgrades from v1.8 to 2.5.1, but mine was 2.3.2, not 1.8… Does it still need those commands run?

[UnklDonald418]

If you can connect to your tables, there should be no problem with your upgrade.
Actually, there have been reports of minor compatibility issues for Base users beginning with version 2.4.0 but nothing that would affect your current design.

[Doranwen]

It worked as soon as I opened it up, so yay! Thanks for all the help.

[chrisb]

Sliderule said
HSQLDB version 2.5.1 and later can open databases created with version 2.0 and above. It will not open databases
created with older versions. It is a good idea to perform SHUTDOWN COMPACT after the upgrade.

[UnklDonald418]

It's also a good idea to execute SHUTDOWN COMPACT after deleting records or tables. That is a housekeeping command that rebuilds the mydb.data file when you close the database, recovering any wasted space.

[Doranwen]

*nod* I'll do that when I delete tables for sure then - and records, if that happens. The way I'm adding data, record deletion will be very rare if it occurs at all, but tables are much more likely. Will keep that in mind then!