Solved

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

Posted on 2011-09-20
7
1,238 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
  • 3
  • 2
  • 2
7 Comments
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 17

Author Comment

by:MIKE
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Ok...I'll check it out....and see what happens...be in touch soon...
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

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…
I recently went through setting up a JasperReports Server using the AWS EC2 instance, and this article will cover some basic administration tasks I had to perform.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

771 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now