Solved

How do I LINK the CLASS table to the TRANSACTION table in Quiickbooks?

Posted on 2011-09-20
7
1,284 Views
Last Modified: 2012-08-13
I'm uisng the QODBC driver to access the Quickbooks v11 data tables to build customized reports using Crystal Reports.

I need to know HOW to link the CLASS table to the TRANSACTION table as I'm creating a Financial Statement that requires a CLASS breakdown.

Which tables are required to accomplish the LINK....???

Thanks
0
Comment
Question by:MIKE
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
7 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 36571242
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 36571259
Class relationships

http://doc.qodbc.com/qodbc/20/tables/table_relations_us0957.html?trelations_id=10&tn_us=TRUE

Transaction relationships
http://doc.qodbc.com/qodbc/20/tables/table_relations_us1ced.html?trelations_id=123&tn_us=TRUE

Class seems to be linked through the other tables like Invoice, Sales, etc

mlmcc
0
 
LVL 8

Expert Comment

by:KoiGirl
ID: 36574200
I don't know what financial statement you need so this is a shot in the dark. I'm using Premier Contractor 2009 and QODBC 2009.

Do you need to create the reports from QuickBooks tables or can you use sp_ProfitAndLossByClass like this:

sp_report ProfitAndLossByClass parameters datefrom={d'2009-10-01'},dateto={d'2009-10-31'}

If you need only specific classes, there is also:

sp_report ProfitAndLossStandard show text,label,amount_1 as amount parameters datefrom={d '2009-10-01'},dateto={d '2009-10-31'} ,returnrows='All',ClassFilterListIDs='A0000-883954149','80000-883954125','670000-1133059440','70000-883954113'

I don't believe there is a link between transaction and class. I believe that is only accomplished through transactionLINES, for example: checkexpenseline, checkitemline, etc.

There is also:

sp_report TxnDetailByAccount show ReportSubtitle,date,text,rowdata,accounttype,accountname,amount,class, debit,credit,txntype parameters  datefrom={d'2009-10-01'},dateto={d'2009-10-05'}

This is a slow report (310 transaction for 5 days in my QuickBooks) but does include the Classes for transaction detail lines. I'm not an accountant so not sure if that works for you.

Otherwise, you may have to link class to transactionLINES, like checkexpenseline, checkitemline to build the financial statement.

Finally, if you need to subtract SPECIFIC classes from the ProfitAndLossStandard, I just posted this how-to using Microsoft Access 2002. It shows how to build a simple form to input DateFrom and DateTo and select the classes to exclude and then creates the P&L excluding those classes.

http://www.vbquick.com/2011/09/exclude-classes-from.html

I am assuming you know how to use the VBDemo to test these? If not, just type them into the Query box (or whatever they call it) and hit the Query button. (sorry if you know all this)

Hope this helps. I'm using 2009 so maybe you will find more functionality with 2011. Best of luck to you!  :)
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 17

Author Comment

by:MIKE
ID: 36576921
Sorry for the delayed reply...

I need to know which specific TABLES....quickbooks DATA TABLES to use to LINK together the CLASS and the TRANSACTION tables.

I'm creating Customized Financial Statements from scratch....base....TABLES....and need to link them using SQL Scrip.....not the "sp_reports".....

I know this can be done, just need to know if anyone has done it yet?

Again this is OUTSIDE of the Quickbooks program and hitting the QB tables directly.....using Crystal.

I would like to write the SQL script first then add that script to a Crystal Report....and format the report from there....etc..et.c..



0
 
LVL 17

Author Comment

by:MIKE
ID: 36576928
FYI,... I believe there are numerous tables that come into play, when trying to link the CLASS table to the TRANSACTION table......
0
 
LVL 8

Accepted Solution

by:
KoiGirl earned 500 total points
ID: 36577659
I believe you will have to use the transactionLINE tables. For example, checkEXPENSELINE, checkITEMLINE, creditcardchargeEXPENSELINE, creditcardchargeITEMLINE, etc. I do not believe you can link TRANSACTIONS to Classes because each TRANSACTION may have multiple transactionLINES; therefore, each transactionLINE may have a different class than the others.

For example, if you query "sp_columns check" to see the check table, you will notice class is not listed. However, if you query sp_columns checkexpenseline or sp_columns checkitemline, you will see ExpenseLineClassRefListID in both tables.

Mlmcc is right that the invoice and sales tables do contain class but not all tables do.

Since you may have different classes for multiple transaction lines of any transaction, you'd have to use transaction expense and item lines anyway instead of transactions.

To join a transactionLINE to the Class table:

ExpenseLineClassRefListID from CheckExpenseLine Table to ListID from Class Table

ExpenseLineClassRefListID from CreditCardChargeExpenseLine Table to ListID from Class Table

etc.

I hope this helps.

0
 
LVL 17

Author Comment

by:MIKE
ID: 36577780
Ok...I'll check it out....and see what happens...be in touch soon...
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem Statement In an SAP BI BO Integration project when a BO universe is built on a BEx query, there can be an issue of unit & formatted value objects not getting generated in a BO universe for some key figures. This results in an issue whereb…
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Suggested Courses

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question