Calculations (again) and imported data

Classic Servoy
1

Following up from another post of mine, I have now imported a large amount of data into a solution that is replacing a FileMaker system.
The main data that I have imported is one years worth of orders with their associated line items. In the line_items file I have some calcs. (stored) that calculate the totals for that line (qty*price,vat etc., the usual) and some aggregates (summaries). The orders file has a relation to the line_items and is meant to display the invoice/order totals underneath a portal to line_items, as well as inv. total in a table view. The problem is that unless I actually go into each order AND scroll thru all line_items, the line_items calcs. aren’t calculating. If I just view an order that has more lines in the portal than are displayed on screen, the aggregates only calculate on the lines that are shown. As soon I scroll the portal to the bottom, they update correctly! Obviously when the system is being used in the future this won’t be a problem, but I have to load it up with hundreds of thousands of records, and it’s going to be a bit of a pain to try and sort this out!
In the image you can see a portal with lots of lines and the totals underneath. The top totals are the new Servoy aggregates, the lower ones were imported from the FM system. As you can see, they don’t match! If I was to scroll the portal to the bottom, they suddenly update. I also have a form showing a table view with one of these aggregates, aand unless I’ve ‘touched’ the record, it is blank!
Please can someone enlighten me as to what I can do to fix this. I have seen on other posts that some calcs. don’t fire unless the record is ‘touched’ (which I have seen), but how can I do this easily, quickly and automatically. I know that I can use the back-end tools (iSQL) to do an ‘UPDATE’, but I’d like to think that Servoy could show sums and calcs properly without that.
Thanks,
Rafi.

2

can’t you write a simple method that does this:

for(var i=1;i<foundset.getSize();i++)
{
  var record = foundset.getRecord(i);
  var tmp = record.calculation;
}

It will run for a while but then it is done for everything at once.

if it is a relation then loop through those:

for(var i=1;i<foundset.getSize();i++)
{
  var record = foundset.getRecord(i);
  var relation = record.relationName;
  for(var k=1;k<relation.getSize();k++)
  {
    var relationRecord = relation.getRecord(k)
    var tmp = relationRecord.calculation;
  }
}
3

Thanks for that suggestion, but it doesn’t seem to work.
The only way I can get it to update all calculations and aggregates is to create a form that has all the elements involved on it, and then slowly loop thru them all, doing an ‘application.updateUI()’ in the loop.
Even then, sometimes the UI doesn’t update properly, so some figures do not get set properly and I have to do them again!
As I said, I know that once this goes into production, the data will be being entered and so the calcs will fire and all should be fine, but at the moment it is a real P.I.T.A. (pain in…).
There should be a way to allow calcs etc. to fire without them having to be visible. I even had to create a tab panel with the line item calcs. in it, but each field was only 4 pixels high to allow in a small panel for enough lines.
I understand some of this is probably down to not wanting to do stuff across massive foundsets, but if there was a way, that worked, like you suggested that can happen quickly across the current foundset, that would be great.

4

but that is impossible if you really ask for it then it has to be calculatee

for example if you do this:

application.output(record.calculation1)

then you should see the right calculated value

5

but that is impossible if you really ask for it then it has to be calculatee

for example if you do this:

application.output(record.calculation1)

then you should see the right calculated value

If I put that in a method that I call via a button, it only puts in the ‘total’ from the first related record.
Let me clarify. I am using a modified version of the example CRM system you supply with Servoy. I have imported data into the line_items file (250k records) and into the orders file (60k records). I make sure that the two files are linked together by ordersid which is the key for orders_to_line_items. If I then look at the orders form, the last column, which is meant to show the total for the order DOESN’T! If I then view that record by clicking the triangle and go to the orderDetails form and make sure I look at all related lines, the total updates. If I just call your app.output when on the orders form, it only loads up the total for the first line.
Please try for yourself by importing some data into line_items and the corresponding orders for those lines and then you will see what I mean.
All data for all parts of a calculation and aggregate must be shown for all of it to properly update. This is obviously an problem for other people like me who are importing a lot of data into a solution. I’m just surprised no one else has come across this.
Please let me know what you think.
Thanks.

6

then do what i say in an example above..
walk through the related records by script

for(var i=1;i<foundset.getSize();i++) 
{ 
  var record = foundset.getRecord(i); 
  var relation = record.orders_to_line_items; 
  for(var k=1;k<relation.getSize();k++) 
  { 
    var relationRecord = relation.getRecord(k) 
   application.output(relationRecord .calculation1) 
  } 
}

this code will touch every related record for every order!!

7

I think that this ‘problem’ has come up before several times and that similar solutions were tendered.

See : http://forum.servoy.com/viewtopic.php?t=2066&highlight= as an example

The hit seems to be taken at import and then by running a method to step through each record to update the imported data calculations.

Once the initial pain is over the natural record creation methodology takes over

Cheers - with no brighter news !
Harry

8

Thanks Harry & Johan.
I’m not sure what has happened, but now even the app.output is not working. I’m just getting NULLs in the output and no figures are appearing. I tried adding in a
controller.recordIndex = i ;
into the loop, didn’t help.
then tried adding
controller.loadRecords() ;
but this brought back all records (I had found just one days worth)
then tried
controller.loadRecords(orders_to_line_items) ;
but this gives me an error

Error setting form foundset, Can’t set a foundset from table line_items into a form using table orders
Can’t set a foundset from table line_items into a form using table orders

I had thought this would load up the related records… but I can’t seem to get it to work.
Anyway, basically it’s still not working based on this thread, but it mostly works when I slowly loop thru on a different form with all fields.
(I’m stumped though as to why the app.output worked earlier and doesn’t now… I’m using the same environment, although I was at the client’s earlier and am now at home, so my physical environment has changed :shock: )