Page 1 of 1

Update values on each record (table view)

PostPosted: Tue Jun 23, 2015 9:44 am
by gdeloule
Hello,

I would like to update every row in a table (table view). To illustrate my request, in the database I have a column "last name" and "first name" column. I would concatenate these two values in a single column called "identity."

Thank you for your help!

Re: Update values on each record (table view)

PostPosted: Tue Jun 23, 2015 1:03 pm
by ROCLASI
Hi,

First of all welcome to the forum. I see this is your first post.

So I guess the question is what the use-case for this update is. There are many many answers to your question and the all depend on what problem you try to solve.

For example; do you really want to update the table or is it just that you want to show the concatenated data in one field.
Then you can use an UN-STORED calc like so:
Code: Select all
function myUnStoredCalc() {
    return first_name + " " + last_name;

    // or if the might have nulls in them or extra spaces you can use the following code:
    // return utils.stringTrim(utils.stringTrim(first_name||'') + " " + utils.stringTrim(last_name||''));
}

This will calculate the concatenated value the moment you need to show it.


If it's truly a one-off update you could simply run a SQL query using any SQL editor using the following SQL:
Code: Select all
UPDATE myTableName SET identity = first_name||' '||last_name

This of course assuming you use PostgreSQL. Not all DB vendors support the || as a concatenation function. Other might use + or the function CONCAT(val1, val2).
After you done this update you might want to restart Servoy for when it already loaded that data (and therefor cached it).

Now, if you really need to automate this update in a solution you might ask yourself why not fill the identity field the moment you enter the record into the table :)
But if you do need to do this update batch-wise you might want to take a look at the JSFoundSetUpdater
Another way would be to loop over the foundset and do the update or to use the rawSQL plugin and do the aforementioned UPDATE statement.
But only the FoundSetUpdater and the FoundSet will automatically handle any invalidation of already cached data by Servoy.

There is also the option of using a STORED calculation but I would recommend against that. Sure it does full your column with the concatenated data but it is pretty costly. It gets triggered at moments that you don't expect it to. The best way to store any calculated values is the moment you save your data like with a method.

So you see, many options that all depend on what problem you try to solve.

Hope this helps.