Looping through all records to update a calculation

Classic Servoy
1

Hi all

OS X 10.3.8 with Servoy 2.2 rc3. I have a sybase solution related to a MySQL database which has two tables. The MySQL database drives a website. Sybase handles a contact management form and a stock book (items for sale - with pictures, prices, details, lengths etc.) In the main Sybase stock book, you can press a button “add to website” which creates the related record in MySQL and the item is instantly on the site via php. there is also a remove from website - and sold items are automatically removed from the website (the stock book marks them as sold and the php search excludes sold items. One function of this system is to present prices in and $. My stock book (and related MySQL database) have a price which is then multiplied by an “exchange rate” which is stored by keeping it in a further related sybase table (administration). Administration is a form / table which allows me as a developer to sell this same solution to more than one customer, there is a relationship from all accessible forms to my private administration form which stores the database “owner” and preferences for their solution (such as exchange rate). The trouble I have is that although the price is multiplied by the exchange rate perfectly, it doesn’t happen until I visit the record (not load the record as part of a found set - but rather actually view the record.) This calculation therefore does not affect all $ prices on the sybase (or MySQL) database when I change the exchange rate other than on the record I am on (and any other record that I load and view). My question is what loop should I use to go through all records to actively change the calculated result?

Thanks for any help you can give, sorry my question is convoluted, just trying to provide all the facts.

Bevil Templeton-Smith

2

Hi Bevil,

Yes, you need to loop through the records to “trigger” the recalculation (it doesn’t work like FMP and recalc everything based on that one change in exchange rate).

3

Hi Bob,

thanks for the reply. I have tried looping through the records along these lines:

controller.loadAllrecords()
for (var i 1; i<=foundset.getSize(); i++)
{
var record = foundset.getRecord (i)
controller.relookup()
}

I have tried a savedata rather than a relookup, and I have tried simply looping through and not actually doing anything. None of these make any difference (and it is hard to see that because when you actually view a record, the data is correctly calculated, it seems to calculate just before showing the data, rather than on all records behind the scenes.) The problem is actually visible when the data is displayed in a web page where the $ amount has not been calculated to be the result of * exchange rate. It SEEMS to be accurate in Servoy because as you go to the record it calculates and shows the correct data, subsequently, each record that has been seen in Servoy has the correct price on the web. It is just getting it to actually loop through and check the calculation without loading and viewing each record…

4

Hello,

there is currently an issue when calculation data that is not shown anywhere. The dev team is (hopefully) investigating the possibility of a refreshCalc(foundset) function, that would allow to refresh all calcs in a foundset. Just looping through records WILL NOT necessarily force Servoy to refresh all calcs. See for example Calculations not updated - Classic Servoy - Servoy Community

Cheers
Patrick

5

Are you looping through the records on the same form as the calc is displayed?

You can also try this:

controller.loadAllrecords()
for (var i = 1; i<=foundset.getSize(); i++)
{
     foundset.setSelection(i)

}

This will loop through and “touch” all the records, but won’t change the currently selected row.

Also - are you using 2.2RC3?

6

Thanks Bob, that nailed it. I don’t know why it was not working before. setSelection did not work (“setselection is not a function”) so I looked it up and found setSelectedIndex(). Tried it and it worked, I assume that was what you meant.

7

:oops:

Sorry- I should have told you it was “pseudo-code”.

I’m glad you figured it out… :D