Servoy Forum

Currently you can have no more than 10 keys in a relationship. Can this number be increased or possibly become unlimited? If making it unlimited would cause to many problems in what is happening behind the scenes, then can I request it at least be raised to 25? I've got a relationship that needs 12 keys and this could grow over time, and currently I'm using the old school hack of checking for mulitple conditions in a calc and using the calc as the key.

Thanks,
-Jeremy

Hi Jeremy,

Can you explain what you are trying to do that you need so many keys in a single relationship?

I'm using a relationship to define a foundset on a related tab. We need about a dozen "filters" many of which are in global fields(on the left side of the relationship), as time goes on the number of filters may increase. (eg. globals.active = "Y")

Using a query to generate the foundset creates an unacceptable load on the system when viewing this form in real time.

The calc is working for now, but a more desirable solution would be to have more than 10 keys at some point.

Hi Jeremy,

jkipling wrote:I'm using a relationship to define a foundset on a related tab. We need about a dozen "filters" many of which are in global fields(on the left side of the relationship), as time goes on the number of filters may increase. (eg. globals.active = "Y")

Globals on the left side don't have much to do with a JOIN though but have everything to do with the WHERE clause.
Also you say 'over time the number of filters may increase'. What do you mean by that. As the dataset gets larger? Or as the table gets extra columns ? Or as progressive insight in the needed functionality requires more ?

jkipling wrote:Using a query to generate the foundset creates an unacceptable load on the system when viewing this form in real time.

The Servoy relationship translates directly to a query to the backend database so the load should be the same. What did you try before that made the load unacceptable ?

jkipling wrote:The calc is working for now, but a more desirable solution would be to have more than 10 keys at some point.

What does this calc do exactly ?

Somehow I think a custom query is all you need.

The tab is defined through the relationship, so no method needs to be run in order to update the found set.
Using the query we had to code the query, get the dataset and load it into the tab, on every relevant dataChange, onLoad, etc. It ended up being unacceptable. I think its the loading, not the querying that caused the lag, but in general there are many more cycles needed that aren't needed with the simple relationship.

Actually what the calc is currently doing is this: Let's say on the right side a single field could be 1 of 10 values. We only want the records where that field is one of 7 values. Adding 3 != clauses to the relationship put us over the limit, so we made the calc to do this logic and returned 1 if it satisfied, 0 otherwise. On the left side theres a global.constant = 1. This is a financial trading system so business rules can and do change frequently and we'd need to update a variety of predicates in this relationship. The number of possible values for the stated field could go up to 15, or new filter parameters could become relevant at anytime.

Hi Jeremy,

I think you need to take a look at the onRecordSelect event and controller.loadRecords(SQLStatement, [optional prepared statement parameters]).
This will let you load the related foundset when a record is selected (during onload or when browsing) and Servoy takes care of the loading (batches of 200, etc).

Hope this helps.

Nope that's not acceptable. A user can be using one parent record for the entire day while the children in the tab could change frequently through out the day due to changes in any of the relevant key fields by other users.

Like I said "and Servoy takes care of the loading". When you use a DataSet then Servoy does nothing.
Try it.

It doesn't work with real time databroadcasting.

And for that matter, relationships sometimes don't either. I'm still investigating when and why on that though.

Databroadcasting should work fine. But I think what you mean is that the resultset should adjust accordingly to the changed data.
Using a relationship or a (manual) search or a SQL query in the controller.loadRecords() all result in a query where Servoy knows what the search criteria is. Not so when you load a DataSet into the controller.

I think that databroadcasting should also change the (related) resultset when data changes but perhaps one of the Servoy developers can comment on this.

Yes. That is what I meant, We need to have this form be able to sit idly, and we need the tab's related foundset to change whenever data changes. I don't think using controller.loadRecords() in an onRecordSelect trigger event accomplishes this. Cron jobs aren't good enough either, we need absolute real time.

Most of the time databroadcasting works as expected, relationships are updated and the foundset is what it should be. Sometimes databroadcasting does not work, relationships are not updated on data changes and the form does not show the correct records, or even a simple field on the form itself will not show the new value. But I think this problem may have to do more with our code and architecture more than servoy. It's an on-going investigation, we will inform the community when it's resolved.

Basically, I was wondering how complicated and taxing it would be to allow more than 10 keys in a relationship. I'd still like to make that feature request.

Hi Jeremy,

Just to be clear. The onRecordSelect is to load the related resultset and is triggered only then. A controller.loadRecords(sql) will load the resultset in the controller and let Servoy handle it further (unlike loading a DataSet or Array into it)
A Servoy relationship does all that for you.

Now in all of the above described scenarios databroadcasting should work and resultsets should change as well.
So you seem to have 2 issues.
1. You can only use 10 keys in a relationship (but that can be worked around using the controller.loadRecords(sql) method)
2. databroadcasting doesn't (always) change the resultset when the records don't match the criteria anymore.

As for actually filing a feature request and/or bug report you should use the Servoy Support system. The forum is just to discuss the feature request/bug report.

Hope this helps.

Thanks a lot Robert. I'll use the formal feature request, but thanks for thinking it through with me.

I happen to have a lot more than just 2 issues, but wanted to discuss the key limit here and we tangentially got into the databroadcasting issue.

So, let me see if I understand what you are saying. Calling controller.loadRecords(sql) is functionally equivalent to having the tab be defined through the relationship? Are you saying you only have to run that step once and then for the duration of having the parent record active it will keep loading the records correctly? I'll have to stress test that with multi user data entry and take a look at the server log.

jkipling wrote:So, let me see if I understand what you are saying. Calling controller.loadRecords(sql) is functionally equivalent to having the tab be defined through the relationship?

It essentially sends the same query to the back-end database, yes.

jkipling wrote:Are you saying you only have to run that step once and then for the duration of having the parent record active it will keep loading the records correctly?

That is something that I like one of the Servoy developers to comment on.
In my view it should change the resultset as it also should do with a related foundset. But I can also imagine that Servoy doesn't change the resultset.
So if any of the Servoy devs can enlighten us ?
If it doesn't change the resultset then that would be a feature request for me.

only RelatedFoundsets are updated/tested when changes are coming from other users.

Normal/shared foundsets are not.