New call-to-action
Results 1 to 29 of 29

Thread: So confused... how to figure out the tables and fields?

  1. #1
    Member
    Real Name
    Beth
    Join Date
    Jul 2011
    Posts
    17

    Default So confused... how to figure out the tables and fields?

    I need to make two reports and I am having a very tough time trying to figure out where to start with all of the tables and fields.

    The reports we need will be used to check over our entries to ensure accuracy. We need the following:
    Payroll
    - Employee Name
    - Hours at regular pay rate
    - Regular pay rate
    - Regular pay earnings
    - Hours at overtime pay rate
    - Overtime pay rate
    - Overtime pay earnings
    - Vacation hours used
    - Gross Pay
    - FICA
    - FICA Wages
    - SUTA
    - Total Deductions
    - Workmans' Comp
    - Net Pay

    A/P:
    -Reference #
    - Vendor Name
    - Invoice Date
    - Account
    - Class
    - Amount
    - Discount

    Is this even possible? Is there something I can read to figure out which tables contain the information needed? What about a glossary for the fields?

    Thanks.

  2. #2
    "Certified" Alphaholic Keith Hubert's Avatar
    Real Name
    Keith Hubert
    Join Date
    Jul 2000
    Location
    London, UK
    Posts
    6,930

    Default Re: So confused... how to figure out the tables and fields?

    Hi Beth,

    Welcome to Alpha Forum.

    Is it possible to make reports from your tables, yes of course it is. The reports will not prove accuracy, they will only prove what was entered.
    Regards
    Keith Hubert
    Alpha Guild Member
    London.
    KHDB Management Systems
    Skype = keith.hubert


    For your day-to-day Needs, you Need an Alpha Database!

  3. #3
    "Certified" Alphaholic
    Real Name
    Mike Christensen
    Join Date
    Nov 2005
    Location
    Michigan U.P.
    Posts
    5,937

    Default Re: So confused... how to figure out the tables and fields?

    For payroll:

    Using the PayrollItemDetail report or TimeTracking table either/both in conjunction with the EmployeeEarning table should have all the information you require.


    For A/P:

    Sounds like the SalesLine table.


    (btaylor.........real name???)
    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
    __________________________________________




  4. #4
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,662

    Default Re: So confused... how to figure out the tables and fields?

    I would think for AP you want to look at bills that are unpaid.

    When you build a report and pick a table, you can press a button that shows the QB form that holds the data in the table. Roll over the field names and an area of the form shows where the field is on the form..

    The names of the tables start with a word that describes the general purpose of the table.

    Invoice
    Checks
    Credit Memos
    Bills
    .......
    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.

  5. #5
    "Certified" Alphaholic
    Real Name
    Mike Christensen
    Join Date
    Nov 2005
    Location
    Michigan U.P.
    Posts
    5,937

    Default Re: So confused... how to figure out the tables and fields?

    oops!! Looked at both SalesLine and BillExpenseLine and found the vendor name/listid in one of them....gave the wrong one! BillExpenseLine has the vendor info.
    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
    __________________________________________




  6. #6
    Member
    Real Name
    Beth
    Join Date
    Jul 2011
    Posts
    17

    Default Re: So confused... how to figure out the tables and fields?

    Quote Originally Posted by Keith Hubert View Post
    Hi Beth,

    Welcome to Alpha Forum.

    Is it possible to make reports from your tables, yes of course it is. The reports will not prove accuracy, they will only prove what was entered.
    Right. That's the goal... to find something that helps us to manually prove accuracy.

  7. #7
    Member
    Real Name
    Beth
    Join Date
    Jul 2011
    Posts
    17

    Default Re: So confused... how to figure out the tables and fields?

    Quote Originally Posted by MikeC View Post
    oops!! Looked at both SalesLine and BillExpenseLine and found the vendor name/listid in one of them....gave the wrong one! BillExpenseLine has the vendor info.
    This was very helpful.

    Is there a way to create a report that calculates the discount amount for each invoice? Either before or after it's paid?
    Last edited by btaylor; 08-02-2011 at 07:22 AM.

  8. #8
    Member
    Real Name
    Beth
    Join Date
    Jul 2011
    Posts
    17

    Default Re: So confused... how to figure out the tables and fields?

    Also, is there a quick way to add fields to a report without starting over?

    Is there a way to filter for transactions entered on a certain date?

  9. #9
    "Certified" Alphaholic
    Real Name
    Mike Christensen
    Join Date
    Nov 2005
    Location
    Michigan U.P.
    Posts
    5,937

    Default Re: So confused... how to figure out the tables and fields?

    I suggest first to go through the User Guide section on Reports as this should have the answers to most of your questions (and "Yes" is the answer to all your questions!).

    http://support.alphasoftware.com/alp...ort_Topics.htm

    If you still have questions on how to implement something definitely post back! (Please post back even if you find your solutions as it will help the next user who may have the same questions).
    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
    __________________________________________




  10. #10
    Member
    Real Name
    Beth
    Join Date
    Jul 2011
    Posts
    17

    Default Re: So confused... how to figure out the tables and fields?

    Quote Originally Posted by MikeC View Post
    I suggest first to go through the User Guide section on Reports as this should have the answers to most of your questions (and "Yes" is the answer to all your questions!).

    http://support.alphasoftware.com/alp...ort_Topics.htm

    If you still have questions on how to implement something definitely post back! (Please post back even if you find your solutions as it will help the next user who may have the same questions).
    Thanks. I was looking for that.

    Is there a glossary of terms defining the items? Sometimes I'm not sure I'm on the right track. Particularly with the discounts. None of the configurations I've tried yield data on the discounts.

    I think the software must be pretty powerful, but man is it dauting to try to figure out!

  11. #11
    Member
    Real Name
    Beth
    Join Date
    Jul 2011
    Posts
    17

    Default Re: So confused... how to figure out the tables and fields?

    Also, I don't see the quick report option?

  12. #12
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,662

    Default Re: So confused... how to figure out the tables and fields?

    Quote Originally Posted by btaylor View Post
    Also, I don't see the quick report option?
    The quick report option is a way to start a report, but not to modify it. It is limited to that scope - it's a starting point.

    Use the report editor for full functionality.

    After using the report editor, you'll move past the quick report and use only the report editor...
    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.

  13. #13
    Member
    Real Name
    Beth
    Join Date
    Jul 2011
    Posts
    17

    Default Re: So confused... how to figure out the tables and fields?

    Thanks Al. I was going off of the User Guide, but I still don't see it.

    _______________________________________________________________________

    I really appreciate your help with me figuring this out.

    I'm not finding what I need for the discount portion of the AP reports (I haven't started on the payroll because I thought that the AP would be easier). In particular, I can't find a field that shows the discount for paying a bill on time. Does such a thing exist?

  14. #14
    "Certified" Alphaholic
    Real Name
    Mike Christensen
    Join Date
    Nov 2005
    Location
    Michigan U.P.
    Posts
    5,937

    Default Re: So confused... how to figure out the tables and fields?

    Try the Sales / Invoice and related tables--SuggestedDiscountAmount field is present in both.....
    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
    __________________________________________




  15. #15
    Member
    Real Name
    Beth
    Join Date
    Jul 2011
    Posts
    17

    Default Re: So confused... how to figure out the tables and fields?

    Quote Originally Posted by MikeC View Post
    Try the Sales / Invoice and related tables--SuggestedDiscountAmount field is present in both.....
    Is this for AR or AP? I need AP :(

  16. #16
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,662

    Default Re: So confused... how to figure out the tables and fields?

    Quote Originally Posted by btaylor View Post
    I'm not finding what I need for the discount portion of the AP reports (I haven't started on the payroll because I thought that the AP would be easier). In particular, I can't find a field that shows the discount for paying a bill on time. Does such a thing exist?
    Are you entering a bill and then paying with a check?

    When I do that I can designate the payment discount. If I look at the BillPaymentCheckLine table I see the discount data.
    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.

  17. #17
    Member
    Real Name
    Beth
    Join Date
    Jul 2011
    Posts
    17

    Default Re: So confused... how to figure out the tables and fields?

    Quote Originally Posted by Al Buchholz View Post
    Are you entering a bill and then paying with a check?

    When I do that I can designate the payment discount. If I look at the BillPaymentCheckLine table I see the discount data.
    Which field shows you the discount data? I don't see them.

    You run this after paying bills (but before printing checks)? Maybe that's why I'm not seeing anything... but none of the fields look related to discounts.

  18. #18
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,662

    Default Re: So confused... how to figure out the tables and fields?

    This is what I seeaapdiscountamt.PNG
    Last edited by Al Buchholz; 08-02-2011 at 08:40 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.

  19. #19
    Member
    Real Name
    Beth
    Join Date
    Jul 2011
    Posts
    17

    Default Re: So confused... how to figure out the tables and fields?

    Quote Originally Posted by Al Buchholz View Post
    This is what I see
    Attachment 28951
    Thanks... though the attachment isn't working. I get a message that says it's invalid. :(

  20. #20
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,662

    Default Re: So confused... how to figure out the tables and fields?

    Quote Originally Posted by btaylor View Post
    Thanks... though the attachment isn't working. I get a message that says it's invalid. :(
    Sorry about that. Fixed it...
    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.

  21. #21
    Member
    Real Name
    Beth
    Join Date
    Jul 2011
    Posts
    17

    Default Re: So confused... how to figure out the tables and fields?

    Thank you. I tried to drag and drop that in, but no data shows up when I run the report. Any thoughts?

  22. #22
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,662

    Default Re: So confused... how to figure out the tables and fields?

    Quote Originally Posted by btaylor View Post
    Thank you. I tried to drag and drop that in, but no data shows up when I run the report. Any thoughts?
    stop in at the webinar tomorrow and let's take a look at this.
    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.

  23. #23
    Member
    Real Name
    Beth
    Join Date
    Jul 2011
    Posts
    17

    Default Re: So confused... how to figure out the tables and fields?

    I think I need to create a new report. I browsed the data and maybe I didn't have it in there right. Will let you know!

  24. #24
    Member
    Real Name
    Beth
    Join Date
    Jul 2011
    Posts
    17

    Default Re: So confused... how to figure out the tables and fields?

    I think that our problem is that we need to see data from two sets on one report, linked on a single line item.

    We need to see things off of the billexpenseline and billpaymentcheckline - with the transactions linked.

    Is that even possible?

  25. #25
    "Certified" Alphaholic Keith Hubert's Avatar
    Real Name
    Keith Hubert
    Join Date
    Jul 2000
    Location
    London, UK
    Posts
    6,930

    Default Re: So confused... how to figure out the tables and fields?

    Hi Beth,

    I'm not a Quickbook expert but this sounds like a serious case of data manipulation is required.

    By that I mean, extracting the required data from one table, some from another and either appending or posting into a third table from which the report is run.

    Sounds like it could be a bit of work, but when its done and that would be after a few trial and errors, you will be very pleased.
    Regards
    Keith Hubert
    Alpha Guild Member
    London.
    KHDB Management Systems
    Skype = keith.hubert


    For your day-to-day Needs, you Need an Alpha Database!

  26. #26
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,662

    Default Re: So confused... how to figure out the tables and fields?

    Quote Originally Posted by btaylor View Post
    I think that our problem is that we need to see data from two sets on one report, linked on a single line item.

    We need to see things off of the billexpenseline and billpaymentcheckline - with the transactions linked.

    Is that even possible?
    I look at the linked transaction tables - they are the data that is shown when you press the history button on the QB screen.

    Sounds like you should be at the webinar tomorrow...
    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.

  27. #27
    Member
    Real Name
    Beth
    Join Date
    Jul 2011
    Posts
    17

    Default Re: So confused... how to figure out the tables and fields?

    Quote Originally Posted by Al Buchholz View Post
    I look at the linked transaction tables - they are the data that is shown when you press the history button on the QB screen.

    Sounds like you should be at the webinar tomorrow...
    I plan on it. (I do work from home and have a 7 month old, so sometimes my schedule has erratic interruptions...)

  28. #28
    Volunteer Moderator
    Real Name
    Alan Buchholz
    Join Date
    Oct 2000
    Location
    Delavan, Wisconsin
    Posts
    9,662

    Default Re: So confused... how to figure out the tables and fields?

    Quote Originally Posted by btaylor View Post
    I plan on it. (I do work from home and have a 7 month old, so sometimes my schedule has erratic interruptions...)
    Beth

    Thanks for attending the webinar. The little guy sounds great.. He should start helping Mom soon...
    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.

  29. #29
    Member
    Real Name
    Beth
    Join Date
    Jul 2011
    Posts
    17

    Default Re: So confused... how to figure out the tables and fields?

    I wish! Sorry that I had to step out - looked like an awesome opportunity to learn, and and I did pick up on a few things.

    Any resoureces that I could read up on in regards to linking tables? Perhaps that's in the user guide. I'll check there.

Similar Threads

  1. Can't figure out this one
    By frankbicknell in forum Application Server Version 10 - Web/Browser Applications
    Replies: 10
    Last Post: 07-10-2011, 09:40 AM
  2. Just can't Figure it out?!*
    By kellerm in forum Alpha Five Version 9 - Desktop Applications
    Replies: 6
    Last Post: 01-09-2011, 07:52 PM
  3. Can't figure this one
    By Jerry Hartness in forum Alpha Five Version 4
    Replies: 3
    Last Post: 11-22-2004, 02:28 PM
  4. Can't figure it II
    By Bruce MacPherson in forum Alpha Five Version 4
    Replies: 7
    Last Post: 09-14-2000, 12:05 PM
  5. Can't figure it
    By Bruce MacPherson in forum Alpha Five Version 4
    Replies: 16
    Last Post: 09-14-2000, 04:45 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •