no calculations through a join table unless I visit the form

Classic Servoy
1

Hi all

I have an ‘invoice’ form which collects lines from line_items which belong to this invoice_id. In between invoice table and line_items table, I have an invoice_join table so that I can store a history of all the invoices and credits that a particular line has been on. As I select an invoice (onRecordSelection), I do sql query to load the relevant lines into the invoice (on an unrelated tabpanel) using a sql join. All good so far.

The invoice form though has to show total, total vat, and a grand total which are currently aggregates in the line_items table on the basis of whether or not they should have VAT etc (vatable is a check field per line).

The whole thing works fine and calculates correctly if I first visit the invoice credit join table - which has a calculation which gets the aggregates from line items. IF I don’t however (and the user won’t), the form is not shown, and the calculation is not triggered, and the values are not updated. Therefore, if a line has vat ticked, and then has it later unticked, the vat value remains.

I have tried EVERY POSSIBLE thing that I can think of (sql loading a dataset, then looping through it; doing recalculates; doing a find() and search() on the relevant join table form and looping through the records; putting the join form’s fields onto my invoice form to force them to be seen). NOTHING works and I need a new set of Servoy brains.

Please could someone tell me the obvious one liner that I am missing and have been for 2 days now…

Thanks

Bevil

2

just for testing, have you tried putting these aggreates inside your lines? visible?
if calc’s are not visible, they never will be fired. (only if you update something else in that record)

just looping over the lines, and touching them, should fire the calc’s also.

3

Hi Harjo

I did put the calcs visible on the lines. They don’t update, I think because it is a calc to a join table to an aggregate on a 3rd table.

I also tried looping and touching each record, like this:

foundset.loadAllRecords()
foundset.setSelectedIndex(1)
for(var i=1;i<=foundset.getSize();i++) 
{ 
foundset.getRecord(i); 
}

It didn’t work

I have now got it semi working by doing a sql update on the line_item lines in question and setting a temporary_invoice_id, then setting a form variable to the aggregated value on the basis of a relation from the invoice number to the temp invoice number on line items (this cuts out the invoice join), and then doing a sql update to clear the temp_invoice_id from the lines. It is REALLY kludgey though, and I have to do application.sleep(200) to let the calculations and relations catch up with themselves otherwise it doesn’t load lines for the invoice etc.

It almost works, almost all of the time, but it is slow and nasty…

I despair.

:(

Bevil

4

I can’t oversee exactly what you are doing.

maybe post a small sample solution, what exactly replicate this, and post it here.
I’ll bet someone (or me ;-) ) can help you out…

5

Hey Harjo…

It is so complicated (for me) that I can’t oversee it either (I was trying to finish this solution at ServoyCamp, still trying to finish it…) its hard to think of what I’ve named fields through a join table…

It does now work with a 200msec delay. Its not ideal. I’ll see if I can make it into a sample solution.

To simplify what I’m asking:

Form A: Table A; Form B: Table B; Form C: Table C

C has lines
A has invoices
B has joins (many As and Cs) so that one invoice (eg A1) will have one line (eg C3), and another invoice (A6) may have the same line (C3)

I want to see on Form A a total (aggregate) of lines on Table C, but there is no data to link between A and C, it HAS to go through B, otherwise the data is wrong

Does that simplify?

6

Bevil,

What version of Servoy are you using?

Servoy 5.1 has some fixes related to updating of calculations.

Rob

7

Hi Rob

Sadly I’m about to deploy it in 4.1.

I tried to initially deploy in 5 but it killed too many things for me to be able to face given the time I had available.

I have little choice but to deploy in 4.

Worrying though is the prospect that it is not possible to do what I want… Is this a complicated thing?

8

Bevil,

The fixes I was talking about were related to issues introduced in 5.0.

Rob