Alpha Five to QuickBooks Using QODBC
I resolved an interface issue between Alpha Five and QuickBooks without using QLINKER (I used the QODBC1 driver which had much more flexibility and power). I used this driver plus some ADO methods to add a customer job and invoice record to QuickBooks from Alpha Five.
1 You can find more info about the QODBC driver at http://www.qodbc.com/default.htm
by James Nickel
'Insert Customer/Job and Invoice Records into QuickBooks Pro using QODBC driver for ODBC interface
option strict
dim qb_customer_job as p
dim qb_invoice_line as p
dim invoice_line_cnt as n
dim jobname as c
dim companyname as c
dim fullname as c
dim contact as c
dim city as c
dim state as c
dim postalcode as c
dim address1 as c
dim address2 as c
dim address3 as c
dim customfieldother as c
dim sql as c
dim list_id as c
dim filter_string as c
dim query.filter as c
dim qry as p
dim rec_count as n
dim user_string as c
qb_customer_job = table.open("qb_customer_job.dbf",FILE_RW_SHARED)
filter_string = "User_id = '" + gUser + "'"
query.filter = filter_string
qry = qb_customer_job.query_create()
rec_count = qry.records_get()
if rec_count = 1 then
companyname = trim(qb_customer_job.Companyname)
jobname = trim(qb_customer_job.Name)
fullname = companyname + ":" + jobname
address1 = trim(qb_customer_job.Billaddressaddr1)
address2 = "ATTN: " + trim(qb_customer_job.Billaddressaddr2)
contact = trim(qb_customer_job.Billaddressaddr2)
address3 = trim(qb_customer_job.Billaddressaddr3)
city = trim(qb_customer_job.Billaddresscity)
state = trim(qb_customer_job.Billaddressstate)
postalcode = trim(qb_customer_job.Billaddresspostalcode)
customfieldother = trim(qb_customer_job.Customfieldother)
else
ui_msg_box("QuickBooks","More than one record for user on Customer Job table")
qb_customer_job.close()
end
end if
qb_customer_job.close()
'********************************** Add Customer Job
dim conn as ole::adodb.connection
dim rs as ole::adodb.recordset
dim cs as c
dim cs_admin as c
dim recordsAffected as n = 0
sql = "INSERT INTO Customer (Name, ParentRefFullName,Companyname,Billaddressaddr1,"\
"Contact,Billaddressaddr2,Billaddressaddr3, Billaddresscity, Billaddressstate, Billaddresspostalcode)" \
"VALUES('" + jobname + "','" + companyname + "','" + companyname + "','" + address1 + "'"\
",'" + contact + "','" + address2 + "','" + address3 + "','" + city + "','" + state + "','" + postalcode + "')"
'********************************** debug(1)
user_string = "User ID=" + gUser
if gCompanyCode = "AL" then
cs_admin = gAccessConnectString
else
cs_admin = gPeckConnectString
end if
cs = stritran(cs_admin, "User ID=admin", user_string)
on error goto error_connection
conn.Open(cs)
on error goto 0 ' turn off error trap
hourglass_cursor(.t.)
on error goto error_sql
conn.execute(sql,recordsAffected)
on error goto 0 ' turn off error trap
if recordsAffected = 1 then ' one record has been added
rs.open("select * from customer where name = '" + jobname + "'", conn)
list_id = rs.fields.item("listid").value
dim itemref as c
dim itemdesc as c
dim itemamount as n
'adding invoice detail
qb_invoice_line = table.open("qb_invoice_line.dbf",FILE_RW_SHARED)
query.filter = filter_string
qry = qb_invoice_line.query_create()
rec_count = qry.records_get()
if rec_count > 0 then
qb_invoice_line.fetch_first() 'invoice line detail
while .NOT. qb_invoice_line.fetch_eof()
itemref = Trim(qb_invoice_line.Itemref_fullname)
itemdesc = Trim(qb_invoice_line.Desc)
itemamount = qb_invoice_line.Amount
recordsAffected = 0
'********************************** Insert Invoice detail into QuickBooks cache (FQSaveToCache = True)
invoice_line_cnt = invoice_line_cnt + 1
if itemdesc = "" then
if itemref <> "" then
sql = "INSERT INTO InvoiceLine (Invoicelineitemreffullname, InvoiceLineRate, InvoiceLineAmount, FQSaveToCache)"\
"VALUES('" + itemref + "'," + itemamount + "," + itemamount + ",1)"
end if
else
if itemref <> "" then
sql = "INSERT INTO InvoiceLine (Invoicelineitemreffullname, InvoiceLineDesc, InvoiceLineRate, InvoiceLineAmount, FQSaveToCache)"\
"VALUES('" + itemref + "','" + itemdesc + "'," + itemamount + "," + itemamount + ",1)"
else
sql = "INSERT INTO InvoiceLine (InvoiceLineDesc, FQSaveToCache)"\
"VALUES('" + itemdesc + "',1)"
end if
end if
on error goto error_invoice_insert
conn.execute(sql,recordsAffected)
on error goto 0 ' turn off error trap
if recordsAffected = 1 then
qb_invoice_line.fetch_next()
else
ui_msg_box("QuickBooks","Invoice Detail not posted to QuickBooks")
qb_invoice_line.close()
conn.close()
delete conn
delete rs
end
end if
end while
else
ui_msg_box("QuickBooks","Zero records on invoice detail table")
qb_invoice_line.close()
conn.close() ' close Connection object and any active Recordset objects associated with the connection
delete conn
delete rs
END
end if
'********************************** Insert Invoice Header - this will trigger QuickBooks to
automatically save invoice detail also (held in cache)
if gCompanyCode = "AL" then
sql = "INSERT INTO Invoice (CustomerRefListId, PONumber, Customerreffullname, Billaddressaddr1,"\
"Billaddressaddr2,Billaddressaddr3, Billaddresscity, Billaddressstate, Billaddresspostalcode, FOB)" \
"VALUES('" + list_id + "','" + customfieldother + "','" + fullname + "','" + address1 + "'"\
",'" + address2 + "','" + address3 + "','" + city + "','" + state + "','" + postalcode + "','" + jobname + "')"
else
sql = "INSERT INTO Invoice (CustomerRefListId, PONumber, Customerreffullname, Billaddressaddr1,"\
"Billaddressaddr2,Billaddressaddr3, Billaddresscity, Billaddressstate, Billaddresspostalcode, FOB)" \
"VALUES('" + list_id + "','" + jobname + "','" + fullname + "','" + address1 + "'"\
",'" + address2 + "','" + address3 + "','" + city + "','" + state + "','" + postalcode + "','" + customfieldother + "')"
end if
recordsAffected = 0
on error goto error_invoice_insert
conn.execute(sql,recordsAffected)
on error goto 0 ' turn off error trap
if recordsAffected = 1
ui_msg_box("QuickBooks",str(invoice_line_cnt) + " invoice items posted to QuickBooks")
else
ui_msg_box("QuickBooks","Invoice header not posted to QuickBooks")
end if
qb_invoice_line.close()
else
ui_msg_box("QuickBooks","Customer Job Not Added")
end if
hourglass_cursor(.f.)
conn.close() ' close Connection object and any active Recordset objects associated with the connection
delete conn
delete rs
end
error_connection:
on error goto 0
ui_msg_box("QuickBooks","Unable to connect to QuickBooks")
delete conn
delete rs
end
error_sql:
on error goto 0
ui_msg_box("QuickBooks","Customer Job already exists or Customer Name mis-match; delete customer job or sync customer name and try again")
conn.close()
delete conn
delete rs
end
error_invoice_insert:
on error goto 0
ui_msg_box("QuickBooks","Item category does not exist in QuickBooks: Invoice not posted")
conn.close()
delete conn
delete rs
end