Sorting stored calculation on first display

Classic Servoy
1

I have a calculated field that needs be be sorted before it is displayed. Since you can only sort stored calculations, it is stored, but causing problems.

What is currently happening is when I first display the form (a list in a dialog), it comes up quickly, but the list isn’t sorted correctly by that field. When I close that form, the entire system appears to be hung up while I imagine the calculating and sorting is actually taking place. Once functionality is regained, and I open back up the form, it is sorted correctly.

Here is the current program flow:

application.displayFormInDialog(formName, etc.)
databaseManager.recalculate(formName.foundset)
forms.formName.controller.sort('fieldName desc')
forms.formName.controller.loadAllRecords()

I hope this makes sense to someone. If not, I would be happy to explain further.

How can I get it to correctly display the first time?

Thank you.

2

Which version of Servoy?

Why do you do this:```
databaseManager.recalculate(formName.foundset)

3

IT2Be:
Which version of Servoy?

Version R2 2.2.5_05-build 337

Why do you do this:```
databaseManager.recalculate(formName.foundset)

To recalculate the calculated field based on some global variables set when the form is called to open.

4

To recalculate the calculated field based on some global variables set when the form is called to open.

Are you sure that that is needed?

5

It doesn’t work at all if that isn’t in there.

The problem is the storing, but you can’t sort on an unstored field.

6

Hmm, I have had a similar sorting issue (without the recalculation and globals issue) a while ago.

So, going back to your original question recalculating takes too long and screws up the form as a result?!

Is there no other way to create the field content. Imho it is not very elegant to use a global on database content, although I understand you are doing this to be able to perform your sort…

When I understand correct, since the recalculation takes a while, you are talking about a lot of records…

7

IT2Be:
Is there no other way to create the field content.

This is really the question. Is there no way to sort on the fly against an unstored calculation? I guess I miss this about filemaker. The problem with this system is that even if you do get it to sort correctly, what happens if two users run the operation at the same time with different globals? They will be storing different values in the rows… disaster.

What are other ways to handle this issue?

8

Well, the question I should have asked in the end (but I was a bit lazy I must admit) is what is it that you are exactly trying to archieve. Your ‘disaster’ conclusion is exactly one of those things I had in mind.

Can you descibe your ‘case’? I am sure there is a solution :)

9

IT2Be:
Can you descibe your ‘case’? I am sure there is a solution :slight_smile:

I’ll describe mine, instead :wink:

Is it possible to make an unstored column sortable in a table view, making it behave just like real columns?
I mean, sort must start when users click on the header of the column, sorting order must switch between ASC and DESC just by clicking), columns must be reorderable and resizable etc.

10

This is my problem, too. I just saw that in my application, all tables (table view form) can not be sorted by the user on calculated fields, by pressing the column header. I had to save those fields in the database to achieve this, but I think this is not OK for the database model.

So it would be nice for the users to be able to sort on the calculated fields, too.

11

Bogdan,

In a table view, Servoy will only query and show a part of the total data in the table.
Since we do not have all data in memory, we can only sort via the database and therefore we can only do this for columns and stored calcs.

Rob

12

Hello Rob,

But what really happened when user press the column header? You sort locally right? You don’t query the database again adding “order by …”. Or did I understood wrong…

I am curious about the mechanism from background, and if there is something that can be done or if you thought about this issue. I fix this by storing in database the columns I display in the table view, but it’s more elegant to be able to sort directly on not stored fields. :)

Thank you very much,
Bogdan.

13

udrescu_bogdan:
But what really happened when user press the column header? You sort locally right? You don’t query the database again adding “order by …”.

Bogdan,

We sort only in the database, so we query again with adding an order-by clause.

Rob

14

Bogdan,

While we do not support sorting on unstored calculation, you can attach your own logic to the onSort command, which will get executed when a user clicks on a TableHeader.

A form has an onSort command, to which you can attach your own method. When the user clicks the column header, your own method then gets fired instead of performing the default sort behavior and you are in control of taking care of the sorting.

So, if you display an unstored calculation that concatenates first + middle + last name and when the header gets clicked you want to sort on last_name, you programically sort of the dataprovider of the last name.

Paul

15

Thanks Paul, actually I started to store also the calculated fields, but I think the calculation is called each time the form is shown first time and also saves the data in the database, because loading a form now takes lot of time, actually too much. I’ll try to remove the calculated fields from the database and use the onSort method.

16

Everytime records are retrieved from the database and loaded into the memory, Servoy executes the calculations (stored and unstored), so their values are correct. In case of stored calculations, if the value retrieved from the database is not equal to the calculated value, then Servoy will update the database.

Note that Servoy does a lot of things to prevent constant reloading of the same records: once a record is loaded (and thus the calcs are fired) it remains in memory for a while. When the records isn’t shown anymore, hasn’t been touched in scripting for a while and the memory usage of the client goes up, the record might be removed from memory. In the meantime though, the records remains in memory, thus the calcs are not fired constantly.

Paul

17

pbakker:
Everytime records are retrieved from the database and loaded into the memory, Servoy executes the calculations (stored and unstored), so their values are correct. In case of stored calculations, if the value retrieved from the database is not equal to the calculated value, then Servoy will update the database.
…

Paul

Hi Paul,

We are having a problem with the stored calculations, particularly with the updating the database part.
There is no way to capture the moment when the update happens, is there?
onRecordEditStart() is only applicable to forms and is not firing when a stored calculation is re-calculated automatically by Servoy.

In our application we control the saving manually, autoSave is always off.
Thus, at any random moment we might end up having unsaved records in memory - the records that have stored calculations whose values have changed.
This stops us from using a few Servoy functions we are relying on: find(), sort(), loadRecords(), etc.

Can we have more control over what’s happening? I don’t mind changing the stored calculations’ values by Servoy but I need a way of being able to capture when exactly that happens so I can save the changes if I need to.

Cheers,
Maria
Servoy Version: 6.0.5 - build 1230

18

:D :arrow: :(

19

Hi guys,

So what do I do with my changed data when a stored calculation is recalculated and triggers the data change in memory?
Can’t believe no one has had this problem so far…

Cheers,
Maria