Solved

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

Posted on 2011-09-20
7
1,308 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 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
Independent Software Vendors: 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:
Annaliese Dell 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

1. Set up your parameter at the report level as usual, check the box Multi-value, and set the Data Type to String 2. Set the Stored Procedure Parameter to varchar(max)  --<---- This part here is the key to it's success Example:    @cst_key var…
Hello, In my precious Article  (http://www.experts-exchange.com/Database/Reporting/A_15280-Create-Project-in-Microstrategy-Part-I.html)we saw the Configuration part for Microstrategy which included Metadata Creation and DataSource Preparation as …
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

628 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