Update the customer's name inside the documents

In this example we will see how to quickly update the data inside our database in a few rows.

Script Group

Program

Event

Program action

As you know, the tables are connected to each other through the sepcial field SubTable, this allows to select a row of values from a different table than the one managed by the current form. The choise of this row then allows to automatically fill in the desired fields. since it is a copy of the data and not a link, if for example the customer's name was changed from is form, the old one would still remain inside the documents where it is recalled. Thanks to this script we will be able to realign the name inside the document.

To use this script is necessary to treat it as an action on the program, thus allowing us to launch it directly when we want.

table_rows = database.getsql("SELECT * FROM names")

nrows = table_rows.countrows()

rows = table_rows.getrows()

First of all let's create the names table, where are entered our customer by retrieving the rows and their numbers.

for i = 1,nrows do

gguid = rows[i].getvalue("gguid")

name = rows[i].getvalue("name")

database.setsql("UPDATE invoices set customer='" .. name .. "' WHERE gguid_name='" .. gguid .. "'")

end

In this cycle we recover the name's gguid and its name in order to be able to re-enter it within the invoices that use it, so that it is updates.

The system is certainly very fast, but it has some problems.

The first is that, writing the sql query in this way, if the customer name had a quote inside, it would generate an error. To manage it, remember that it is sufficient to double them or use the function convap as in this example:

"update invoices set customers='" ... utility.convap(name) .. "' where ...."

The second problem is that if it were a Cloud database, the changes made would not be synchronized. Synchronization is in fact based on other values and functions.

If you want to synchronize, this system would still be wrong because, after updating data it would no longer be possible to check which ones have changed. This would mean sending all customers back to the server despite no changes being made to them.

If data synchronization is required, there are always two steps to be done through scripts.

database.setsql("UPDATE invoices SET tid=" .. tostring(utility.tid()) .. " WHERE gguid_name='" .. gname .. "'")

First you need to upadate the tid with the current date bacuse it is the temporal register of the data row.

During the synchronization phase, the program checks which rows have been updates since the last time and sends only those.

table_rows = database.getsql("SELECT * FROM invoices WHERE gguid_name=' " .. gname .. "'")

nrows = table_rows.countrows()

rows = table_rows.getrows()

for i = 1,nrows do

database.addsyncbox("invoices",rows[i].getvalue("gguid"))

end

The second operation is actually only performed if the script must also be used on mobile devices. These, in fact, have a synchronizations system that classifies the data to be sent in a slightly different way.

In this part of the script all the invoices rows that our customer uses, are retrieved and are marked to be sent for synchronization with the command addsyncbox.

Last note: remember that as a filter it is always a good rule to add "and eli=0" to avoid recovering the data that are currently trashed.