[Solved] Wild card filtering in Form

[Ablaknyitókilincs]

Hi there!

So in a nutshell, after I learnt a lot lot lot of lot things (because I started from 0 a few weeks ago), I got another problem, but I just could not find anything that helps me.
I got a "huge" Database, and I want to do Inputs (in a table named "Inputs"), in it, by a form. The form is kinda working by now (queries, extra filter tables, blah blah a lot of weird stuff), but that's not the point.

The point is, I got a few list boxes, which are filtering each other (Name->Customer->etc, and if I pick a name, only the customers connected with the name showed), and almost the last box is showing the Projects for me.
The problem is, sometimes there are only a few, but sometimes there are a sh***load of projects, and a lot of them named almost the same (1-2 characters changed), and it is pretty upsetting to search for the exact one.
What I am trying to do, is that I tried to put an extra Text Box in the form, which saving to a new Filtering table (contains only filter id and entered data), and by this input made in the text box, the project names should be filtered.

To do this, I found a lot of stuff, and made a working Query, and when I run it, it is asking for an input, and showing me the filtered results noicly.

So my question is: How to use this query (or any solution is fine for me ) in a form, to filter the project names. The query input should be the TEXT BOX input.
I tried to do some slave-master bindings, but all failed, usually i got around 20-30lines of error .

(oh, and the query is actually filtering an another query)





The filtered qProjects:
SELECT "PROJECTS"."PROJECT NAME", "PROJECTS"."PROJECT ID", "ENTRIES"."ENTRY.ID" FROM "PROJECTS", "ASSIGNMENT.CUST.MYACC", "ENTRIES", "PBD-PROJ FILTER" WHERE "PROJECTS"."MY ACCOUNT" = "ASSIGNMENT.CUST.MYACC"."MY ACCOUNT" AND "ENTRIES"."CUST.ID" = "ASSIGNMENT.CUST.MYACC"."CUST.ID" AND "PROJECTS"."PLAYBOOK DIVISION" = "PBD-PROJ FILTER"."PLAYBOOK DIVISION" AND "ENTRIES"."ENTRY.ID" = ( SELECT MAX( "ENTRY.ID" ) FROM "ENTRIES" )

The wild card filter:
SELECT * FROM "qProjects" WHERE ( UPPER ( "PROJECT NAME" ) LIKE '%' || UPPER ( :FILTER ) || '%' OR :FILTER IS NULL )

Any kind of help would be appreciated <3

[Villeroy]

download/file.php?id=4689 (with a combo box. this is just the same as a text box but with additional auto-complete feature).

The wild card filter:
SELECT * FROM "qProjects" WHERE ( UPPER ( "PROJECT NAME" ) LIKE '%' || UPPER ( :FILTER ) || '%' OR :FILTER IS NULL )

Yes, this should work.

menu:Tools>SQL...

Code: Select all

CREATE TABLE "Filter"("ID" INT PRIMARY KEY, TXT VARCHAR(100));
INSERT INTO "Filter" VALUES(1,NULL)

menu:View>Refresh Tables

Now bind a main form to row #1 of that table. Other rows and columns can be inserted for other filters. This filter table works a little bit like a spreadsheet since we refer to a value in a distinct column TXT and row #1.

Form's SQL source: SELECT * FROM "Filter" WHERE "ID"=1 (entire row #1 with primary key)
Disallow insertion and deletion of records, disable the navigation toolbar for this form so there is no way to neither tab into any other record nor write into a new record nor delete any record.

Add your subform linked to your parameter query and bind master field "TXT" to slave field "FILTER" (which is a parameter name in this case).

[Ablaknyitókilincs]

Hey, thanks for the quick answer ^^

Right now, my base is crashing hardly, not responding etc so I will try this in a moment or two.

But my problem (and sorry if I see it wrong), is that you want something to do with the main form. Actually i got a lot of sub-sub-sub forms, because one is filtering by the previous sub form entry. My sub form (which should show me the Text-filtered results) is the 2nd from behind.

I made a table which was similar to this, but when base will come back to me, I will delete that and create as your suggestion.

And what about the binding? How should I do it? You mean that when I'm in the Main form Props-Data-Content I should use that SQL? (Now it contains this: SELECT "ENTRY.ID", "EMP.ID", "ENTRY.DATE", "ENTRY.COMMENT" FROM "ENTRIES").
Or by main form, I can use a subform, as a main one? (where the filtered sub-form is opening from)

I did something similar earlier, but as I said it only gave me a ton of error :\ The point should be: Projects list box should contain only the text-filtered projects, and by picking one, it should save the Project ID in the entries table (this is working already, but the filtering is not)

Again: sorry if I'm stupid, I just started from 0, but I try my best


EDIT:
I think i did what you suggested, but instead of a main form, i created a sub-form, before the project sub form. Binded etc, and this is my error (was the same before):

SQL Status: S1000
Error code: -38

