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.


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.


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.


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)
  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
    MsgBox ("End date is an error")
  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
  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
      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) = ""
            linedata(4, i) = InvoiceLineRetList.GetAt(i).InvoiceLineRet.Desc.GetValue
          End If
          If InvoiceLineRetList.GetAt(i).InvoiceLineRet.Quantity Is Nothing Then
            linedata(5, i) = 0
            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
                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
        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.

        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
        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. "
          Set responseMsgSet = SessionManager.DoRequests(requestMsgSet)
        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
      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. "
        If batch <> "" Then
          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)
            End If
        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
MsgBox "Process Complete"
Close #1
Kill mpath + "\upload.txt"
Exit Sub

MsgBox "Invalid path on uploadpath.txt"

Exit Sub


Wow, all Done

Hit Counter