Visual FoxPro
Visual FoxPro
Quickbooks Query an Invoice
See more QuickBooks Examples
Demonstrates how to query for invoices matching a SELECT statement via the Quickbooks REST API.Chilkat Visual FoxPro Downloads
LOCAL lnSuccess
LOCAL loJsonToken
LOCAL loRest
LOCAL lnBTls
LOCAL lnPort
LOCAL lnBAutoReconnect
LOCAL loSbAuth
LOCAL loSbResponseBody
LOCAL lnRespStatusCode
LOCAL loJson
LOCAL lcDocNumber
LOCAL lcSyncToken
LOCAL lcDomain
LOCAL lcBalance
LOCAL lcBillAddrCity
LOCAL lcBillAddrLine1
LOCAL lcBillAddrPostalCode
LOCAL lcBillAddrLat
LOCAL lcBillAddrLong
LOCAL lcBillAddrCountrySubDivisionCode
LOCAL lcBillAddrId
LOCAL lcTxnDate
LOCAL lcTotalAmt
LOCAL lcCustomerRefName
LOCAL lcCustomerRefValue
LOCAL lcShipAddrCity
LOCAL lcShipAddrLine1
LOCAL lcShipAddrPostalCode
LOCAL lcShipAddrLat
LOCAL lcShipAddrLong
LOCAL lcShipAddrCountrySubDivisionCode
LOCAL lcShipAddrId
LOCAL lcDueDate
LOCAL lcPrintStatus
LOCAL lnDeposit
LOCAL lnSparse
LOCAL lcEmailStatus
LOCAL lnApplyTaxAfterDiscount
LOCAL lcId
LOCAL lnTxnTaxDetailTotalTax
LOCAL lcMetaDataCreateTime
LOCAL lcMetaDataLastUpdatedTime
LOCAL j
LOCAL lnCount_j
LOCAL lnLineNum
LOCAL lcAmount
LOCAL lcSalesItemLineDetailTaxCodeRefValue
LOCAL lcSalesItemLineDetailItemRefName
LOCAL lcSalesItemLineDetailItemRefValue
LOCAL lcDetailType
LOCAL lcDefinitionId
LOCAL lcInvType
LOCAL lcName
LOCAL lnQueryResponseStartPosition
LOCAL lnQueryResponseTotalCount
LOCAL lnQueryResponseMaxResults
LOCAL lcTime
LOCAL i
LOCAL lnCount_i
lnSuccess = 0
* This example requires the Chilkat API to have been previously unlocked.
* See Global Unlock Sample for sample code.
* First get our previously obtained OAuth2 access token.
loJsonToken = CreateObject('Chilkat.JsonObject')
lnSuccess = loJsonToken.LoadFile("qa_data/tokens/qb-access-token.json")
loRest = CreateObject('Chilkat.Rest')
lnBTls = 1
lnPort = 443
lnBAutoReconnect = 1
lnSuccess = loRest.Connect("sandbox-quickbooks.api.intuit.com",lnPort,lnBTls,lnBAutoReconnect)
loSbAuth = CreateObject('Chilkat.StringBuilder')
loSbAuth.Append("Bearer ")
loSbAuth.Append(loJsonToken.StringOf("access_token"))
loRest.Authorization = loSbAuth.GetAsString()
* --------------------------------------------------------------------------
* Note: The above REST connection and setup of the AWS credentials
* can be done once. After connecting, any number of REST calls can be made.
* The "auto reconnect" property passed to rest.Connect indicates that if
* the connection is lost, a REST method call will automatically reconnect
* if needed.
* --------------------------------------------------------------------------
* This is a GET request, so there should be no Content-Type
* This line of code is just to make sure..
loRest.RemoveHeader("Content-Type")
loRest.AddHeader("Accept","application/json")
loRest.AllowHeaderFolding = 0
* Add a SELECT statement
loRest.AddQueryParam("query","select * from Invoice where id = '239'")
loSbResponseBody = CreateObject('Chilkat.StringBuilder')
lnSuccess = loRest.FullRequestNoBodySb("GET","/v3/company/<realmID>/invoice",loSbResponseBody)
IF (lnSuccess <> 1) THEN
? loRest.LastErrorText
RELEASE loJsonToken
RELEASE loRest
RELEASE loSbAuth
RELEASE loSbResponseBody
CANCEL
ENDIF
lnRespStatusCode = loRest.ResponseStatusCode
IF (lnRespStatusCode >= 400) THEN
? "Response Status Code = " + STR(lnRespStatusCode)
? "Response Header:"
? loRest.ResponseHeader
? "Response Body:"
? loSbResponseBody.GetAsString()
RELEASE loJsonToken
RELEASE loRest
RELEASE loSbAuth
RELEASE loSbResponseBody
CANCEL
ENDIF
* Success is indicated by a 200 response status.
? "response status code = " + STR(lnRespStatusCode)
loJson = CreateObject('Chilkat.JsonObject')
loJson.LoadSb(loSbResponseBody)
loJson.EmitCompact = 0
? loJson.Emit()
* Sample output:
* Use the this online tool to generate parsing code from sample JSON:
* Generate Parsing Code from JSON
* {
* "QueryResponse": {
* "startPosition": 1,
* "totalCount": 1,
* "maxResults": 1,
* "Invoice": [
* {
* "DocNumber": "1070",
* "SyncToken": "0",
* "domain": "QBO",
* "Balance": 150.0,
* "BillAddr": {
* "City": "Bayshore",
* "Line1": "4581 Finch St.",
* "PostalCode": "94326",
* "Lat": "INVALID",
* "Long": "INVALID",
* "CountrySubDivisionCode": "CA",
* "Id": "2"
* },
* "TxnDate": "2015-07-24",
* "TotalAmt": 150.0,
* "CustomerRef": {
* "name": "Amy's Bird Sanctuary",
* "value": "1"
* },
* "ShipAddr": {
* "City": "Bayshore",
* "Line1": "4581 Finch St.",
* "PostalCode": "94326",
* "Lat": "INVALID",
* "Long": "INVALID",
* "CountrySubDivisionCode": "CA",
* "Id": "109"
* },
* "LinkedTxn": [],
* "DueDate": "2015-08-23",
* "PrintStatus": "NeedToPrint",
* "Deposit": 0,
* "sparse": false,
* "EmailStatus": "NotSet",
* "Line": [
* {
* "LineNum": 1,
* "Amount": 150.0,
* "SalesItemLineDetail": {
* "TaxCodeRef": {
* "value": "NON"
* },
* "ItemRef": {
* "name": "Services",
* "value": "1"
* }
* },
* "Id": "1",
* "DetailType": "SalesItemLineDetail"
* },
* {
* "DetailType": "SubTotalLineDetail",
* "Amount": 150.0,
* "SubTotalLineDetail": {}
* }
* ],
* "ApplyTaxAfterDiscount": false,
* "CustomField": [
* {
* "DefinitionId": "1",
* "Type": "StringType",
* "Name": "Crew #"
* }
* ],
* "Id": "239",
* "TxnTaxDetail": {
* "TotalTax": 0
* },
* "MetaData": {
* "CreateTime": "2015-07-24T10:35:08-07:00",
* "LastUpdatedTime": "2015-07-24T10:35:08-07:00"
* }
* }
* ]
* },
* "time": "2015-07-24T10:38:50.01-07:00"
* }
lnQueryResponseStartPosition = loJson.IntOf("QueryResponse.startPosition")
lnQueryResponseTotalCount = loJson.IntOf("QueryResponse.totalCount")
lnQueryResponseMaxResults = loJson.IntOf("QueryResponse.maxResults")
lcTime = loJson.StringOf("time")
i = 0
lnCount_i = loJson.SizeOfArray("QueryResponse.Invoice")
DO WHILE i < lnCount_i
loJson.I = i
lcDocNumber = loJson.StringOf("QueryResponse.Invoice[i].DocNumber")
lcSyncToken = loJson.StringOf("QueryResponse.Invoice[i].SyncToken")
lcDomain = loJson.StringOf("QueryResponse.Invoice[i].domain")
lcBalance = loJson.StringOf("QueryResponse.Invoice[i].Balance")
lcBillAddrCity = loJson.StringOf("QueryResponse.Invoice[i].BillAddr.City")
lcBillAddrLine1 = loJson.StringOf("QueryResponse.Invoice[i].BillAddr.Line1")
lcBillAddrPostalCode = loJson.StringOf("QueryResponse.Invoice[i].BillAddr.PostalCode")
lcBillAddrLat = loJson.StringOf("QueryResponse.Invoice[i].BillAddr.Lat")
lcBillAddrLong = loJson.StringOf("QueryResponse.Invoice[i].BillAddr.Long")
lcBillAddrCountrySubDivisionCode = loJson.StringOf("QueryResponse.Invoice[i].BillAddr.CountrySubDivisionCode")
lcBillAddrId = loJson.StringOf("QueryResponse.Invoice[i].BillAddr.Id")
lcTxnDate = loJson.StringOf("QueryResponse.Invoice[i].TxnDate")
lcTotalAmt = loJson.StringOf("QueryResponse.Invoice[i].TotalAmt")
lcCustomerRefName = loJson.StringOf("QueryResponse.Invoice[i].CustomerRef.name")
lcCustomerRefValue = loJson.StringOf("QueryResponse.Invoice[i].CustomerRef.value")
lcShipAddrCity = loJson.StringOf("QueryResponse.Invoice[i].ShipAddr.City")
lcShipAddrLine1 = loJson.StringOf("QueryResponse.Invoice[i].ShipAddr.Line1")
lcShipAddrPostalCode = loJson.StringOf("QueryResponse.Invoice[i].ShipAddr.PostalCode")
lcShipAddrLat = loJson.StringOf("QueryResponse.Invoice[i].ShipAddr.Lat")
lcShipAddrLong = loJson.StringOf("QueryResponse.Invoice[i].ShipAddr.Long")
lcShipAddrCountrySubDivisionCode = loJson.StringOf("QueryResponse.Invoice[i].ShipAddr.CountrySubDivisionCode")
lcShipAddrId = loJson.StringOf("QueryResponse.Invoice[i].ShipAddr.Id")
lcDueDate = loJson.StringOf("QueryResponse.Invoice[i].DueDate")
lcPrintStatus = loJson.StringOf("QueryResponse.Invoice[i].PrintStatus")
lnDeposit = loJson.IntOf("QueryResponse.Invoice[i].Deposit")
lnSparse = loJson.BoolOf("QueryResponse.Invoice[i].sparse")
lcEmailStatus = loJson.StringOf("QueryResponse.Invoice[i].EmailStatus")
lnApplyTaxAfterDiscount = loJson.BoolOf("QueryResponse.Invoice[i].ApplyTaxAfterDiscount")
lcId = loJson.StringOf("QueryResponse.Invoice[i].Id")
lnTxnTaxDetailTotalTax = loJson.IntOf("QueryResponse.Invoice[i].TxnTaxDetail.TotalTax")
lcMetaDataCreateTime = loJson.StringOf("QueryResponse.Invoice[i].MetaData.CreateTime")
lcMetaDataLastUpdatedTime = loJson.StringOf("QueryResponse.Invoice[i].MetaData.LastUpdatedTime")
j = 0
lnCount_j = loJson.SizeOfArray("QueryResponse.Invoice[i].LinkedTxn")
DO WHILE j < lnCount_j
loJson.J = j
j = j + 1
ENDDO
j = 0
lnCount_j = loJson.SizeOfArray("QueryResponse.Invoice[i].Line")
DO WHILE j < lnCount_j
loJson.J = j
lnLineNum = loJson.IntOf("QueryResponse.Invoice[i].Line[j].LineNum")
lcAmount = loJson.StringOf("QueryResponse.Invoice[i].Line[j].Amount")
lcSalesItemLineDetailTaxCodeRefValue = loJson.StringOf("QueryResponse.Invoice[i].Line[j].SalesItemLineDetail.TaxCodeRef.value")
lcSalesItemLineDetailItemRefName = loJson.StringOf("QueryResponse.Invoice[i].Line[j].SalesItemLineDetail.ItemRef.name")
lcSalesItemLineDetailItemRefValue = loJson.StringOf("QueryResponse.Invoice[i].Line[j].SalesItemLineDetail.ItemRef.value")
lcId = loJson.StringOf("QueryResponse.Invoice[i].Line[j].Id")
lcDetailType = loJson.StringOf("QueryResponse.Invoice[i].Line[j].DetailType")
j = j + 1
ENDDO
j = 0
lnCount_j = loJson.SizeOfArray("QueryResponse.Invoice[i].CustomField")
DO WHILE j < lnCount_j
loJson.J = j
lcDefinitionId = loJson.StringOf("QueryResponse.Invoice[i].CustomField[j].DefinitionId")
lcInvType = loJson.StringOf("QueryResponse.Invoice[i].CustomField[j].Type")
lcName = loJson.StringOf("QueryResponse.Invoice[i].CustomField[j].Name")
j = j + 1
ENDDO
i = i + 1
ENDDO
RELEASE loJsonToken
RELEASE loRest
RELEASE loSbAuth
RELEASE loSbResponseBody
RELEASE loJson