Create customers from the work reports
In this example we will see how to create customers starting from work reports. Usually this operation is done because the technicians insert a new customer into the report without creating it through a subtable.
Another case is that reports are imported from an Excel file created by another program and therefore customer are not created. Of course it is assumed that on each row of the report there are the main data to be able to identify the customer if it already exists or if it must be created.
table = database.getsql("SELECT * FROM reports WHERE eli=0")
rows = table.getrows()
for i = 1,table.countrows() do
--extrapolate the customer
namecustomer = rows[i].getvalue("namecustomer")
gguid_namecustomer = rows[i].getvalue("gguid_namecustomer")
--if gguid is empty the customer not exist
if gguid_namecustomer = = "" then
--check if customer is already exist
tablecustomer = database.getsql("SELECT * FROM customers WHERE eli=0 and name='" .. utility.convap(namecustomer) .. "'")
if tablecustomer.countrows() == 0 then
--if 0 then customer not exist
--create a head customer
gguid_namecustomer = utility.gguid()
values = "INSERT INTO customers(gguid,tid,eli,arc,ut,uta,exp,gguidp,ind,tap,dsp,dsc,dsq1,dsq2,utc,tidc) VALUES ("
values = values .. "'" .. gguid_namecustomer .. "'," .. tostring(utility.tid()) .. ",0,0,'','','',''," .. tostring(database.getind("customers")) .. ",'','','',0,0,'',0)"
--update data customers
values = " UPDATE customers SET name='" .. utility.convap(namecustomer) .. ",address='" .. utility.convap(rows[i].getvalue("address")) .. " WHERE gguid='" .. gguid_namecustomer .. "'")
--customer already exist, update the gguid for create relation
rows2 = tablecustomer.getrows()
gguid_namecustomer = rows2.getvalue("gguid")
--update for syncbox windows
database.setsql("UPDATE report SET tid=" .. tostring(utility.tid()).. ",gguid_namecustomer='" .. gguid_namecustomer .. "'")
--add a syncbox for mobile
First we create the reading cycle on the reports and examine only those that do not have a valid customer gguid connected.
Since maybe another report has already created this customer before we proceed to do a search on this on the name.
If even the name did not bring anything then we create the customer by recovering the data available on the reports.
Once this is done, we update the reports to have the relationship with the customer through his gguid.
All this works if in any case the customer on the report is managed with a field of type subtable.