Topic: Script Substitute for a Search Button

Hey guys.
I know i could just use a search button, hid it, and then use a script to "click" the button.
The problem is that i am trying to clean all the hidden menus all the hidden buttons and get this done manually.

I want to set a tablegrid filter for a search and here's what i'm trying to do:

var s:string
s = (the edit box of the contacts of course)

Form1.tablegrid1.dbFilter := '(clientes.contacts1 (is this the sql table right?) LIKE ''%'+s+'%'''+')
form1.tablegrid1.dbupdate

But i dont understand if this is even possible, or if i am doing anything wrong in my syntax.
Can you guys help?

Re: Script Substitute for a Search Button

Hi Vasco,
I would do it like this, but they may be other ways too.
Regards,
Derek.

Post's attachments

Attachment icon filterbyscript.zip 336.64 kb, 506 downloads since 2018-05-09 

Re: Script Substitute for a Search Button

thanks Derek! smile

Re: Script Substitute for a Search Button

Hey Derek, sorry to bother you again.

how do i use your expression with multiple filters?

Example:

  frm_assistencia.TableGrid_Search_Cl.dbfilter := ('nome like "%'+nome+'%"') and ('contacto1 like "%'+contacto1+'%"');

Re: Script Substitute for a Search Button

Hi,
Perhaps you can do it a bit like this (see attached).
Derek.

Post's attachments

Attachment icon filterbyscript.zip 336.88 kb, 444 downloads since 2018-05-09 

Re: Script Substitute for a Search Button

that works well when i'm using 1 search bar, but i want to use 1 for each individual item : name, phone etc... altough using one(like google search) is also very nice.

thanks derek! smile

Re: Script Substitute for a Search Button

Hello VascoMorais and derek,


The problem with multiple search fields is that it can become complicated when you have to test 3 or 4 fields for user inputs, leading to a veryyyyyyy looooong sequence of

if... then... else...

in order ton cover all the combinations.


That is.... if you put the search logic in each "OnChange" events of your input components.


What I usually do is regroup all the search logic in one place, a procedure, that will take care of all the conditions.
From there, all I have to do is link your component's OnChange events to this single procedure.


In the attached example, your have two tables.
The first one is called asset and contains more or less 26.000 rows with for each
- a serial number (SKU number)
- an asset name
- an asset status (is it owned, wanted...)
- a reference to the second table
The second table only contains a text field, with the name of the vendor of the assets


Searching through 26.000 rows (my actual database has more than 300.000) can be heavy, especially if you want to be able to search on all table fields


In the fully commented code attached, you will see that :
- the search can be performed on 4 criteria at the same time
- when a field is empty the database filter is neutalized
- I define 4 variables for each possible filters and concatenate them in one single filter activated by each OnChange events of the components


Hope this helps a little

have a good day olds friends smile


Cheers


Mathias


PS :
1 - my database is 15 times larger than this sample, so I added a Timer event to delay the effect of the filter until the user stops typing
2 - The SKU filter operates with a LIKE only on the end of the serial number
3 - The asset name filter operates with a LIKE from both ends of the name
4 - The formatting of the TableGrid takes place only in it's OnChange event, this way I don't have to replicate this formatting for each changes
5 - I use Radiobuttons because the little hassle of creating them all is better than having to handle the logic behind 5 comboboxes when only one can be checked at a single time

Post's attachments

Attachment icon MVD_MultiSearch.zip 1.42 mb, 544 downloads since 2018-05-10 

I'm a very good housekeeper !
Each time I get a divorce, I keep the house

Zaza Gabor

Re: Script Substitute for a Search Button

the god mathmathou has arrived!

9 (edited by derek 2018-05-10 00:04:54)

Re: Script Substitute for a Search Button

Hi Vasco, Hi Mathias,
Sorry, I thought you wanted it all working off a single search field.
If you want one search field for each item, can you do it like this (see attached?).
Derek

Post's attachments

Attachment icon filterbyscript3.zip 337.03 kb, 504 downloads since 2018-05-10 

Re: Script Substitute for a Search Button

hey Derek, thanks again for your example, but in your case (and mine too, since i also tried "and's" instead of "or's" and using vars for each edit boxes).
If you have a person called "no phone" without a phone number, you can't search it for his name "no phone" it won't show up, because the other field is blanc.
That's what i need to solve.

Re: Script Substitute for a Search Button

Mathias , i have a question.

Why do you have different procedues for each field, that lead to the same funcion: run a second procedure called apply filters.
Wouldn't it be better if you go directly to call apply filters directly from the procedure?

12 (edited by mathmathou 2018-05-13 21:07:16)

Re: Script Substitute for a Search Button

Hello VascoMorais,


Imagine you have 3 components on which you want to filter :

  • a checkbox which can be checked or not

  • a combobox which can have an element selected or not

  • and edit field that can be empty or not


If you use the onChange event of those components to filter your query, ALL conditions must be specified in ALL onChange events of ALL components, with a specific SQL qury for each case.
So you end up with a lot of conditions in each onCange events (if checkbox is checked and edit field is empty or checkbox is not checked and combobox index > -1 and so on...). This is difficult to keep track of all the chained conditions and their effect. Not talking when you have more than 3 components for filtering.


But if you regroup all conditions in one procedure like I did, the logic is only in one place and you don't have to worry about AND and OR, just code what each component does and that's it. You only need one single filtering query and each element can be nullified with '1 = 1' (which is always true) or the filter issued by the component state.


Hope this makes things a little clearer.


Wish you a good week


Cheers


Mathias

I'm a very good housekeeper !
Each time I get a divorce, I keep the house

Zaza Gabor

Re: Script Substitute for a Search Button

ahhh.. yes i understand, i skipped that because i am only using the "edit boxes" in my case. so i have the procedure directly to the entire script.

Many thanks Mathias, once again you are a terrific asset to this group smile