Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2011-09-20
7
Medium Priority
?
1,368 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 101

Expert Comment

by:mlmcc
ID: 36571242
0
 
LVL 101

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:Annaliese Dell
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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
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:
Annaliese Dell earned 2000 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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Integration Management Part 2
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
Suggested Courses

926 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