The code shown below is designed to enter an invoice with 11 Line Items into QBES V8 using Alpha Five V9. The table "multi_invoices" is filtered by refnumber 2811 and is sorted so that "Fqsavetocache" is set to 1 for the first 10 records and 0 for the last record. When I run the code I get 11 "Record was created" messages and no errors, but when I look at the invoice in QBES it shows one invoice with 1 line item - the last one in the series.
The following quote is directly from this Forum:
"Multi-line invoices require a series of SQL statements to complete a single invoice. In the example below we are creating a 3 line invoice using 3 SQL INSERT commands in sequence. The key to this process is the field named "FQSaveToCache". This field is not part of the table, but is used as a flag to the QODBC driver. In the sequence below, you should note that the value of "FQSaveToCache" is set to 1 or TRUE for the first two line item insert statements, and then it is set to 0 or FALSE for the final statement.
A TRUE setting of "FQSaveToCache" instructs QODBC to take the values from your INSERT statement and hold them for later processing, but not to save them to QuickBooks yet. When QODBC receives the final transaction where the cache is set to 0 or FALSE, the contents of the current INSERT statement will be combined with all of the previous INSERT statements held in the cache for this connection, and saved as a batch into QuickBooks."
It seems to me that my code adheres to the method described above
I would really appreciate it if someone could please show me what I'm doing wrong here.
THANKS
JAS
code = UI_YES_NO+UI_ATTENTION_SYMBOL + UI_SECOND_BUTTON_DEFAULT
result = ui_msg_box("","This action will EXPORT ALL OPEN INVOICES TO QBES. CONTINUE?",code)
IF result = UI_NO_SELECTED THEN
goto finish2
end
END IF
Dim Shared vCustomerreflistid as C
Dim Shared vCustomerreffullname as C
Dim Shared vTxndate as D
Dim Shared vRefnumber as C
Dim Shared vBilladdressaddr1 as C
Dim Shared vBilladdressaddr2 as C
Dim Shared vBilladdressaddr3 as C
Dim Shared vBilladdressaddr4 as C
Dim Shared vBilladdressaddr5 as C
Dim Shared vBilladdresscity as C
Dim Shared vBilladdressstate as C
Dim Shared vBilladdresspostalcode as C
Dim Shared vBilladdresscountry as C
Dim Shared vShipaddressaddr1 as C
Dim Shared vShipaddressaddr2 as C
Dim Shared vShipaddressaddr3 as C
Dim Shared vShipaddressaddr4 as C
Dim Shared vShipaddressaddr5 as C
Dim Shared vShipaddresscity as C
Dim Shared vShipaddressstate as C
Dim Shared vShipaddresspostalcode as C
Dim Shared vShipaddresscountry as C
Dim Shared vInvoicelineitemreflistid as C
Dim Shared vInvoicelineitemreffullname as C
Dim Shared vInvoicelinedesc as C
Dim Shared vInvoicelinequantity as N
Dim Shared vInvoicelinerate as N
Dim Shared vInvoicelineamount as N
Dim Shared vFqsavetocache as N
dim total_records as N
dim iterations as N = 0
dim t as p
t=table.open("multi_invoices")
query.order="Txnid+Cache_order"
query.filter="refnumber=\"2811\""
indx=t.query_create()
total_records =indx.records_get()
t.fetch_first()
WHILE .not. t.fetch_eof()
iterations = iterations + 1
statusbar.SET_text("Working - records processed: " + alltrim(str(iterations,5,0))+" of"+alltrim(str(total_records,5,0)))
vCustomerreflistid = t.CUSTOMERMSGREFLISTID
vCustomerreffullname = t.CUSTOMERREFFULLNAME
vTxndate = t.TXNDATE
vRefnumber = t.REFNUMBER
vBilladdressaddr1 = t.BILLADDRESSADDR1
vBilladdressaddr2 = t.BILLADDRESSADDR2
vBilladdressaddr3 = t.BILLADDRESSADDR3
vBilladdressaddr4 = t.BILLADDRESSADDR4
vBilladdressaddr5 = t.BILLADDRESSADDR5
vBilladdresscity = t.Billaddresscity
vBilladdressstate = t.Billaddressstate
vBilladdresspostalcode = t.Billaddresspostalcode
vBilladdresscountry = t.Billaddresscountry
vShipaddressaddr1 = t.SHIPADDRESSADDR1
vShipaddressaddr2 = t.SHIPADDRESSADDR2
vShipaddressaddr3 = t.SHIPADDRESSADDR3
vShipaddressaddr4 = t.SHIPADDRESSADDR4
vShipaddressaddr5 = t.SHIPADDRESSADDR5
vShipaddresscity = t.Shipaddresscity
vShipaddressstate = t.Shipaddressstate
vShipaddresspostalcode = t.Shipaddresspostalcode
vShipaddresscountry = t.Shipaddresscountry
vInvoicelineitemreflistid = t.Invoicelineitemreflistid
vInvoicelineitemreffullname = t.Invoicelineitemreffullname
vInvoicelinedesc = t.Invoicelinedesc
vInvoicelinequantity = t.Invoicelinequantity
vInvoicelinerate = t.Invoicelinerate
vInvoicelineamount = t.Invoicelineamount
vFqsavetocache = t.FQSAVETOCACHE
'Insert a new record into a remote SQL database.
'DIM a connection variable
DIM cn as SQL::Connection
dim flagResult as l
flagResult = cn.open("::Name::QB_Test")
IF flagResult = .f. THEN
ui_msg_box("Error","Could not connect to database. Error reported was: " + crlf() + cn.CallResult.text)
end
END IF
'Specify that we are using Portable SQL syntax
cn.PortableSQLEnabled = .t.
'Dim a SQL arguments object, create arguments and set their values
DIM args as sql::arguments
IF a5_eval_valid_expression("=Var->vCustomerreflistid",local_variables()) THEN
args.add("CustomerRefListID",convert_type(a5_eval_expression("=Var->vCustomerreflistid",local_variables()),"C"))
END IF
IF a5_eval_valid_expression("=Var->vCustomerreffullname",local_variables()) THEN
args.add("CustomerRefFullName",convert_type(a5_eval_expression("=Var->vCustomerreffullname",local_variables()),"C"))
END IF
IF a5_eval_valid_expression("=Var->vTxndate",local_variables()) THEN
args.add("TxnDate",convert_type(a5_eval_expression("=Var->vTxndate",local_variables()),"D"))
END IF
IF a5_eval_valid_expression("=Var->vRefnumber",local_variables()) THEN
args.add("RefNumber",convert_type(a5_eval_expression("=Var->vRefnumber",local_variables()),"C"))
END IF
IF a5_eval_valid_expression("=Var->vBilladdressaddr1",local_variables()) THEN
args.add("BillAddressAddr1",convert_type(a5_eval_expression("=Var->vBilladdressaddr1",local_variables()),"C"))
END IF
IF a5_eval_valid_expression("=Var->vBilladdressaddr2",local_variables()) THEN
args.add("BillAddressAddr2",convert_type(a5_eval_expression("=Var->vBilladdressaddr2",local_variables()),"C"))
END IF
IF a5_eval_valid_expression("=Var->vBilladdressaddr3",local_variables()) THEN
args.add("BillAddressAddr3",convert_type(a5_eval_expression("=Var->vBilladdressaddr3",local_variables()),"C"))
END IF
IF a5_eval_valid_expression("=Var->vBilladdressaddr4",local_variables()) THEN
args.add("BillAddressAddr4",convert_type(a5_eval_expression("=Var->vBilladdressaddr4",local_variables()),"C"))
END IF
IF a5_eval_valid_expression("=Var->vBilladdressaddr5",local_variables()) THEN
args.add("BillAddressAddr5",convert_type(a5_eval_expression("=Var->vBilladdressaddr5",local_variables()),"C"))
END IF
IF a5_eval_valid_expression("=Var->vBilladdresscity",local_variables()) THEN
args.add("BillAddressCity",convert_type(a5_eval_expression("=Var->vBilladdresscity",local_variables()),"C"))
END IF
IF a5_eval_valid_expression("=Var->vBilladdressstate",local_variables()) THEN
args.add("BillAddressState",convert_type(a5_eval_expression("=Var->vBilladdressstate",local_variables()),"C"))
END IF
IF a5_eval_valid_expression("=Var->vBilladdresspostalcode",local_variables()) THEN
args.add("BillAddressPostalCode",convert_type(a5_eval_expression("=Var->vBilladdresspostalcode",local_variables()),"C"))
END IF
IF a5_eval_valid_expression("=Var->vBilladdresscountry",local_variables()) THEN
args.add("BillAddressCountry",convert_type(a5_eval_expression("=Var->vBilladdresscountry",local_variables()),"C"))
END IF
IF a5_eval_valid_expression("=Var->vShipaddressaddr1",local_variables()) THEN
args.add("ShipAddressAddr1",convert_type(a5_eval_expression("=Var->vShipaddressaddr1",local_variables()),"C"))
END IF
IF a5_eval_valid_expression("=Var->vShipaddressaddr2",local_variables()) THEN
args.add("ShipAddressAddr2",convert_type(a5_eval_expression("=Var->vShipaddressaddr2",local_variables()),"C"))
END IF
IF a5_eval_valid_expression("=Var->vShipaddressaddr3",local_variables()) THEN
args.add("ShipAddressAddr3",convert_type(a5_eval_expression("=Var->vShipaddressaddr3",local_variables()),"C"))
END IF
IF a5_eval_valid_expression("=Var->vShipaddressaddr4",local_variables()) THEN
args.add("ShipAddressAddr4",convert_type(a5_eval_expression("=Var->vShipaddressaddr4",local_variables()),"C"))
END IF
IF a5_eval_valid_expression("=Var->vShipaddressaddr5",local_variables()) THEN
args.add("ShipAddressAddr5",convert_type(a5_eval_expression("=Var->vShipaddressaddr5",local_variables()),"C"))
END IF
IF a5_eval_valid_expression("=Var->vShipaddresscity",local_variables()) THEN
args.add("ShipAddressCity",convert_type(a5_eval_expression("=Var->vShipaddresscity",local_variables()),"C"))
END IF
IF a5_eval_valid_expression("=Var->vShipaddressstate",local_variables()) THEN
args.add("ShipAddressState",convert_type(a5_eval_expression("=Var->vShipaddressstate",local_variables()),"C"))
END IF
IF a5_eval_valid_expression("=Var->vShipaddresspostalcode",local_variables()) THEN
args.add("ShipAddressPostalCode",convert_type(a5_eval_expression("=Var->vShipaddresspostalcode",local_variables()),"C"))
END IF
IF a5_eval_valid_expression("=Var->vShipaddresscountry",local_variables()) THEN
args.add("ShipAddressCountry",convert_type(a5_eval_expression("=Var->vShipaddresscountry",local_variables()),"C"))
END IF
IF a5_eval_valid_expression("=Var->vInvoicelineitemreflistid",local_variables()) THEN
args.add("InvoiceLineItemRefListID",convert_type(a5_eval_expression("=Var->vInvoicelineitemreflistid",local_variables()),"C"))
END IF
IF a5_eval_valid_expression("=Var->vInvoicelineitemreffullname",local_variables()) THEN
args.add("InvoiceLineItemRefFullName",convert_type(a5_eval_expression("=Var->vInvoicelineitemreffullname",local_variables()),"C"))
END IF
IF a5_eval_valid_expression("=Var->vInvoicelinedesc",local_variables()) THEN
args.add("InvoiceLineDesc",convert_type(a5_eval_expression("=Var->vInvoicelinedesc",local_variables()),"M"))
END IF
IF a5_eval_valid_expression("=Var->vInvoicelinequantity",local_variables()) THEN
args.add("InvoiceLineQuantity",convert_type(a5_eval_expression("=Var->vInvoicelinequantity",local_variables()),"N"))
END IF
IF a5_eval_valid_expression("=Var->vInvoicelinerate",local_variables()) THEN
args.add("InvoiceLineRate",convert_type(a5_eval_expression("=Var->vInvoicelinerate",local_variables()),"N"))
END IF
IF a5_eval_valid_expression("=Var->vInvoicelineamount",local_variables()) THEN
args.add("InvoiceLineAmount",convert_type(a5_eval_expression("=Var->vInvoicelineamount",local_variables()),"N"))
END IF
IF a5_eval_valid_expression("=Var->vFqsavetocache",local_variables()) THEN
args.add("FQSaveToCache",convert_type(a5_eval_expression("=Var->vFqsavetocache",local_variables()),"N"))
END IF
dim sqlInsertStatement as c
sqlInsertStatement = <<%txt%
INSERT INTO InvoiceLine (CustomerRefListID, CustomerRefFullName, TxnDate, RefNumber, BillAddressAddr1, BillAddressAddr2, BillAddressAddr3, BillAddressAddr4, BillAddressAddr5, BillAddressCity, BillAddressState, BillAddressPostalCode, BillAddressCountry, ShipAddressAddr1, ShipAddressAddr2, ShipAddressAddr3, ShipAddressAddr4, ShipAddressAddr5, ShipAddressCity, ShipAddressState, ShipAddressPostalCode, ShipAddressCountry, Invoicelineitemreflistid, Invoicelineitemreffullname,InvoiceLineDesc, InvoiceLineQuantity, InvoiceLineRate, InvoiceLineAmount, FQSaveToCache) VALUES (:CustomerRefListID, :CustomerRefFullName, :TxnDate, :RefNumber, :BillAddressAddr1, :BillAddressAddr2, :BillAddressAddr3, :BillAddressAddr4, :BillAddressAddr5, :BillAddressCity, :BillAddressState, :BillAddressPostalCode, :BillAddressCountry, :ShipAddressAddr1, :ShipAddressAddr2, :ShipAddressAddr3, :ShipAddressAddr4, :ShipAddressAddr5, :ShipAddressCity, :ShipAddressState, :ShipAddressPostalCode, :ShipAddressCountry, :Invoicelineitemreflistid, :Invoicelineitemreffullname, :InvoiceLineDesc, :InvoiceLineQuantity, :InvoiceLineRate, :InvoiceLineAmount, :FQSaveToCache)
%txt%
dim flag as l
flag = cn.Execute(sqlInsertStatement,args)
IF flag = .f. THEN
ui_msg_box("Error","Record was not inserted. Error reported was: " + crlf(2) + cn.CallResult.text,UI_STOP_SYMBOL)
ELSE
IF cn.AffectedRows() = 1 THEN
'ui_msg_box("Notice","Record was created.",UI_INFORMATION_SYMBOL)
ELSE
ui_msg_box("Error","Record was not inserted." ,UI_STOP_SYMBOL)
END IF
END IF
'Now, close the connection
cn.close()
t.fetch_next()
END WHILE
finish:
indx.drop()
t.close()
ui_msg_box("","Operation Complete!")
END
finish2:
ui_msg_box("","Operation Cancelled!")[/CODE] |
