Pledge for more power to the developer :)

Classic Servoy
1

Hello,

I just want to share an idea, that in my eyes would solve quite a few problems fairly easy.

  1. OR-Relation

Quite a few requests have come about OR-Relations or enhanced possibilities in relations. Some Servoyan answered that this was difficult because of brackets and so forth. I think something neat would be this:

In the “Define Relation” you can still choose Source and Destination server and table, define the relationship name and where is now the definition of keys you have a tab panel. One tab says “Match keys”, the other says “SQL”. Under “Match keys” we have what is there today. In the “SQL” tab we can enter any SQL statement (the WHERE-clause, at least) including global variables. Something like

(field_a = globals.filter_left or field_b <= globals.filter_right) and field.c IS NULL

could be entered. This way we could do flexible joins and would have to take care about the outcome ourselves. Under “Options” I would suggest to disable “Allow creation of related records” in SQL mode. “Delete related records” - I am not so sure, either.

  1. addFoundSetFilterParam

should be changed to a form property called “SQL filter” right underneath the useSeperateFoundSet checkbox. It behaves just like the text property, a dialog opens where we can enter SQL. If we enter a WHERE-clause there, Servoy will use that whenever it querys data, also when used over relations or in searches, just like a foundSetFilter.

I believe that both suggestions would greatly enhance “foundset management”. Both shouldn’t be so hard to do, is it :D ? I understand the concept of Servoy to keep the user away from SQL. Servoy does a great job in handling the basic jobs, even allowing for pretty sophisticated stuff.

On the other hand we are not given the obvious possibility of entering pure SQL anywhere (except for databaseManager functionality, I have to admit), even tracing it is not so easy. And sometimes it seems even easier to type a SQL statement instead of learning the “Servoy way” when using for example addFoundSetFilterParam(). The other day I came about something that I still haven’t solved: I want to use a foundSetFilter to filter for one column to contain 1 or the other column to contain the user ID. With SQL I could easily do that.

I believe that the consistency of a solution doesn’t even have to suffer from this, either. It should be possible to test an SQL Statement for validity and return an error if the statement is wrong.

Is there any other ideas on this?

Thanks for taking the time
Patrick

2

hmm, no replies on this one… Maybe I wrote too much text.

So in short, the feature request is this:

  1. Define relations using a SQL-WHERE-clause
  2. new form property SQL_Filter where we can enter a SQL-WHERE-Clause

for explanation on how/why see text above.

Is this possible?

3

patrick:
hmm, no replies on this one… Maybe I wrote too much text.

So in short, the feature request is this:

  1. Define relations using a SQL-WHERE-clause
  2. new form property SQL_Filter where we can enter a SQL-WHERE-Clause

for explanation on how/why see text above.

Is this possible?

I really don’t know if it’s possible (the dev team keeps on surprising me ;-); but it’s a very good idea: the Relationship Tool would become even more flexible

4

I don’t think this will be too difficult to do since Servoy doesn’t do more than adding those definitions to a query as a WHERE-clause, does it?

5

Just to give you a bit more response:

Yes, that would be great expensions of the allready available tools! :D

The option to enter your own SQL statement in the relation is something I’ve been missing for a while.

I have many tables that contain “transaction” based records, meaning: records with start and enddates. Now I have to create calculations and globals to get the right related records, whereas if ywe could enter our own SQL into the relation, that would not be needed anymore…

Hope the DEV team can make this happen…

Paul

6

My guess is the dev issue in supporting a more flexible WHERE is not in where to put it in the servoy developer interface though I do like your ideas for this. I’d be willing to suffer a much less elegant interface to get the power of more flexible relations. I’m finding myself continuing to do the painful workarounds required of filemaker.

An even simpler interface would be to change the combobox (where you select a key for each side of a relation) to editable so you can just enter a javascript statement in there instead?

7

If I remember well, literals on the left part of the relation are already in the pipeline of devteam… (can’t remember the exact thread however)
This is a first step.

8

Yes, sure, but why don’t do it right? Once you have that, we start asking for OR-relations or whatever… That exactly is my point: instead of trying to make everything possible the easy way, let us do it ourselves!

I was quite surprised when I figured that when using a LIKE relation you have to take care of “%” yourself. In an interface that means for example: you have a global where a user enters a value. You can’t use that for a like relation, because it has no %. You don’t want the user to even bother about those %, so you end up having a calculation that does ‘%’ + globals.xy + ‘%’ just to be able to do what I would like to do with a simple ‘%’ + globals.xy + ‘%’ directly inside the relation definition.

