What A Job!!
The Job:
Develop a software program to scan an invoice number from the invoice document, then scan each item physically to verify that the correct item was pulled, then scan the product batch or lot number (a second barcode on the label), give an indication of completion when completed. And finally, upon completion, load the batch numbers back onto the sales invoice's custom field called "Batch" in QuickBooks Enterprise edition
My Background for this:
20 years of programming in Basic, FoxPro, HTML, ASP and one previous program connecting data in QuickBooks. No previous experience in an object oriented programming techniques. I have previously worked for two different companies using the older SBT and Accpac VisionPoint software and had created the exact same scanning functionality.
Platform:
I found most of the support out there for programming in Visual Basic and had a copy of Visual Studio 6. I am not familiar with C or C# or Java. My client has Enterprise but not Advanced Inventory. My previous project was on Premier. As it turns out, It does not matter for this project.
Resources I found:
First you must download and install the QuickBooks SDK (I used Version 13) This includes the OSR, On Screen Reference, The SDK Programmers Guide and numerous example files. There was also a video produced that described how to set up visual Basic.
Purpose for writing this:
I spent more than 50 hours over a week attempting to do just one thing that I thought would be fairly easy. In FoxPro, I could have done this one step in literally 5 minutes. No exaggeration. It was simply Find the invoice, find the line, replace batch with "XXX" that is it. This programming in QuickBooks is so cryptic and foreign to me that it took that long to understand it. Perhaps I am not the sharpest tool in the shed. I am writing this to help any of you out there understand this process without the struggle I endured.
Preamble:
I chose to program using the QBFC method, and what I learned from this is that using this method is mostly an objected oriented technique. As I had no experience with this, there was a very large learning curve. I did a lot of cut and paste from examples. I also figured out a way to run my code and produce an XML output. This was critical as many of the examples in the SDK Programmers guide were only the XML output and there was no good instruction as to how to generate that XML from QBFC. and it is the XML that is sent to QuickBooks. This let me test and tweak my code to generate correct XML. The next thing was understanding the overall structure of requesting data and the response that QuickBooks would give you and how to parse the response. I use the terms request and response as that is what the SDK uses.
Limits:
This tutorial is limited to Querying Data, Adding Data, Modifying Data mostly in the Sales invoice. I will tell you that it is possible to do much more including creating a screen that looks like a quickbooks screen and making it function like any other screen. I don't know how to do that. You can also run reports. I don't yet know how to do that either. QB has a pretty robust reporting capability, I haven't had to generate my own (yet). There are many other nuances that I am not going to cover like linked transactions and such. In most cases of adding records, what we are talking about is importing data. The is a much easier way to do this using the import QIF format. To do this, simply create a sample transaction, output it to a QIF file, Open the QIF file with a text editor, Figure out a way to format your external data that way, and run an import. No talking to QB involved. Use Excel, Basic, Foxpro or any program that can generate a text file. Adding Customer, Invoices, Payables all can be done this way
So this is the basic process.
1. Create a connection This is chapter 3 in the ProGuide
2. Make a request, if what you want is a list of sales invoices or a list
of transactions, you are talking about a query so we will make a query request.
Go to the OSR and figure out which file (I use this term because in my world,
invoices are in one file, cash receipts are in another. But in QuickBooks, it
more like a transaction type)
If your purpose is to make a change to something, its a mod request, if it is to
add a transaction, it is an add request
3. In the case of a Query, Qualify the request, Tell it whether or not you want
to filter it, or get every record. Say whether you need the line items, say
whether you need extended data (What is extended data, in the case of invoices,
you can create custom fields for every inventory item, when that item is added
to the invoice, the inventory's custom field can be entered. See chapter
11 (around figure 11-6) to see how to allow entry of this information into your
invoice and to print it onto the invoice itself. This was actually at the
heart of my project.
in the case of a modification, you would do a query first to get the transaction id number (trxid) so that you could later tell QuickBooks what to do to it.
4. Execute the query
5. Look at the response, Parse it out
6. Make a new request, to modify the invoice, now its a mod request, To modify a transaction, you need to know the trxid. Transaction ID number are internal id's, its how you tell QB which invoice you are talking about. As there could be more than one invoice with the same number, invoice number is not unique. This request actually includes all of the instructions to do for instance, if you want to change the memo field on the invoice, you would tell the request to change the memo to a specific value. When you execute the request, all of the instructions are carried out and the response will tell whether it was successful.
Now lets look at some code and in the code, I will try to explain the syntax and how I know what words to use.
This sample code is my working project with some extra for tutorial purposes. It opens a text file and reads it one line at a time into some variables and repeats the process for every record in the file. Remember, i scanned an invoice, then the sku number and then the batch. I want to place the batch number on the line item of the invoice in a custom field called "Batch #". I will also show you where you can change things on the header of the invoice such as the memo field and on the line items such as description, quantity or price. When the dust settled and after 50 hours of working on it, I discovered that I didn't even need to go into the invoice modification to accomplish my task at all. it Figures.
To setup VB6 to work on Quickbooks, first create a project, Standard Exe
This will leave you in a project with a form. All I need for this is a
simple Execute button for this App
Use the Command Button to Draw a button on the form. Right Click on
the form to change the caption.
Now you must add the qbfc13 1.0 Type Library to the references. I
believe this makes VB understand the OSR or in layman's terms, the specific
database information of QuickBooks. When you type command lines, it will
give you the available options. Project, References, check the box on QBFC13 1.0
Type Library
Now Double Click the button and you are in the program that executes when you
click the button. For environment purposes, I found these
items very useful. Debug, Watch and Immediate. I could place a stop
in the program and execute it line by line, watch memory variables values.
Really see what is going on as it ran.
Imbeded in my code is a lot of REM statements. I use this because I cannot remember what I was doing and why. My comments and instructions about the code for this blog will be Italicized. The "Rems" will not.
Lets first Insert these two functions after the main subroutine
Place this function below the first End Sub
Public Function GetLatestMsgSetRequest(SessionManager As QBSessionManager) As
IMsgSetRequest
Dim supportedVersion As String
supportedVersion = QBFCLatestVersion(SessionManager)
If (supportedVersion >= "13.0") Then
Set GetLatestMsgSetRequest = SessionManager.CreateMsgSetRequest("US", 13,
0)
ElseIf (supportedVersion >= "12.0") Then
Set GetLatestMsgSetRequest = SessionManager.CreateMsgSetRequest("US", 12,
0)
ElseIf (supportedVersion >= "11.0") Then
Set GetLatestMsgSetRequest = SessionManager.CreateMsgSetRequest("US", 11,
0)
ElseIf (supportedVersion >= "10.0") Then
Set GetLatestMsgSetRequest = SessionManager.CreateMsgSetRequest("US", 10,
0)
ElseIf (supportedVersion >= "9.0") Then
Set GetLatestMsgSetRequest = SessionManager.CreateMsgSetRequest("US", 9,
0)
ElseIf (supportedVersion >= "8.0") Then
Set GetLatestMsgSetRequest = SessionManager.CreateMsgSetRequest("US", 8,
0)
ElseIf (supportedVersion >= "7.0") Then
Set GetLatestMsgSetRequest = SessionManager.CreateMsgSetRequest("US", 7,
0)
ElseIf (supportedVersion >= "6.0") Then
Set GetLatestMsgSetRequest = SessionManager.CreateMsgSetRequest("US", 6,
0)
ElseIf (supportedVersion >= "5.0") Then
Set GetLatestMsgSetRequest = SessionManager.CreateMsgSetRequest("US", 5,
0)
ElseIf (supportedVersion >= "4.0") Then
Set GetLatestMsgSetRequest = SessionManager.CreateMsgSetRequest("US", 4,
0)
ElseIf (supportedVersion >= "3.0") Then
Set GetLatestMsgSetRequest = SessionManager.CreateMsgSetRequest("US", 3,
0)
ElseIf (supportedVersion >= "2.0") Then
Set GetLatestMsgSetRequest = SessionManager.CreateMsgSetRequest("US", 2,
0)
ElseIf (supportedVersion = "1.1") Then
Set GetLatestMsgSetRequest = SessionManager.CreateMsgSetRequest("US", 1,
1)
Else
MsgBox "You are apparently running QuickBooks 2002 Release 1, we strongly
recommend that you use QuickBooks' online update feature to obtain
the latest fixes and enhancements", vbExclamation
Set GetLatestMsgSetRequest = SessionManager.CreateMsgSetRequest("US", 1,
0)
End If
End Function
Place this function below what you just pasted
Function QBFCLatestVersion(SessionManager As QBSessionManager) As String
Dim strXMLVersions() As String
strXMLVersions = SessionManager.QBXMLVersionsForSession
Dim i As Long
Dim vers As Double
Dim LastVers As Double
LastVers = 0
For i = LBound(strXMLVersions) To UBound(strXMLVersions)
vers = Val(strXMLVersions(i))
If (vers > LastVers) Then
LastVers = vers
QBFCLatestVersion = strXMLVersions(i)
End If
Next i
End Function
This probably isn't necessary if you change the caption on the button in the
properties window
Click on the form option in the first box, this should take you down to the
private Sub Form_load()
Change it to look like this
Private Sub Form_Load()
Me.Caption = ""
Command1.Caption = "Click to begin process ..."
End Sub
At this point, you should be able to run the program but it will do nothing, But it should not error out.
Now we will be working on the program that will execute when you click the
button. After the "Private Sub Command2_Click()" This entire
program is a subroutine, exiting is simply "Exit Sub"
This creates a message box reminder
Dim msg As String
Dim okCancel As VbMsgBoxResult
msg = "This application Loads Batches into invoice line items. " & _
vbCr & vbCr & _
"QuickBooks must be running with a data file open." & vbCr
okCancel = MsgBox(msg, vbOKCancel)
If okCancel = vbCancel Then
Exit Sub
End If
The VB program can reside anywhere on the network. To identify where the upload file is, i have included a text file called uploadpath.txt. This is far easier to edit than this program if the location is different. In this case the uploadpath.txt file has a single line of "q:\invoicescans". This is the drive that holds the upload.txt file
Rem check to see if there is a path file
Rem create path file that ways where the upload.txt file is located
If Dir(App.Path + "\uploadpath.txt") = "" Then
MsgBox "The uploadpath.txt file is not found. Create a text file with
contents like c:\path"
Exit Sub
End If
Dim mpath As String
Open App.Path + "\uploadpath.txt" For Input As #1
Input #1, mpath
Close #1
rem this is a test to see if I can create a file there. is the directory
valid
On Error GoTo badpath
Open mpath + "temp.txt" For Output As #1
Close #1
Kill mpath + "temp.txt"
On Error GoTo 0
Rem this creates a variable that holds the information about the session
Dim SessMgr As New QBSessionManager
Rem this opens a connection to quickbooks and names it
SessMgr.OpenConnection2 "", "Load Batches into Invoices", ctLocalQBD
Rem this begins a session with the quickbooks data file
SessMgr.BeginSession "", omDontCare
When this executes, with QB open, you should get an authorization window in quickbooks with the program name "Load Batches into Invoices". You will need to authorize it.
If Quickbooks is not open, you could open it by specifying the file to open like this on the next line
Rem SessMgr.BeginSession "c:\Documents and Settings\All Users\Documents\Intuit\QuickBooks\Company Files\Companyname.QBW", omDontCare
I always want to know what the last close date is if any because if you
try to modify or enter a transaction before this date, it will error out. So
request #1 is to the preferences/accounting page and getting a close date if any
Rem request #1 to determine last close date
Rem this creates a variable to be a request type variable
Dim msgReq1 As IMsgSetRequest
Set msgReq1 = GetLatestMsgSetRequest(SessMgr)
Rem dimensions query1 to be a query on the preferences file
Dim query1 As IPreferencesQuery
Rem this append the query into the request object
Set query1 = msgReq1.AppendPreferencesQueryRq
Rem dimensions response to be a response type variable
Dim Response1 As IMsgSetResponse
Rem this runs the request on the session and stores the results in the response1
memory variable (Object)
Set Response1 = SessMgr.DoRequests(msgReq1)
You might wonder, how did I know the IPreferencesQuery? Think of this
this way, you can dimension a memory variable to be a string, integer, array,
double (0.00), etc. Here you dimension a variable to hold a certain type
of request, A preference Query. It is designed to hold data in a format
correct for the data you are requesting. Go into the OSR and pull up
PreferencesQuery. This was basically a guess but they are named kind of like the
QuickBooks screens. Under the Tag, it says IpreferencesQuery. There you
go. So now you want to add this query to the message request. I can't seem
to get the hang of figuring out what commands go where but just take a look at
the last line with the append on it. Also look at the symmetry. There is a
message set request and a message set response. To figure out the
response, on the OSR click on the response tab. I use the practice of
numbering my query's and responses. When you work with more than one, it
could get confusing.
This response has a few levels represented by the indentations. The
hardest part is working your way down the levels to get to the data you want.
Remember that I use the word Variable like others use Objects.
Rem At this point the response variable holds a single response because only
one query was loaded
Rem into the request
Rem Now the responses are loaded into a list
Rem First, setup a variable to be a response list
Dim ResponseList1 As IResponseList
Rem This gets the list of responses into the list variable responselist1
Rem About the only thing you can ask of it at this time is the count of how many
responses are in the response
Rem Get the responses response list
Set ResponseList1 = Response1.ResponseList
Rem Second, we need to put the first response in the list into a variable.
The As Iresponse is the first tag on rem the OSR response
Dim curResponse1 As IResponse
Rem Get the first response and put it in the variable. Counting in VB starts at
Zero
Set curResponse1 = ResponseList1.GetAt(0)
Rem At this point we can ask for variables and their values from curResponse1
but only
Rem at the top layer of the response, about the status and such
Rem It is in the detail of the response that we need to get
I set the mclosedate variable this way as the default date. I didn't want a
blank date as there may not be a close date in the system yet.
mclosedate = CDate("01/01/2000")
If (curResponse1.StatusCode = 0) Then
If Not (curResponse1.Detail.AccountingPreferences.ClosingDate Is
Nothing) Then
mclosedate =
curResponse1.Detail.AccountingPreferences.ClosingDate.GetValue
End If
SessMgr.EndSession
SessMgr.CloseConnection
Else
MsgBox ("End date is an error")
SessMgr.EndSession
SessMgr.CloseConnection
Exit Sub
End If
There you go. I have closed the session and the connection but have not cleared any of the variables. This can be important as if you make another request and add it to the first request, then you would need to get the second response, not the first. I will usually try to clean house.
Now we will start an new session which looks just like the last session. I don't know what the last line there does.
Rem start a new session
Dim Sessionmanager As New QBSessionManager
Rem this opens a connection to quickbooks and names it
Sessionmanager.OpenConnection2 "", "Load Batches into Invoices", ctLocalQBD
Sessionmanager.BeginSession "", omDontCare
Dim requestMsgSet As IMsgSetRequest
Set requestMsgSet = GetLatestMsgSetRequest(Sessionmanager)
requestMsgSet.Attributes.OnError = roeContinue
This time we want to scan through a text file of of invoice number, sku # and
batch # and counts,
Find the invoice in Quickbooks, then the line item, then modify the batch field
which is a custom field
I am going to define some memory variables to be used later.
Dim invoicetofind As String
Dim sku As String
Dim batch As String
Dim quant As Integer
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim FullName As String
Dim linedata() As String
Dim extdata() As String
Dim TransID As String
Dim EditSeq As String
Dim test2 As String
Rem this item is to flag if there is a need to modify the invoice
Rem this was created as a training
Dim modifyinvoice As String
If Dir(mpath + \"upload.txt") <> "" Then
Else
MsgBox "Upload File does not exist"
Exit Sub
End If
Open mpath+"\upload.txt" For Input As #1
Now here begins the loop. On the OSR Bring up InvoiceQuery, request
Do Until EOF(1)
Input #1, invoicetofind, sku, batch, quant
Rem If there is a blank line in the file, skip
If invoicetofind <> "" Then
Rem Setup an invoice query dimension a variable as an invoice query
Dim InvoiceQuery As IInvoiceQuery
Rem Append the query into the request appendinvoicequeryrq
Set InvoiceQuery = requestMsgSet.AppendInvoiceQueryRq
When you create a query, you can set all kinds of filters and rules as to what
will be returned.
Two rules are used here, first is to include the line items of the invoice, the
other is to include the custom fields which is called extended data. I
believe it is called that because this data is not actually in the invoice query
at all. It took me a few days to figure that one out. Now it seems
obvious.
Rem This next line is nowhere in the OSR but it is necessary for the query to
return custom field information
InvoiceQuery.OwnerIDList.Add ("0")
InvoiceQuery.IncludeLineItems.SetValue True
Rem Look at the osr under invoicequery. To set a filter to return only invoices
with a specific
Rem refnumber(invoice number) your filter statement must go through several
layers In this case, 5
InvoiceQuery.ORInvoiceQuery.InvoiceFilter.ORRefNumberFilter.RefNumberFilter.MatchCriterion.SetValue
(CONTAINS)
InvoiceQuery.ORInvoiceQuery.InvoiceFilter.ORRefNumberFilter.RefNumberFilter.RefNumber.SetValue
(invoicetofind)
Finally, now that the request is set to find the single invoice equal to "invoicetofind",
run the request
by setting up a response. My message set wording is different as well as
the response wording. I am not being consistent. After a while,
there are so many variables and commands I got lost.
Rem Setup the response to the query
Dim responseMsgSet As IMsgSetResponse
Rem this runs the request and places the response into responsemsgset
Set responseMsgSet = Sessionmanager.DoRequests(requestMsgSet)
Now lets parse the response. This one is more complex than the
preferences one above because it includes line items and such. Move the
OSR to the response tab. All responses here are referenced as response2.
The response from the preference above is still accessible
Rem Create a variable to hold the response using the iresponse type
Dim Response2 As IResponse
Rem This pulls the first response out of the responseMsgSet, since there was
only 1 request, there is
Rem only one response
Set Response2 = responseMsgSet.ResponseList.GetAt(0)
Rem At this point, response2 has statuses about the response,
Rem the detail of the response is then loaded into a list
If Response2.Detail Is Nothing Then
MsgBox "No Detail Available for invoice " & invoicetofind
Rem I don't want to exit as there are additional scans to be processed
Else
Rem The response detail must now be loaded into a list form. Basically a
list of invoices
Dim InvoiceRetList As IInvoiceRetList
Set InvoiceRetList = Response2.Detail
Rem Now get the first invoice in the responselist into an object with the
type invoiceret (not on the osr)
Dim InvoiceRet As IInvoiceRet
Set InvoiceRet = InvoiceRetList.GetAt(0)
At this point you can see the header fields of the invoice, internal
fields and visible fields
Rem Save the Txnid and EditSeq
TransID = InvoiceRet.TxnID.GetValue
EditSeq = InvoiceRet.EditSequence.GetValue
Anything you want to do to this invoice later on should be called up by the
Txnid. This is an internal unique number for this invoice. Since QB
allows multiple invoices with the same number, our response might have pulled up
more than one invoice. For this app, I assume that the company only has
one and this scan can't know which of multiples so I would save the batch to the
first one found.
Next, I want to drill down to the line items. We will set a variable to hold the
line items of this invoice, then scan through it to get the line's txnlineid
field if it matches the scan. If so, that is the line I want to add the
batch number to.
If you scroll down the OSR to get to the ORInvoicelineRetList, All of the lines
are below this level
Dim InvoiceLineRetList As IORInvoiceLineRetList
Rem Read this like, Put the list of line items of the invoice into this
variable
Set InvoiceLineRetList = InvoiceRet.ORInvoiceLineRetList
FullName = ""
Dim needlineadd As Integer
needlineadd = -1
Dim Linethatchanged() As Integer
Rem there is a count of the number of line items
in the invoiceret object, the item holding the entire
Rem first invoice and its lines, record counters
start at zero
For i = 0 To InvoiceRet.ORInvoiceLineRetList.Count - 1
Rem load all pertanant data into variables
There was a problem here. If the line item of the invoice had no sku,
then you have to test this. It will error out if you try to set a variable
to this value. This is true for the quantity, description field as well.
If the value is Zero, it will not error, but if it is blank it will. the
reason is that a blank is a null and you cannot put null into a string or
integer
If Not (InvoiceLineRetList.GetAt(i).InvoiceLineRet.ItemRef Is
Nothing) Then
FullName =
InvoiceLineRetList.GetAt(i).InvoiceLineRet.ItemRef.FullName.GetValue
I decided to load the entire line item into an array. This may be
necessary.
Rem array definition
Rem 1 txnid, 2 transaction line id,3 itemref,4
description,5 Quantity, 6 Rate, 7 Batch,
Rem first element of the array is the transaction
id for the entire invoice
Rem probably don't need to save it
ReDim Preserve linedata(7,0,I) as string
linedata(1, i) =
InvoiceRetList.GetAt(0).TxnID.GetValue
linedata(2, i) = InvoiceLineRetList.GetAt(i).InvoiceLineRet.TxnLineID.GetValue
linedata(3, i) = InvoiceLineRetList.GetAt(i).InvoiceLineRet.ItemRef.FullName.GetValue
If InvoiceLineRetList.GetAt(i).InvoiceLineRet.Desc Is Nothing Then
linedata(4, i) = ""
Else
linedata(4, i) = InvoiceLineRetList.GetAt(i).InvoiceLineRet.Desc.GetValue
End If
If InvoiceLineRetList.GetAt(i).InvoiceLineRet.Quantity Is Nothing Then
linedata(5, i) = 0
Else
linedata(5, i) = InvoiceLineRetList.GetAt(i).InvoiceLineRet.Quantity.GetValue
End If
linedata(6, i) = InvoiceLineRetList.GetAt(i).InvoiceLineRet.Amount.GetValue
Rem check to see if line
has an item on it or is blank.
If (Not
InvoiceLineRetList.GetAt(i).InvoiceLineRet.ItemRef Is Nothing) Then
Rem now check
to see if the item already has a batch number. We will not overwrite
unless it has a "#"
Dim
dataextretlist As IDataExtRetList
Set
dataextretlist = InvoiceLineRetList.GetAt(i).InvoiceLineRet.dataextretlist
If Not (dataextretlist
Is Nothing) Then
Rem if this line is the correct line and there is a #, then store the batch
number. if not we don't care
If linedata(3, i) = sku And InvoiceLineRetList.GetAt(i).InvoiceLineRet.dataextretlist.GetAt(0).DataExtValue.GetValue
= "#" Then
Rem Then you can set the batch number for this line
linedata(7, i) = batch
Else
linedata(7, i) = InvoiceLineRetList.GetAt(i).InvoiceLineRet.dataextretlist.GetAt(0).DataExtValue.GetValue
End If
Rem now the interesting thing here is if the quantity scanned is less than the
quantity on the invoice
Rem then do we reset the quantity but then we need to add a second line because
the next scan is likely to be
Rem the next batch for the balance
This is really important. When the invoice
is created, they don't know if the item can be shipped of a single batch.
If not, then they will scan a second batch number. The total must be the
total of the invoice so we will need to change the quantity on the first
occurrence of the sku, save the transaction line id of that one and add the
batch number. When we add a second line, we do not know the batch number
yet. It would probably be the next scan record. When it gets added,
it will inherit the batch from the item code, "#". Since the first SKU
will now have a batch number, it will be skipped over. This assumes that
the scanning program will sorts and consolidates all of the scans for the same
SKU by every unique occurrence of sku+batch.
Rem needlineadd is -1 if it has not been used yet
If
sku = linedata(i, 3) And needlineadd = -1 Then
ReDim Preserve
linethatchanged(0 To i)
if the quantity is less than the invoice, store the line information in a new
array. When adding a line, if the line number is -1, they know it is an
add new line
If quant < linedata(5, i) Then
Dim addlinedata(7) As String
addlinedata(1) = linedata(1, i)
addlinedata(2) = "-1"
addlinedata(3) = linedata(3, i)
addlinedata(4) = linedata(4, i)
addlinedata(5) = linedata(5, i) - quant
addlinedata(6) = linedata(6, i)
Rem I stored the batch but really we don't want it. The new line gets no
batch
addlinedata(7) = batch
needlineadd = i
End If
What happens the other way, what if more than one line has the exact same sku.
Then I might need to modify the batch number on more than one line
If quant > linedata(5, i) Then
linethatchanged(i) = linedata(2, i)
End If
End If
End If
End If
End If
Next
mtxnlineid = linedata(2, i)
Linedata(7, i) = batch
quanttoapply = quanttoapply -
linedata(5, i)
End If
Rem Make sure array size is equal to i
ReDim Preserve linethatchanged(0 To i) As String
Rem must read it all in first
If needlineadd > -1 Then
We now must to an invoicemod, modify line and then add line to invoice.
Unfortunately, there is no way to change the order of the lines on the invoice.
The line will be at the bottom. It might be possible to delete all of the
lines, then re-add them but I am not going to do this here.
requestMsgSet.ClearRequests
Dim InvoiceMod As IInvoiceMod
Set InvoiceMod =
requestMsgSet.AppendInvoiceModRq
InvoiceMod.TxnID.SetValue (TransID)
InvoiceMod.EditSequence.SetValue (EditSeq)
Dim InvoiceLineModList As
IORInvoiceLineModList
Set InvoiceLineModList =
InvoiceMod.ORInvoiceLineModList
Dim invline As IInvoiceLineMod
For j = 0 To
InvoiceRet.ORInvoiceLineRetList.Count - 1
Set invline =
InvoiceMod.ORInvoiceLineModList.Append.InvoiceLineMod
invline.TxnLineID.SetValue linedata(2, j)
Rem if on the
correct line, change the quantity to the scan qty only
If j = needlineadd
Then
invline.Quantity.SetValue quant
End If
Next
Rem now add the new line
Rem 1 txnid, 2 transaction line id,3
itemref,4 description,5 quantity,6 Rate,7 batch
Rem first element of the array is the
transaction id for the entire invoice
Set invlineadd =
InvoiceMod.ORInvoiceLineModList.Append.InvoiceLineMod
invlineadd.TxnLineID.SetValue ("-1")
invlineadd.ItemRef.FullName.SetValue
(addlinedata(3))
invlineadd.Desc.SetValue
(addlinedata(4))
invlineadd.Quantity.SetValue
(addlinedata(5))
invlineadd.ORRatePriceLevel.Rate.SetValue (addlinedata(6))
What is interesting here is that we have an add line and a modify line in a
single request.
Set responseMsgSet =
SessionManager.DoRequests(requestMsgSet)
If InvoiceRet.TxnDate.GetValue <=
mclosedate Then
MsgBox "Invoice " &
invoicetofind & " is dated before the close date. Cannot make changes. "
Else
Set responseMsgSet =
SessionManager.DoRequests(requestMsgSet)
requestMsgSet.ClearRequests
End If
Now we will do the actual change to the batch number. First we test
to see if the invoices data is prior to the close. Remember that the
response is still available to us. i could exit the sub but i am still
looping scans and the next scan could be a different invoice # and exiting the
sub would kill the process.
Rem Clear requests from message Set because we are going to do another
request. This does not
requestMsgSet.ClearRequests
Rem This should not be possible as the file
should not have a line on it that doesn't exist
If InvoiceRet.TxnDate.GetValue <= mclosedate Then
MsgBox "Invoice " & invoicetofind & " is
dated before the close date. Cannot make changes. "
Else
If batch <> "" Then
Rem WE NEED TO DO THIS
FOR EVERY i THAT HAS A TRANSACTIONLINEID
Rem First do a query on
the file to find the correct records
Dim DataExtMod As
IDataExtMod
Rem there could be more
than one line that changed, check every line
For k = 0 To i
If
linethatchanged(k) <> "" Then
Several kinds of external data are stored here, Custom fields for the
inventory master, and customer master files,
Custom fields for transaction data like what we are trying to do. Take a
look in the OSR, The third
level is for listData or transaction data. All we are trying to do is
enter data and tell it that it belongs to a transaction with an invoice number
of txnid, a line of that invoice of txnlineid, a name of "Batch #" and a value of
what the scan recorded. The only really strange thing is setting the
data type. This variable tdetInvoice is some kind of system variable that
QB understands. On the programmers guide, they say to use a, STR255 for
this but it did not work. I figured this out as my final challenge.
If you need to change a Purchase order instead, there is another type for that.
Set DataExtMod = requestMsgSet.AppendDataExtModRq
DataExtMod.OwnerID.SetValue ("0")
DataExtMod.ORListTxn.TxnDataExt.TxnDataExtType.SetValue (tdetInvoice)
DataExtMod.DataExtName.SetValue ("Batch #")
DataExtMod.DataExtValue.SetValue (linedata(7, k))
DataExtMod.ORListTxn.TxnDataExt.TxnID.SetValue (TransID)
DataExtMod.ORListTxn.TxnDataExt.TxnLineID.SetValue (linethatchanged(k))
Set responseMsgSet = SessionManager.DoRequests(requestMsgSet)
requestMsgSet.ClearRequests
End If
Next
ReDim linethatchanged(0)
Rem MsgBox responseMsgSet.ToXMLString
End If
End If
End If
End If
If you want to see the response or the request, you can see it by doing
this message box above. But there are limits to the size of the box so you
cannot always see it all. Much of the programmers guide examples are the
xml result and to test your qbfc, to see if it created the correct xml, you
could do this instead. At least you could see the file with a text editor,
Do this before you clear the request
rem test2 = requestMsgSet.ToXMLString
rem Open "c:\temp\request.txt" For Output As
#2
rem Write #2, test2
rem Close #2
Loop
MsgBox "Process Complete"
SessMgr.EndSession
SessMgr.CloseConnection
Close #1
Kill mpath + "\upload.txt"
Exit Sub
badpath:
MsgBox "Invalid path on uploadpath.txt"
Exit Sub
Wow, all Done