1 (edited by CDB 2020-08-23 07:48:41)

Topic: Using a component in a filter query

Rather than using the script facility to write a filter, I thought I might be able to parse an edit box into the filter query in the 'set up auto query'. Sadly this does not work, as it appears the filter searches for a column with the edit box name.


When a script is used either the SQLQuery or the SQLExecute are quite happy to have a component in the SQL text, so somewhere there is the ability to parse components into a SQL statement.


Before I give up and write the script, does anyone know if it is possible to retrieve the contents of an edit box and parse them into the filter script? 


Image attached of what I'm trying to do. This creates the error; 'No such column frmMain.edtAssGroup.text' .


I have tried

(( assemblyNo ="{edtAssGroup}") AND (subOrdinateParts.id_part_categories != 2))

  This doesn't cause an error message, but it doesn't work possibly because edtAssGroup is on the main form and not on the form I'm using to display this filtered result.


Thank you in advance.

Post's attachments

Attachment icon auto_query_filter.PNG 124.44 kb, 139 downloads since 2020-08-23 

On a clear disk you can seek forever

2 (edited by derek 2020-08-23 11:58:07)

Re: Using a component in a filter query

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.

Re: Using a component in a filter query

Well thanks Derek,  I hadn't thought of doing it that way, mainly because I was going to write it in the script and suddenly thought, maybe I can use MVD to save me some typing.


I'll try using the SEARCH function and see if that produces the result I need.

On a clear disk you can seek forever

4 (edited by tcoton 2023-10-29 21:27:37)

Re: Using a component in a filter query

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.

Re: Using a component in a filter query

The 'search' function allows you to filter a search field by its value being 'equal', 'less than', 'greater than', 'starts with', 'contains' and 'not equal'.
This is also true for the 'sqlquery' option
There is also the option to concatenate more than one search field so the user only inputs a search value in one edit field.
I appreciate your desire to take stuff out of the script and try to embed it more into native MVD but I'm not sure I fully understand the exact problem you're having with the 'search' options. 
Perhaps you could attach your project or an example that highlights the issue?
Derek.

Re: Using a component in a filter query

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.

Post's attachments

Attachment icon auto_query_filter_by_edit.zip 336.38 kb, 76 downloads since 2023-10-30 

Re: Using a component in a filter query

That's one of MVD's limitation I think. Just do some script and do make use advantage of the Tablegrid dbFilter.

Post's attachments

Attachment icon auto_query_filter_by_edit2.zip 549.1 kb, 80 downloads since 2023-10-30 

brian

Re: Using a component in a filter query

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

Post's attachments

Attachment icon auto_query_filter_by_edit.zip 336.66 kb, 74 downloads since 2023-10-30 

Re: Using a component in a filter query

tcoton wrote:

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

Good to know, but if you really hate it to have query on the dcu, then embed it on the form.xml. imagination is the key. It'll be visible also but i think it'll be hard.

Post's attachments

Attachment icon auto_query_filter_by_edit3.zip 548.77 kb, 74 downloads since 2023-10-30 

brian

Re: Using a component in a filter query

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.

Post's attachments

Attachment icon auto_query_filter_by_edit3.zip 336.3 kb, 96 downloads since 2023-10-30 

Re: Using a component in a filter query

derek wrote:

Hi Both,
I was thinking along the lines of concatenated fields in a calculated field and not using a script at all (see attached).
Be careful with the script option as it could be case sensitive.
Regards,
Derek.

This one rocks. Derek have unlimited imagination.

brian

Re: Using a component in a filter query

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

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.

Re: Using a component in a filter query

You need to be careful.
Due to the space during concatenation (ifnull(info1||' ','')), the search will find an unreal "h t".
The probability is low but you need to know.

Re: Using a component in a filter query

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.

Post's attachments

Attachment icon auto_query_filter_by_edit4.zip 337.54 kb, 92 downloads since 2023-10-30 

15 (edited by tcoton 2023-10-30 20:14:42)

Re: Using a component in a filter query

@Sparrow -  What could cause such a behavior?

Re: Using a component in a filter query

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.

Re: Using a component in a filter query

Derek described everything.

It is advisable to simply replace the space character with an unused character. Then there will be no false positives.
Example:

trim(ifnull(info1||'§','')||ifnull(info2||'§','')) 

Re: Using a component in a filter query

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

19 (edited by tcoton 2023-10-31 17:02:56)

Re: Using a component in a filter query

Beware, if you are using 2 calculated fields using a trim on different fields, you will be able to filter the grids with a text field but you won't be able to edit due to a SQL error telling you that one of the column does not exist. IT looks like the calculated fields are all charged in memory during the application initialization and there can be some mix up. I had to rethink some of my design where I could make a lot of scripts disappear ... but had to redo it.

20 (edited by brian.zaballa 2023-10-31 20:02:33)

Re: Using a component in a filter query

tcoton wrote:

Beware, if you are using 2 calculated fields using a trim on different fields, you will be able to filter the grids with a text field but you won't be able to edit due to a SQL error telling you that one of the column does not exist. IT looks like the calculated fields are all charged in memory during the application initialization and there can be some mix up. I had to rethink some of my design where I could make a lot of scripts disappear ... but had to redo it.

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

brian

Re: Using a component in a filter query

brian.zaballa wrote:

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

Yes I did try that to hide my search query from being in the .dcu file and while the search was working very well, editing via the tablegrid did exactly  that!!

22 (edited by brian.zaballa 2023-10-31 20:47:09)

Re: Using a component in a filter 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.

brian

Re: Using a component in a filter query

Strange problem with dcu file.
What about sqlite.db?

Re: Using a component in a filter query

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...

Re: Using a component in a filter query

sparrow wrote:

Derek described everything.

It is advisable to simply replace the space character with an unused character. Then there will be no false positives.
Example:

trim(ifnull(info1||'§','')||ifnull(info2||'§','')) 

Hi all,

Any idea on how to translate this Sqlite version to MySQL? I get a ton of errors while trying to tweak it to fit MySQL and I am not far from the headache.