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

Ready to Ship report

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

    Ready to Ship report

    Hello. I'm a new user, and the number of tables and fields is a bit overwhelming. I want to make a "ready to ship" report of all invoices which have been paid in full, but where the "Other" field (which we use to store the UPS tracking number) is blank.

    I just need a little guidance on this first report, then I'll be able to make many more on my own.

    Here are columns I want to show:

    Invoice#, PO#, Buyer's Name, Phone, Email, Invoice Total, Amt Paid, Date Paid

    And I want to select records based on the following criteria:

    1) Invoice is paid. That could either be that the amount paid equals the invoice total (or balance due is zero, or invoicetotal minus amount paid equals zero, etc)
    AND
    2) The "other" field is empty (or "other" does NOT start with 1Z, which all UPS tracking codes start with)

    So my question is:
    * Which tables do I need? I think the Amount paid might be stored somewhere besides "Invoices"
    * Anything I need to know to make this work right?

    Thanks for your help. I'm looking to get this done this afternoon, after your company closes for the day, so your answer will be a BIG HELP to me.

    Many thanks.

    -Mark


    p.s. I'll also be needing a second report that relies on data that we'll add via a user-defined field in the invoices table. Will that field show up automatically in the invoices table after I add it to Quickbooks?

    #2
    Re: Ready to Ship report

    hi mark,

    not sure what you mean by 'what tables do i need'

    in terms of filtering your report, thats quite easy. in the report, go to the menu bar and select 'report' then click 'select records' here you can build your filter.

    another option is to add the filter to a button which will open the report. if you use action scripting for this button, it will give you an option to select records based upon a filter.

    hope this helps

    Comment


      #3
      Re: Ready to Ship report

      didnt realise this was in q reports, just ignore me, i thought it was a normal report questions

      sorry

      Comment


        #4
        Re: Ready to Ship report

        Hi Mark,
        Check out the InvoiceLine table for your fields.

        Once you have the report based upon the correct table and your fields added to your report, you can create a filter for it.

        Many ways to do such but easiest would be to go the top menu when in design mode for the report, choose Reports, then choose Select Records. A dialog box will pop up and you will see two fields...one for an order expression and the other for a filter.

        Pressing the button to the right of the filter field will bring up the filter's expression builder.

        I believe there is an "IsPaid" field in the InvoiceLine table to indicate that an invoice has been paid. Choose this field in the builder and have it equal .T. . then add the .AND. . The next part of the expression would use this if "other" is indeed your field name. other<>"".

        So end result would be along the lines of
        Code:
        IsPaid=.T. .AND. other<>""
        If there is a chance that something could be in the other field besides a UPS code (or an incorrect one -- one that does not start with 1Z) then you could continue with adding more such as....
        Code:
        IsPaid=.T. .AND. other<>"" .AND. substr(alltrim(other),1,2)="1Z"

        p.s. I'll also be needing a second report that relies on data that we'll add via a user-defined field in the invoices table. Will that field show up automatically in the invoices table after I add it to Quickbooks?
        If you use an existing custom field from QB then yes.
        Mike
        __________________________________________
        It is only when we forget all our learning that we begin to know.
        It's not what you look at that matters, it's what you see.
        Henry David Thoreau
        __________________________________________



        Comment


          #5
          Re: Ready to Ship report

          Originally posted by BangTheGavel View Post
          Hello. I'm a new user, and the number of tables and fields is a bit overwhelming. I want to make a "ready to ship" report of all invoices which have been paid in full, but where the "Other" field (which we use to store the UPS tracking number) is blank.

          I just need a little guidance on this first report, then I'll be able to make many more on my own.

          Here are columns I want to show:

          Invoice#, PO#, Buyer's Name, Phone, Email, Invoice Total, Amt Paid, Date Paid

          And I want to select records based on the following criteria:

          1) Invoice is paid. That could either be that the amount paid equals the invoice total (or balance due is zero, or invoicetotal minus amount paid equals zero, etc)
          AND
          2) The "other" field is empty (or "other" does NOT start with 1Z, which all UPS tracking codes start with)

          So my question is:
          * Which tables do I need? I think the Amount paid might be stored somewhere besides "Invoices"
          * Anything I need to know to make this work right?
          I think all of the info is in the invoice table.

          Originally posted by BangTheGavel View Post
          Thanks for your help. I'm looking to get this done this afternoon, after your company closes for the day, so your answer will be a BIG HELP to me.

          Many thanks.

          -Mark


          p.s. I'll also be needing a second report that relies on data that we'll add via a user-defined field in the invoices table. Will that field show up automatically in the invoices table after I add it to Quickbooks?
          Custom fields will show when the table is refreshed if you are using the select *

          Otherwise, you'll have to add the fields to the select list.
          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


            #6
            Re: Ready to Ship report

            I looked a bit more into this...

            Assuming the Buyer's Name is the Customer name, in order to use the Phone and Email fields you would create a set between the Invoice table and the Customer table. For the Set linkage use the Customerreflistid and Listid respectively. Invoice to be parent and Customer table child (one to one linkage).

            Once done the fields will be available to use in your report.
            Mike
            __________________________________________
            It is only when we forget all our learning that we begin to know.
            It's not what you look at that matters, it's what you see.
            Henry David Thoreau
            __________________________________________



            Comment


              #7
              Re: Ready to Ship report

              Thanks Mike, Al, and Richard.

              I've got the report working. I even linked to the customer table to get the customer's email address and phone. Great response and help from you, so thank you.

              Only a few minor things to deal with.

              TIME STAMP: I need to time-stamp the report. I see a system variable for the data, but not the time. Is there a way to print the current time on the report (without having to enter it manually)?

              INVOICE TOTAL: I would like to print the invoice total on the report as well. However, I don't see a field for that (shockingly). I see Subtotal, and I see AppliedAmount (which is a negative number) but I don't see a Total. Do I have to calculate that somehow, by adding tax? Or is it in another table?

              Many thanks.

              -Mark

              Comment


                #8
                Re: Ready to Ship report

                Mark,
                The Subtotal field is the invoice total prior to tax generally. The appliedamount field is with tax (or other addons---actual amount paid).

                The current Time can be placed on a report by creating a calculated field and simply setting it to be equal to now().

                If just the total of what you may have filtered for on the invoice, then use the drag-drop genie and place the invoice amount field you use to a footer and the genie will pop up--choose total.
                Mike
                __________________________________________
                It is only when we forget all our learning that we begin to know.
                It's not what you look at that matters, it's what you see.
                Henry David Thoreau
                __________________________________________



                Comment


                  #9
                  Re: Ready to Ship report

                  Got it, Mike. That's working fine now.
                  One thing I can't seem to find, though, are the payment records.
                  How are payments for specific invoices stored in Quickbooks?
                  We use Intuit's credit card processing, so payments are usually imported into the system directly. Occasionally, we enter a check or CC payment manually.
                  (I don't think that matters, but who knows?!?!)
                  In any case, which table has the payments for an invoice? I'd want to show the most recent payment made, the amount, and the date.
                  Thanks.
                  -Mark

                  Comment


                    #10
                    Re: Ready to Ship report

                    Originally posted by BangTheGavel View Post
                    Got it, Mike. That's working fine now.
                    One thing I can't seem to find, though, are the payment records.
                    How are payments for specific invoices stored in Quickbooks?
                    We use Intuit's credit card processing, so payments are usually imported into the system directly. Occasionally, we enter a check or CC payment manually.
                    (I don't think that matters, but who knows?!?!)
                    In any case, which table has the payments for an invoice? I'd want to show the most recent payment made, the amount, and the date.
                    Thanks.
                    -Mark
                    Look at the invoicelinkedtxn table to get the transactions for an invoice.

                    There are other linkedtxn tables for other tables also....
                    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


                      #11
                      Re: Ready to Ship report

                      Thanks for that, Al!
                      So actually, what I've now done is base the entire report on the InvoiceLinkedTxn table, and related it to the Customer table (for the email and phone data) and now everything is running fine....
                      Except...
                      I've just been handed a new requirement which has me a bit stumped.
                      Up to now, we've stored the Tracking number in the CustomFieldOther field in the Invoice (and InvoiceLinkedTxn) table, so it is easy to select records where that field is empty.
                      However we are now using a quickbooks add-on program that stores the Tracking Code in a line item (InvoiceLine table) with a specific item code (ie InvoiceLineItemRefFullName is "Shipping Info")
                      And my requirement is that I show a report where there is NO SUCH RECORD.
                      So, I've manually linked InvoiceLine to my InvoiceLinkedTxn table, but I don't see how to write a query in QRB that basically says...
                      "Include all records from invoicelinetxn where we do NOT find an invoiceline record for "Shipping Info"
                      I think I need a subquery, but I dont' see how to do that in QRB either.
                      And my college SQL is failing me.
                      Any hints on how to select records for this query?
                      -Mark

                      Comment


                        #12
                        Re: Ready to Ship report

                        Originally posted by BangTheGavel View Post
                        Thanks for that, Al!
                        So actually, what I've now done is base the entire report on the InvoiceLinkedTxn table, and related it to the Customer table (for the email and phone data) and now everything is running fine....
                        Except...
                        I've just been handed a new requirement which has me a bit stumped.
                        Up to now, we've stored the Tracking number in the CustomFieldOther field in the Invoice (and InvoiceLinkedTxn) table, so it is easy to select records where that field is empty.
                        However we are now using a quickbooks add-on program that stores the Tracking Code in a line item (InvoiceLine table) with a specific item code (ie InvoiceLineItemRefFullName is "Shipping Info")
                        And my requirement is that I show a report where there is NO SUCH RECORD.
                        So, I've manually linked InvoiceLine to my InvoiceLinkedTxn table, but I don't see how to write a query in QRB that basically says...
                        "Include all records from invoicelinetxn where we do NOT find an invoiceline record for "Shipping Info"
                        I think I need a subquery, but I dont' see how to do that in QRB either.
                        And my college SQL is failing me.
                        Any hints on how to select records for this query?
                        -Mark
                        Mark

                        You're becoming a QRB expert with SQL. Congrats..

                        There are 2 or 3 ways of approaching this.

                        1. Write a custom SQL for the extraction from QB with a multiple table join and filter to get what you want.

                        2. Extract the individual tables and let the QRB set structure do the filtering.

                        3. Follow #2 and preprocess the data prior to running the report.

                        For more details, come to the webinar on Thursday.
                        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


                          #13
                          Re: Ready to Ship report

                          Thanks for the compliment, Al.
                          I hope to be able to give back to the community once I become a tenth as smart as you about QRB.
                          I will attend on Thursday, but our requirement is to get this report running today.
                          All of your solutions, however, look like Greek to me -- they seem to require more knowledge of QRB than I have.
                          Can you point me to the documentation where I can read up on those?
                          Is there a place where I can just write the SQL query I want to execute? If so, where?
                          Alternatively, can I just buy an hour of your time?
                          --Mark

                          Comment


                            #14
                            Re: Ready to Ship report

                            Al,

                            So I gave this a shot. I figured if I could just redefine the InvoiceLine table to include only those records with "Shipping:UPS SHIPPING INFO" (and similar for USmail and Other shipping types) as the item's name, then I could run the report for all invoice records which did NOT match to an invoiceline table.

                            So, I made my own invoiceline table, called invoicelineshippingmlw (mlw are my initials, so I can easily identify tables I make myself). This custom table is a copy of the invoiceline table, with a filter that limits which records are selected.

                            Here's the actual where clause:

                            SELECT * FROM InvoiceLine WHERE InvoiceLineItemRefFullName LIKE 'Shipping:UPS SHIPPING INFO%' OR InvoiceLineClassRefFullName LIKE 'Shipping:USMAIL SHIPPING INFO%' OR InvoiceLineItemRefFullName LIKE 'Shipping:OTHER SHIPPING INFO%'

                            Is this what you had in mind?

                            -Mark

                            p.s. I'll keep working on this, but I look forward to your comments.

                            Comment


                              #15
                              Re: Ready to Ship report

                              Originally posted by BangTheGavel View Post
                              Al,

                              So I gave this a shot. I figured if I could just redefine the InvoiceLine table to include only those records with "Shipping:UPS SHIPPING INFO" (and similar for USmail and Other shipping types) as the item's name, then I could run the report for all invoice records which did NOT match to an invoiceline table.

                              So, I made my own invoiceline table, called invoicelineshippingmlw (mlw are my initials, so I can easily identify tables I make myself). This custom table is a copy of the invoiceline table, with a filter that limits which records are selected.

                              Here's the actual where clause:

                              SELECT * FROM InvoiceLine WHERE InvoiceLineItemRefFullName LIKE 'Shipping:UPS SHIPPING INFO%' OR InvoiceLineClassRefFullName LIKE 'Shipping:USMAIL SHIPPING INFO%' OR InvoiceLineItemRefFullName LIKE 'Shipping:OTHER SHIPPING INFO%'

                              Is this what you had in mind?

                              -Mark

                              p.s. I'll keep working on this, but I look forward to your comments.
                              That is certainly part of it. You may also want to get only the fields (aka columns) that are needed... the linking field(s) and data that is needed.

                              Or you can go another set and select multiple tables - link and filter in one SQL statement...

                              Or use this table (invoicelineshippingmlw) in a QRB set along with the invoicelinkedtxn table..
                              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

                              Working...
                              X