Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

Inserting records into QuickBooks invoice line table

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    Inserting records into QuickBooks invoice line table

    I have successfully used QODBC and Alpha to put customer records into the customer table in QuickBooks. I have also been successful at creating an invoice header record. So far, so good.
    Does anyone know what is the minimum required to create invoice line items. I tried putting the trxid number, but that did not seem to do anything, even though Alpha said a record was created. I opened the invoice in QB and could not see the new line.

    I would appreciate any thoughts.
    Thanks in advance.
    Jay Talbott
    Jay Talbott
    Lexington, KY


    #2
    Re: Inserting records into QuickBooks invoice line table

    Jay

    I think this is what you want. I have not done this yet but am going on memory from a presentation by Al Buchholz on QODBC at this past year's Alpha Conference.

    http://doc.qodbc.com/qodbc/20/tables...ml?tables_id=2

    Click on the table you want the info on. There is a column called Req Ins which I believe denotes whether the field is required for an record insert. I could be wrong about that but I know the info you need is located here.

    Hopefully Al will see this and chime in as he knows this stuff inside and out.

    Regards,

    Jeff

    Additional thought: From Al's presentation, it is important to keep in mind that when using Alpha and QODBC to enter records that it behaves JUST like you were typing the record into QB itself. So, I don't think you would enter TrxID as that would not be something you would actually enter if you were keying it into QB. You kinda have to get your head in that frame of mind as I recall (which actually makes it easier) instead of thinking you have to fill in every behind the scenes field as you would with a typical import routine for something like this.
    Last edited by jkletrovets; 11-18-2007, 01:33 AM.

    Comment


      #3
      Re: Inserting records into QuickBooks invoice line table

      I remembered this from Al's site. It is his presentation from the conference.

      http://www.bookwood.com/atec2007bwsl.pdf

      Comment


        #4
        Re: Inserting records into QuickBooks invoice line table

        Jay

        Jeff is a good student.

        He's put you on the right track. Have you resolved your issues?
        Al Buchholz
        Bookwood Systems, LTD
        Weekly QReportBuilder Webinars Thursday 1 pm CST

        Occam's Razor - KISS
        Normalize till it hurts - De-normalize till it works.
        Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
        When we triage a problem it is much easier to read sample systems than to read a mind.
        "Make it as simple as possible, but not simpler."
        Albert Einstein

        http://www.iadn.com/images/media/iadn_member.png

        Comment


          #5
          Re: Inserting records into QuickBooks invoice line table

          Jeff and Al,
          Yes, I have resolved my issue. I am able to insert invoice lines into invoices. It appears that the only necessary field is TxnId and now that I can do it by hardcoding the linking field, I need to be able to achieve the following:
          Check if the customer exists. If not, add that customer.
          Create invoice header
          Create line items

          That's it. I will ask for help from this great forum if I need it, but I think I am on my way.
          Thanks again for all your help.
          Jay
          Last edited by Jay Talbott; 10-15-2013, 03:57 PM.
          Jay Talbott
          Lexington, KY

          Comment


            #6
            Re: Inserting records into QuickBooks invoice line table

            Jay

            While you can add line items after adding a header, the preferred way is to add the lineitems with a FQSaveToCache = 1 for the line items and then add the invoice header. It seems backwards at first, but is more efficient.

            The option that you are using good to add more invoice lines to an existing invoice. Which will work and is allowed by QB desktop. It may cause some confusion to change an invoice after it's printed, so it's up to you to keep that straight...
            Al Buchholz
            Bookwood Systems, LTD
            Weekly QReportBuilder Webinars Thursday 1 pm CST

            Occam's Razor - KISS
            Normalize till it hurts - De-normalize till it works.
            Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
            When we triage a problem it is much easier to read sample systems than to read a mind.
            "Make it as simple as possible, but not simpler."
            Albert Einstein

            http://www.iadn.com/images/media/iadn_member.png

            Comment


              #7
              Re: Inserting records into QuickBooks invoice line table

              Hi Al

              Do you know of any visual info(i.e. screenshots) that is available for running through the insertion of records into QB from Alpha? I could not find anything on the QODBC site.

              It is straight forward to connect to QB and create a passive linked table or import the data into Alpha but going the other way is slightly more complicated. It does not appear hard from witnessing your presentation, but just getting the proper steps down appears to be the issue for me.

              I am going to have to do what Jay is doing in the near future and was just looking for some hints / tips / instructions. For instance, I was unaware of using the FQSaveToCache = 1 tip.

              Thanks...

              Jeff

              Comment


                #8
                Re: Inserting records into QuickBooks invoice line table

                Jeff,
                Here is the script produced by converting ActionScripting to XBasic. It inserts lines into an existing invoice, by asking the user to place information into a dialog box. It is my partial progress. Next step is to use variables to grab info from Alpha and stuff into QB.
                I could sure use some more info on FQSaveToCache = 1
                I have no idea what this even means??????
                Jay




                'Date Created: 18-Nov-2007 12:06:50 PM
                'Last Updated: 18-Nov-2007 01:21:29 PM
                'Created By : User
                'Updated By : User
                '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_link")
                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


                Dim UnMappedColumns as c
                UnMappedColumns = <<%txt%
                TxnID|C
                InvoiceLineItemRefFullName|C
                InvoiceLineDesc|M
                InvoiceLineQuantity|N
                InvoiceLineRate|N
                %txt%

                'Turn the list of UnMappedColumns into a argument definition string
                dim pargs_c as c
                pargs_c = a5_SimpleArgumentDefinition(UnMappedColumns)
                dim pargs as p

                'Turn the argument definition string into an object
                property_from_string(pargs,pargs_c)
                delete pData
                dim pData as p

                'Prompt for values for each of the UnMapped columns
                pData = a5_promptforparameters("Specify column values",pargs.Arguments,pargs.OKButtonLabel,pargs.CancelButtonLabel)

                if pData.Last_button_pressed = "Cancel" then
                end
                end if

                args.set("TxnID",pData.TxnID)
                args.set("InvoiceLineItemRefFullName",pData.InvoiceLineItemRefFullName)
                args.set("InvoiceLineDesc",pData.InvoiceLineDesc)
                args.set("InvoiceLineQuantity",pData.InvoiceLineQuantity)
                args.set("InvoiceLineRate",pData.InvoiceLineRate)

                dim sqlInsertStatement as c
                sqlInsertStatement = <<%txt%
                INSERT INTO InvoiceLine (TxnID, InvoiceLineItemRefFullName, InvoiceLineDesc, InvoiceLineQuantity, InvoiceLineRate) VALUES (:TxnID, :InvoiceLineItemRefFullName, :InvoiceLineDesc, :InvoiceLineQuantity, :InvoiceLineRate)
                %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()
                Jay Talbott
                Lexington, KY

                Comment


                  #9
                  Re: Inserting records into QuickBooks invoice line table

                  Here is an explanation of inserting multiple invoice line items followed by a single add of the invoice header.

                  add invoice lines
                  Al Buchholz
                  Bookwood Systems, LTD
                  Weekly QReportBuilder Webinars Thursday 1 pm CST

                  Occam's Razor - KISS
                  Normalize till it hurts - De-normalize till it works.
                  Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
                  When we triage a problem it is much easier to read sample systems than to read a mind.
                  "Make it as simple as possible, but not simpler."
                  Albert Einstein

                  http://www.iadn.com/images/media/iadn_member.png

                  Comment


                    #10
                    Re: Inserting records into QuickBooks invoice line table

                    Perfect! Thanks Al. That's what I needed.

                    Regards,

                    Jeff

                    Comment


                      #11
                      Re: Inserting records into QuickBooks invoice line table

                      Jay

                      Thanks for the sample script. I have printed this off and will study it more when I get to my QB needs. Best of luck with your project. I'd be interested in hearing how you make out in the end.

                      Regards,

                      Jeff

                      Comment


                        #12
                        Re: Inserting records into QuickBooks invoice line table

                        Al,
                        I am having a problem with the sample on QODBC's web site when I try mimic their method of inserting the Header (Invoice table) using Alpha's Action Scripting. Here is the code that Alpha converted to X-Basic from Action Scripting.

                        '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_link")
                        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

                        args.add("CustomerRefListID","47001-1071525403")
                        args.add("ARAccountRefListID","40000-933270541")
                        args.add("TxnDate",ctodt("10/01/2007 12:00:00 00 am"))
                        args.add("RefNumber","1")
                        args.add("BillAddressAddr1","Brad Lamb")
                        args.add("BillAddressAddr2","1921 Appleseed Lane")
                        args.add("BillAddressCity","Bayshore")
                        args.add("BillAddressState","CA")
                        args.add("BillAddressPostalCode","94326")
                        args.add("BillAddressCountry","USA")
                        args.add("IsPending",0)
                        args.add("TermsRefListID","10000-933272658")
                        args.add("DueDate",ctodt("10/31/2007 12:00:00 00 am"))
                        args.add("ShipDate",ctodt("10/01/2007 12:00:00 00 am"))
                        args.add("ItemSalesTaxRefListID","2E0000-933272656")
                        args.add("Memo","Memo Test")
                        args.add("IsToBePrinted",1)
                        args.add("CustomerSalesTaxCodeRefListID","10000-999022286")

                        dim sqlInsertStatement as c
                        sqlInsertStatement = <<%txt%
                        INSERT INTO Invoice (CustomerRefListID, ARAccountRefListID, TxnDate, RefNumber, BillAddressAddr1, BillAddressAddr2, BillAddressCity, BillAddressState, BillAddressPostalCode, BillAddressCountry, IsPending, TermsRefListID, DueDate, ShipDate, ItemSalesTaxRefListID, Memo, IsToBePrinted, CustomerSalesTaxCodeRefListID) VALUES (:CustomerRefListID, :ARAccountRefListID, :TxnDate, :RefNumber, :BillAddressAddr1, :BillAddressAddr2, :BillAddressCity, :BillAddressState, :BillAddressPostalCode, :BillAddressCountry, :IsPending, :TermsRefListID, :DueDate, :ShipDate, :ItemSalesTaxRefListID, :Memo, :IsToBePrinted, :CustomerSalesTaxCodeRefListID)

                        %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()


                        Here is the message which results:

                        Record was not inserted. Error reported was:
                        Database API specific error
                        Your database has returned the following error code and description to Alpha Five. Consult your database documentation for further information.
                        10003-'[QODBC] Not supported
                        SQL State is: 42S00'


                        Any thoughts?
                        Thanks.
                        Jay
                        Jay Talbott
                        Lexington, KY

                        Comment


                          #13
                          Re: Inserting records into QuickBooks invoice line table

                          1. Do you have a read/write version of QODBC or a readonly?

                          2. I tend to put the value of something in it's field rather than the listid for that value.

                          3. Try less fields. I don't see one that should cause a problem (other than ids)... but it's a good debugging technique to divide and conquer.

                          Remember, you are simulating data entry into QB. QB never shows you the ids...

                          Edit: You also can't add a header with no line items. Try it from a QB invoice screen. It won't allow it there either...
                          Last edited by Al Buchholz; 11-21-2007, 09:31 PM.
                          Al Buchholz
                          Bookwood Systems, LTD
                          Weekly QReportBuilder Webinars Thursday 1 pm CST

                          Occam's Razor - KISS
                          Normalize till it hurts - De-normalize till it works.
                          Advice offered and questions asked in the spirit of learning how to fish is better than someone giving you a fish.
                          When we triage a problem it is much easier to read sample systems than to read a mind.
                          "Make it as simple as possible, but not simpler."
                          Albert Einstein

                          http://www.iadn.com/images/media/iadn_member.png

                          Comment


                            #14
                            Re: Inserting records into QuickBooks invoice line table

                            Al and others,
                            First, Happy Thanksgiving.

                            I was finally successful in creating an invoice (header and lines). Here is the script that did it:

                            'Date Created: 18-Nov-2007 12:06:50 PM
                            'Last Updated: 22-Nov-2007 02:54:42 PM
                            'Created By : User
                            'Updated By : User
                            '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_link")
                            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

                            Dim UnMappedColumns as c
                            UnMappedColumns = <<%txt%
                            TxnID|C
                            InvoiceLineItemRefFullName|C
                            InvoiceLineDesc|M
                            InvoiceLineQuantity|N
                            InvoiceLineRate|N
                            %txt%

                            'Turn the list of UnMappedColumns into a argument definition string
                            dim pargs_c as c
                            pargs_c = a5_SimpleArgumentDefinition(UnMappedColumns)
                            dim pargs as p

                            'Turn the argument definition string into an object
                            property_from_string(pargs,pargs_c)
                            delete pData
                            dim pData as p

                            'Prompt for values for each of the UnMapped columns
                            'pData = a5_promptforparameters("Specify column values",pargs.Arguments,pargs.OKButtonLabel,pargs.CancelButtonLabel)

                            'if pData.Last_button_pressed = "Cancel" then
                            ' end
                            'end if


                            args.set("InvoiceLineItemRefFullName","Blueprint Changes")
                            args.set("InvoiceLineDesc","Blueprint changes to shed and poolhouse")
                            args.set("InvoiceLineQuantity",2)
                            args.set("InvoiceLineRate",500)
                            args.set("FQSaveToCache",1)


                            dim sqlInsertStatement as c
                            sqlInsertStatement = <<%txt%
                            INSERT INTO InvoiceLine (InvoiceLineItemRefFullName, InvoiceLineDesc, InvoiceLineQuantity, InvoiceLineRate, FQSaveToCache) VALUES ( :InvoiceLineItemRefFullName, :InvoiceLineDesc, :InvoiceLineQuantity, :InvoiceLineRate, :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 the header info goes in
                            args.set("CustomerRefFullName","Talbott")
                            args.set("RefNumber","98")

                            dim sqlInsertStatement as c
                            sqlInsertStatement = <<%txt%
                            INSERT INTO Invoice (CustomerRefFullName, RefNumber) VALUES (:CustomerRefFullName, :RefNumber)
                            %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()

                            I will now substitute variables and that should complete the project.
                            Thanks again for all of your help.

                            Don't eat too much turkey. Remember, you are what you eat <grin>.
                            Jay Talbott
                            Lexington, KY

                            Comment


                              #15
                              Re: Inserting records into QuickBooks invoice line table

                              Thanks for posting your solution Jay! I am sure this will help others in the future...like me!:D

                              Comment

                              Working...
                              X