Servoy Forum

Hello,

I want to update one column in my memory foundset without updating the whole foundset.
Is this possible?

Kind regards.

Hi Jeffrey,

as far as I know, a foundset is always something in memory, as it represents a (desired / filtered) subset of the physical database table.
(There can be a foundset without physical table, but I guess that is not something you had in mind.)

Maybe you want to say "I want to update one column in my memory foundset without updating the whole table." ?

In that case you would loop through the foundset, which would look like this.
Note that the getSize() function in the for loop will make sure that all records will be changed, and not only the first 200 (or 70 or whatever) in the foundset which will be loaded initially.

Code: Select all

   
for (i = 1; i <= fs.getSize(); i++) {
      
   rRec = fs.getRecord(i);

   rRec.yourFieldName = "yourNewValue";
}


My personal preference is to not use such a loop in cases where I have to update a lot of records (which happens seldom), as they are slower than SQL, but to use just plain SQL UPDATES inside Servoy, like this (sSQL contains the SQL-Query-String):

Code: Select all

plugins.rawSQL.executeSQL(scopes.utils.DB.SERVER, null, sSQL);

Hi Bernd,

We are using a foundset without a physical table.
Tomorrow i will try the for loop i will let you know if it worked.

Ok, in that case you would have a specific IF-condition that you use inside the loop.
Only when that IF is true, a record of the foundset will get changed.
But you would loop through the complete foundset to inspect all records.

Dear jeffrey,

Have you already tried with the foundsetUpdater?

It's very fast. Y
You can use it as follow:

Code: Select all

var updater = databaseManager.getFoundSetUpdater(foundsetYouWantToUpdate)
     updater.setColumn("columnName","valueYouWantToInser")
     updater.performUpdate()

I haven't used it to update a foundset not linked to any table but it should work anyway.

Regards

Marco

Bernd.N wrote:Ok, in that case you would have a specific IF-condition that you use inside the loop.
Only when that IF is true, a record of the foundset will get changed.
But you would loop through the complete foundset to inspect all records.

Thank you very much!!
I fixed it with a foundset filter.

This is how i coded the function.

Code: Select all

fs.addFoundSetFilterParam('print_or_email', '=', 0, 'filterPrintOrEmail');
fs.loadAllRecords();


marco.rossi wrote:Dear jeffrey,

Have you already tried with the foundsetUpdater?

It's very fast. Y
You can use it as follow:

Code: Select all

var updater = databaseManager.getFoundSetUpdater(foundsetYouWantToUpdate)
     updater.setColumn("columnName","valueYouWantToInser")
     updater.performUpdate()

I haven't used it to update a foundset not linked to any table but it should work anyway.

Regards

Marco

I did try it like that but it didn't update as i wanted

There is an article from Gary regarding looping through foundsets that might be interesting for you:

Code: Select all

http://www.dotzlaw.com/tutorials/servoy-tutorials/servoy-tutorial-optimizing-code-performance/