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.

Script Group

Program

Event

Action

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)"

database.setsql(values)

--update data customers

values = " UPDATE customers SET name='" .. utility.convap(namecustomer) .. ",address='" .. utility.convap(rows[i].getvalue("address")) .. " WHERE gguid='" .. gguid_namecustomer .. "'")

database.setsql(values)

database.addsyncbox("customers",gguid_namecustomer)

else

--customer already exist, update the gguid for create relation

rows2 = tablecustomer.getrows()

gguid_namecustomer = rows2[1].getvalue("gguid")

end

--update for syncbox windows

database.setsql("UPDATE report SET tid=" .. tostring(utility.tid()).. ",gguid_namecustomer='" .. gguid_namecustomer .. "'")

--add a syncbox for mobile

database.addsyncbox("reports",gguid_namecustomer)

end

end

program.refreshsection("reports")

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.