Using a component in a filter query (Page 1 of 2)

Hi CDB,
I don't believe you can use 'AUTOQUERY' in that way - it's usually just for 'static' filters.
What you're trying to do (using an edit field (or combobox, datetimepicker etc) as a dynamic filter)  is usually what you would use the 'SEARCH' option for.
Derek.

The search button function does not work since you cannot set a condition, just select components involved in the search. I get error with the SQL Query generated looking like this:

select distinct columns, id from table where ""."" like 'correct_component_value'

Something is missing with the function search or there is a trick to use it.

I am not trying to run a search per se, I want to filter an automatic query with multiple arguments that most work with the filter but one of them has to come from a text field in the form to try to match 2 different columns in the database that could contain the value of the text field. After playing around with the search function I understood how it was working but how do you search in 2 or more columns at once without scripts?

As per attachment.

OK. Looks like I have no other choice but to keep it in the script...  The one in attachment is working as intended.

Hi Both,
I was thinking along the lines of concatenated fields in a calculated field and not using a script at all (see attached).
Using the data in the attachment, you can search for
1.  'data' - finds it in both 'info1' and 'info2' columns
2.  'bam' - finds it in 'info1'
3. ''your' - finds it in 'info2'
4.  'blah%0' - finds 'blah' in 'info1' and '0' in 'info2'
By default,  searching in a calculated field is not case sensitive which might be a problem if using a script
And apologies for the unimaginative test data - couldn't think of anything 'real' as example!
Regards,
Derek.

Wow!! This is very elegant. I am getting less dumb by the hour  

Would you mind explaining the logic behind this clever calculated field? All I understand is that it removes and replace null characters found by some space but I do not get how it looks for the value of the text field in whichever column it would be.

I had to add a button1.click in the script for it to work the way I want, my text field cannot be edited but it will change value after selecting an object from another form.

Hi,
1. The value of each field in the contatenation is pulled from the database using their field names, not from the columns in the tablegrd so it doesn't need to look for values in specific columns.
2. The 'trim' removes any possible leading spaces from each of the fields in the concatenation;  strictly speaking, you should have a 'trim' for each field not just for the first field (see the amended attachment).
3.  The 'ifnull' is needed because if any of the fields used in the contenation is null, then the calculated field itself would be blank - the 'ifnull' prevents this from happening (that's why in the test data, there are checks for Info 1 being blank, Info 2 being blank etc).  If all of the fields in the concatenated calculated field are mandatory, you wouldn't need this.
In the amended attachment, I've placed the calculated field (concatenation of Info1 and Info2) on Form 2 so you can see what it looks like).
As an aside, when you pass the value through from the other form, you can still use the incremental search (and hide the search button).  But without knowing the whole project you're working on, that may or may not help.
Regards,
Derek.

Thanks for the explanation Derek, you saved my day.

I implemented your solution using an invisible text box replicating via script the text box containing the read only value to be used as a filter, pointing to the calculated field with incremental search using an invisible button search (auto click on show via script) to retrieve my filtered data. The table grid has also a filter and all I have in my script is limited to the strict minimum. No more complicated SQL code in the script and dates are displayed according to the user system preferences.Win - win.

Thanks Sparrow, it does make sense to avoid false positives.

when you say editing, are you perhaps put the calc field on a component(e.g. TEdit) in a form and add it on the save or make it editable on a grid? That won't work. If you are familiar with SQL, then that calculated field is only an alias, so it is really not in database. It's like SELECT trim(ifnull(info1||'§','')||ifnull(info2||'§','')) AS calcfield FROM yourtable. calcfield is not in database but is only in the result of your query

don't show the calculated field on the grid, you can always show the fields(ones that is on the database) e.g. on the example, the info1 and info2. then edit them. calc fields only work as a view.

I have been working for a very long time with very curious people who could not help but try to look how software are working. Even at a very high technical level, I have seen people trying to understand how something was working without reading the documentation and BREAKING everything by altering one file to see what it would do. So, yes, it would be great to be able to encrypt the sqlite.db but few people are going to be able to alter the .db file. However, screwing the app by changing anything in the .dcu file cannot be easier.

I have seen "solutions" here to embed everything in a "virtual" container but I am still working on how to distribute updates afterwards...