Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2011-09-20
7
Medium Priority
?
1,332 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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

704 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