I am trying to make “advanced searchs” on Servoy, using two related forms: a container form and a related form (tabPanel).
I start a find() and I introduce my criteria on the first record of the container;
then I create a new record, and I introduce new criteria, so I get a condition such as (criteria1 OR criteria2)
Let’s say it’s a query such as:
select * from container_table
where (container_table.criteria1 OR container_table.criteria2)
Now, let’s say I introduce search criteria not only on the container form but also on the related form.
Then the query I would get would be this one:
select * from container_table
join related_table on container_to_relatedTable_relation
where (container_table.criteria1 OR container_table.criteria2)
AND (related_table.criteria)
But this is not what I want.
I would like a query such as
select * from container_table
join related_table on container_to_relatedTable_relation
where (container_table.criteria1 AND related_table.criteria1)
OR (container_table.criteria2 AND related_table.criteria2)
I know that this is NOT the way Servoy works, because the relation between the container form and the relatedTable has no effect on my search.
But is there a way to bypass this behaviour to make the query behave the way I want?
maservoy:
Now, let’s say I introduce search criteria not only on the container form but also on the related form.
Then the query I would get would be this one:
select * from container_table
join related_table on container_to_relatedTable_relation
where (container_table.criteria1 OR container_table.criteria2)
AND (related_table.criteria)
I would actually consider this a bug in the Servoy parser. When you add criteria in a related table you are doing that in the context of the selected main record, not all main records. I suggest you file this as a bug in Servoy’s support system.
maservoy:
But is there a way to bypass this behaviour to make the query behave the way I want?
The only workaround I know of is to create the SQL yourself and load the records using controller.loadRecords(SQL) or controller.loadRecords(SQL, parameterArray).
ROCLASI:
I would actually consider this a bug in the Servoy parser. When you add criteria in a related table you are doing that in the context of the selected main record, not all main records. I suggest you file this as a bug in Servoy’s support system.
Hi Robert,
it’s not a bug. I suggested it and the answer was that Servoy behaves in that way.
Nevertheless you gave me an idea on trying to join both tables using the parameters and then loadRecords…
Harjo:
I’m not sure if this is going to help, but did you try: controller.duplicateRecord() instead of controller.newRecord() inside the
if(controller.find() ) {
//your search here…
}
Hello Harjo,
I already tried. It’s the same behaviour…
Hans Nieuwenhuis:
Hi,
Which version of Servoy is this ?
Regards,
Hans
maservoy:
it’s not a bug. I suggested it and the answer was that Servoy behaves in that way.
Maybe not a bug then but in my view a flawed design. Putting criteria in related tables should be in only the context of the parent record.
I will file a case for this.
ROCLASI:
Hi Miguel,
Maybe not a bug then but in my view a flawed design. Putting criteria in related tables should be in only the context of the parent record.
I will file a case for this.
I agree. It’s very difficult for a user to understand that behaviour.
I am tring to solve it with our callbacks.
We have a global method for every onLoad, onHide, onRecordSelection… and every form makes a call to this global method.
The global method looks for a local method on the calling form (also named onLoad, onHide…) etc. and if it exists, it fires the local one.
Somehow, these callbacks are our application architecture; and I’m trying to figure out something in these callbacks to bypass Servoy’s behaviour.