Assert failed: S0000 Direct execute with param count > 0java.lang.Exception
at org.hsqldb.Trace.getStackTrace(Unknown Source)
at org.hsqldb.Trace.doAssert(Unknown Source)
at org.hsqldb.DatabaseCommandInterpreter.executePart(Unknown Source)
at org.hsqldb.DatabaseCommandInterpreter.execute(Unknown Source)
at org.hsqldb.Session.sqlExecuteDirectNoPreChecks(Unknown Source)
at org.hsqldb.Session.execute(Unknown Source)
at org.hsqldb.jdbc.jdbcStatement.fetchResult(Unknown Source)
at org.hsqldb.jdbc.jdbcStatement.executeQuery(Unknown Source)
in statement [SELECT * FROM ( SELECT "PROJECTS"."PROJECT NAME", "PROJECTS"."PROJECT ID", "ENTRIES"."ENTRY.ID" FROM "PROJECTS", "ASSIGNMENT.CUST.MYACC", "ENTRIES", "PBD-PROJ FILTER" WHERE "PROJECTS"."MY ACCOUNT" = "ASSIGNMENT.CUST.MYACC"."MY ACCOUNT" AND "ENTRIES"."CUST.ID" = "ASSIGNMENT.CUST.MYACC"."CUST.ID" AND "PROJECTS"."PLAYBOOK DIVISION" = "PBD-PROJ FILTER"."PLAYBOOK DIVISION" AND "ENTRIES"."ENTRY.ID" = ( SELECT MAX( "ENTRY.ID" ) FROM "ENTRIES" ) ) AS "qProjects" WHERE ( UPPER ( "PROJECT NAME" ) LIKE '%' || UPPER ( :FILTER ) || '%' OR :FILTER IS NULL )]

[Ablaknyitókilincs]

So, I tried a lot of variations, and all the time, the long error.
What I think should work is this:
I made the table as you said, which contains the "TXT" column.
I made a subform before the projects form (subform, because it has other forms before it like names, customers etc, all filtering each other).
It looks like this
MainFormEntry->SubCust->SucLoc->SubPBD->FILTER->SubProj->SubDev (all contains list boxes, labels, etc. etc.etc.)

So the FILTER form -> DATA -> CONTENT is :

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

Containing a Label, a refresh button, and a Text Box, which Data field is set to " TXT ".

The "SubProj" Form looks like this: Data -> Content:

SELECT "ENTRY.ID", "PROJECT ID" FROM "ENTRIES" WHERE "PROJECT ID" IS NULL

Link master fields: "TXT"
Link slave fields: "FILTER"
Containing a Label, a refresh button, and a List Box-----> This should show me the Parameter filtered (like containing 2017 only) project names.
This List Box looks like this: Data -> Data Field: PROJECT ID (this is where it should save the ID of the project I pick, this is in the entry table)
Type of list content: Sql
List content:

SELECT * FROM "qProjects" WHERE ( UPPER ( "PROJECT NAME" ) LIKE '%' || UPPER ( :FILTER ) || '%' OR :FILTER IS NULL )

I guess this should list this SQL, and get the :FILTER from the master-slave binding. But when i try to run the Form, long error (mentioned above) occurring.

ps.: If i change the List Content to something else (like qproject query, which is listing the projects filtered by the customer) the form is working like a charm, so i guess something is wrong with this.

[Villeroy]

Try this:

Code: Select all

SELECT * FROM "qProjects","Filter" WHERE "Filter"."ID"=1 AND ( UPPER ( "PROJECT NAME" ) LIKE '%' || UPPER ( "Filter"."TXT" ) || '%' OR "Filter"."TXT" IS NULL )

The filtered record set might be read-only, however it would be read-only anyway since "qProject" joins muliple tables.

I guess this should list this SQL, and get the :FILTER from the master-slave binding. But when i try to run the Form, long error (mentioned above) occurring.

yes.

2 more ideas:
1) use "qProjects".* instead of * sometimes the lonely * makes trouble.
2) use a parameter name other than FILTER. I'm not sure if FILTER is an SQL keyword. use pFILTER or something.

[Ablaknyitókilincs]

Heeeey!

The first code is working almost like a charm I wanted to do exactly this. The FILTER name was not a problem (changed but was the same error).

It will be a bit uglier, because they need to write something in the Text Box, or it will try to list the previous search results.

My idea is to make 2 Project list, one will show the original results of the qProjects, and the other will show the Text-filtered ones.

Is there any way to clear the "Filter.TXT" data after every new record is set? I mean, automatically.
If not, is there any way to use something like a radio button to bann or enable a subform? Like if they want to filter by text, they pick the "FILTER" radio button, (or flick or something) if they do not want to filter, they just leave it. So one way the list box shows the "qProjects" query result (originally), and the other way it would show the Coded one.

Anyway, this was a huuuuuge help for me. <3 Thank you very much and I hope you find something like 100Euro on the streets or something