You know how in FileMaker [always a popular way to start a post here] you can start a find, then add one or more requests to extend or constrain the search, and the user can flip between the requests, checking their find requests before submitting the query?
Yeah, I know there are other ways of filtering your foundset, but that one has its place. Any thoughts on implementing such a thing in Servoy? It occurs to me that one way would be to have each request be a new row in a table. In such a scenario, the finds could be saved if the user wanted. (Actually, this would require two tables: “finds” and “requests”.)
Alternatively, the search form could consist of globals, and the requests could be loaded into a two-dimensional array. Flipping between requests would consist of reloading the globals with the values stored at the different indexes. No rows need be created this way, but finds are not stored.
Or maybe there’s a better way. Requirements:
40 fields available, from four tables; text, numbers, dates
build query across multiple requests: AND / OR / NOT capabilities
user can view the group of requests before submitting query
I actually have created such functionality in my solutions.
What I use is a main form that will not be in find mode because it has a separate foundset. On that form I have my buttons and a tabpanel with the forms that ARE in find mode.
This way you can create new findrequests and can browse through your requests before hitting the ‘do search’ button.
It’s a bit of work but it gives a lot of searching power in the users hands.
As for globals. You don’t need them. Unless, in one case I had, you have data more than 2 tables deep. Of course when handeling searches more than 2 tables deep means you need to create your own SQL.
Then you also need to know that you can’t enter search data in globals when in find mode. Only in ‘browse’ mode.
And to be able to browse through your requests you need to handle all the data from each requests yourself.
Also another gotcha with using globals is that you can’t use characters like ^ and ! in your globals when they are of type number or datetime. They are simply not there when you read the global back. So you need to use string globals for all. Fun
Thank you, Robert! That gives me much to consider; I truly appreciate it. Nice clean interface design!
A question on your implementation: How do you deal with like (%) finds?
In search routines that I have implemented so far, search requests are typed into globals by the user and then, prior to submitting the query, % signs are added programmatically when and where appropriate. So “Ji” typed becomes “Ji%” and returns Jim in the first name field and “Dev” typed becomes “%Dev%” and returns “Servoy Developer” in the title field, etc.
Some users would be fine with adding their own % marks in the search; others may find it confusing.
Well I prefer to let the users use the Servoy control characters them-selfs.
Mostly because it’s almost impossible to know when they want to Dev% or when to %Dev% or when to #Dev% etc…
Only with well defined search screens like a simple quicksearch field you can do that for them.
Btw you can let the user type in the regular fields (when in find mode) and when they hit the custom find button you can add any control characters before doing controller.search().
As for globals and these control characters and custom SQL…it’s not simple but it can be done.
You need to translate and/or interpret all the known control characters (#%^!..) and create the SQL equivalent (upper(), LIKE, IS NULL,NOT, BETWEEN) and all that fun.
In essence you are recreating Servoy functionality…