Hello,
I created a query that is filtered by two dropdown lists. The result of this query is going to a table. Everything works well but I would like to add the option of NOT SELECTING one of the two filters i.e. adding * amongst the dropdown options.
Is there a way to do it? I attach the file for reference. The form that I am trying to update is called Investors Calling
20171207 CRM SETA.odb
Thanks in advance
Regards
Tommaso
Hello once again,
There is a method to do this but it will require you to change your approach to filtering. It again relates to the last question and answer I provided so hopefully this sample will work for you. I will be using the CompanyCallingRevised form which is a modified version of yours and further modified for this answer. I am not using your current specified form because, once again, it is confusing and after spending too much time trying to figure out what results you are expecting I gave up. Please, in the future, include what the expected results are to be. Your form sample has no labels and someone looking at this must edit the form & try to figure out everything from scratch.
While the filtering method you are using works, it is extremely restrictive. You cannot use wildcards such as
Description LIKE( CONCAT( '%', "FILTER"."DESCRIPTION", '%' )
which allows you to select something if a portion matches. In your case, you want to select ALL of something if the filter field is empty or IS NULL.
So, in your filtering you have a select statement of everything. Then to weed out the unwanted, the ‘Master’ & ‘SubForm’ tables are linked via the form property link values. For more flexible filtering, eliminate these field properties completely and base the result entirely on the SQL statement.
My attached form (as was done in previous question) does just that. It incorporates a WHERE selection based on selected info vs filter field value. In the SQL WHERE statement, I use WHERE table.value = filter.value for selection. This gives you only records matching the filter field. Here I add another check if nothing is selected WHERE (table.value = filter.value) OR (filter.value ISNULL).
Here is the complete SQL statement used in the sample:
SELECT "companies"."name", "companies"."country_id",
"companies"."seta_id", "A"."sector_id",
"A"."notes", "companies"."company_id"
FROM "companies", "Filter"
LEFT JOIN (SELECT "company_id", "sector_id", "notes"
FROM "company_sector", "Filter"
WHERE "sector_id" = "Integer") A
ON "companies"."company_id" = "A"."company_id"
WHERE (("companies"."country_id" = "Filter"."country_id")
OR ("Filter"."country_id" IS NULL))
AND (("A"."sector_id" = "Filter"."Integer")
OR ("Filter"."Integer" IS NULL))
Please note: not all SQL can be created using the graphic interface. In this case it is in SQL View mode (my preferred method).
Now to use the sample, you can select an item from each list box and get the specific result. If, however, you want ALL items (your wildcard requirement) from either or both of the list boxes, for that selection simply select the BLANK line at the very beginning of the list. This will be placed in the filter field & the SQL will detect as NULL.
Sample: WildCardListBox
Hi, thanks again. So you removed the slave-master connection and you select directly from the SQL of the Subform. That works just fine (I promise your previous version did not). I am still quite new with SQL and I still have to digest the LEFT JOIN. Thank you very much for not giving up on me!
Tommaso
My pleasure. Don’t understand why last sample didn’t work. This is same sample with a minor change. Doesn’t matter.
Joins are a bit tougher but essential. They help in normalization. This site may help - w3schools. There is a visual for the different joins on this page. The rest of the site should also be a reference for SQL.
Questions always welcome - please ask responsibly.
Just a question, I apologise if it sounds stupid but I cannot make sense of “A”. What is that? And where did you define it? And why there is one without " "? I checked the website you pointed and I haven’t found that syntax.
No apologies needed. Logical question. First, using HSQLDB (comes with LO) field and file name can be entered without quotes if they are entirely capitalized and contain no spaces or special characters. Either with or without works. The editor will put them there anyway.
After the LEFT JOIN is the select statement entirely within parenthesis. This is obtained first and its’ result must be given a name. Once created, to access it’s info (outside the select) you must use this assigned …
… name to refer to any of the extracted contents. That is why the WHERE portion has table name used as “A”.something. The name is up to you but must not be duplicated. I have a habit (maybe not good) to use A,B,C,etc for these names. Yes you can end up with SQL containing lots of different Select statements being Joined or otherwise. SQL is like learning a programming language for databases.
You may also want to search the internet for HSQLDB v1.8 Manual (this is the one delivered with LO). It will give specifics on its’ SQL. The site I pointed you to is fairly general as each DB may have variations. Also the DB with LO is VERY old. The standards have changed much since that version and with it comes enhancements to SQL and what can be done.
got it got it