9

olivier melet:
If I remember well, literals on the left part of the relation are already in the pipeline of devteam… (can’t remember the exact thread however)
This is a first step.

Other than saving a step, what’s the difference between having a literal and a field that contains a literal in a relation?

10

patrick:
Yes, sure, but why don’t do it right? Once you have that, we start asking for OR-relations or whatever… That exactly is my point: instead of trying to make everything possible the easy way, let us do it ourselves!

I was quite surprised when I figured that when using a LIKE relation you have to take care of “%” yourself. In an interface that means for example: you have a global where a user enters a value. You can’t use that for a like relation, because it has no %. You don’t want the user to even bother about those %, so you end up having a calculation that does ‘%’ + globals.xy + ‘%’ just to be able to do what I would like to do with a simple ‘%’ + globals.xy + ‘%’ directly inside the relation definition.

I certainly agree. The whole “%” is a real pain. I would prefer that Servoy include it by default and offer a way to escape it if needed.

I believe the lack of richer relationship definitions is a fundamental weakness in servoy. On using SQL for defining a relation as you suggest, I don’t think the language or user interface to define the query is the challenge. I suspect this problem is at the core of how servoy works to connect to and send queries to databases. While it can do things like create virtual joins between two seperate databases, it isn’t really a join at all. This probably also relates to the whole 200 record foundset limit. It probably also crops up as a challege to do things like alllowing addFoundSetFilterParam to be updated dynamically. Finally, you can’t use a multivariate value in a relation like you can do with FMP and soft returns between values.

Let’s just hope the dev team can get this remedied. I’d trade in modules for this - though I do love modules.

11

I certainly agree. The whole “%” is a real pain. I would prefer that Servoy include it by default and offer a way to escape it if needed.

‘%’ a pain? Not at all, a user can get used to it and otherwise you hardcode around it. Yes, another maybe more inflexible way to work but that life, you win some and you lose some. In this case I think you lose some you win many. BTW the advantage of %value% is that we can also do %value or value%. Isn’t that nice?

I believe the lack of richer relationship definitions is a fundamental weakness in servoy.

Right now yes but hey, we are talking about a product that on the market for a little 2 years now. Have a look at what we gained in those two years and compare that to any other product…

On using SQL for defining a relation as you suggest, I don’t think the language or user interface to define the query is the challenge.
In fact I suspect this problem is at the core of how servoy works to connect to and send queries to databases. While it can do things like create virtual joins between two seperate databases, it isn’t really a join at all. This probably also relates to the whole 200 record foundset limit. It probably also crops up as a challege to do things like alllowing addFoundSetFilterParam to be updated dynamically.

This is jumping to conclusions. I don’t see the relation between the things you pile up here.

12

We are not discussing pains or weaknesses. I am just trying to make something great even better. And I do believe that for Servoy this is even easier to do than developing something like a “literal” relationship, or an OR-relationship or whatever relationship you may come up with.

As far as I see that, all that happens is that Servoy uses the relationship definition to do a rather simple WHERE-clause in the form

key_1 = key_2 AND key_3 = key_4 …

So I don’t see why it shouldn’t be possible to let us completely type in that clause ourselves. In my suggestion the current possibilities will still exist as of today, but will be expanded to what I asked for here.

The relationship enhancement is actually not the only thing I am interested in but also that foundSetFilter. I think this is equally important.

13

On more remark on this:

The whole “%” is a real pain. I would prefer that Servoy include it by default and offer a way to escape it if needed.

I am not sure that you really want this. Something like

where column LIKE '%value%'

is very different from

where column LIKE 'value%'

and I am sure you need both. The thing I do not like about this is the calculation. The calc is only needed for this reason and therefore should be where it belongs, in the relationship definition.

14

Don’t get me wrong. No complaints here. I’m amazed by the productivity of the dev team and love the product. However, they do listen to us so we owe it to them and the product flesh out what we are asking for and why.

From a user perspective, searching is only after storing data in the priorities for a database. Since most of them are now accustomed to using google, adding % in Servoy is tough for many to get used to.

For the developer, we need more powerful ways to manage foundsets. Patrick is on the right track with where clause definitions in relations and a filterParam property of forms. That would be awesome. I just fear that delivering these may be extra hard due to Servoy’s architecture. The good news is that the dev team loves to surprise us with new goodies.