CData Quickbooks Replication to SQL - Tables required for Accounts Receivables report

Jeremy Poisson
Jeremy Poisson used Ask the Experts™
on
I currently implemented a technology from cdata software (cdata.com)  that replicates tables from QuickBooks (contractor version 18) to SQL (screenshot attached) and, I'm now in the process of trying to create an accounts receivable report based off of the newly replicated data.

Do any of you experts know which tables (presumably credit memos and invoices) and data in those tables I will need to get proper AR data? I'd like to be able to see the current AR, 1-30, 31-60, 61-90, 91+ days past due, if possible.

Thank you all, in advance!!

CDATA_REPLICATION.png
QuickBooks_Tables.png
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Hi,
 I 'd like to try to help you with this, but I can't see your screenshot.   Jeremy, I'm not familiar with the replica tabled, but I could tell you what affects A/R.
 Invoice Total's….they would include already include shipping and taxes or late fees. Everything that the customer owes the company.
 Credit Memos....would be deducted from A/R
Customer Sales Discounts.....would be deducted from A/R
Refunds....although not common,....would be deducted from A/R.

Don't forget Customer Payments....would be deducted from A/R


That's about all I could think of for now.

Hope this helps.

Rob
JohnBusiness Consultant (Owner)
Most Valuable Expert 2012
Expert of the Year 2018

Commented:
Anything you can run as a report, you can export to Excel and from Excel, use the data.

So to the above, run Accounts Receivable. Aging Summary and export. .

If you wish to get detail transactions out of Quick Books, you need a third party exporting tool that you can get from the Intuit Marketplace.
Jeremy PoissonPresident

Author

Commented:
Hi John - I've already implemented a 3rd party intermediary that replicates data from QB via a web API. My question is related to getting the proper SQL tables to view the accounts receivable aging for customers. I'm unsure which tables I need to join, and I'm what order.
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Jeremy PoissonPresident

Author

Commented:
Hi Robert - I will get more screenshots and info for review!
JohnBusiness Consultant (Owner)
Most Valuable Expert 2012
Expert of the Year 2018

Commented:
Jeremy PoissonPresident

Author

Commented:
Hi John - I apologize for not being clear. I've already automated the data (all tables are replicated from QB to SQL) and have previously corresponded with the CEO of Blendo. My question is not how do we replicate - again, this is complete. My question is which tables do I need to use to view AR data. This would probably require someone familiar with CData and their table structure, Or, someone with intimate knowledge of QuickBooks.
JohnBusiness Consultant (Owner)
Most Valuable Expert 2012
Expert of the Year 2018

Commented:
QOBDC may be what you are looking for

http://qodbc.com/data-layouts/

They have the Schema layout for QB.

I do a lot of QuickBooks reports but Excel (even heavy use) satisfies our needs.
Jeremy PoissonPresident

Author

Commented:
Ironically, QODBC is what I replaced. I don't recall being able to see actual sql statements. I have all of the table names, I need to know which ones to use to generate an SQL query to view accounts receivable data.
Jeremy PoissonPresident

Author

Commented:
Also, the end result is going to be an excel report with an embedded SQL data source. I have 4 separate companies I will consolidate AR data for. 2 companies use QuickBooks and the other 2 use a separate ERP (of which I've already created AR data for). If I could just get the exact tables needed from QB, and the proper SQL joins, I'll be off and running.
Jeremy PoissonPresident

Author

Commented:
This is the CData table list (the tables I replicate from QuickBooks). It's for their 2015 version but, they are still the same for 2017.

http://cdn.cdata.com/help/RQA/sync/pg_alltables.htm
Okay Jeremy,  I gave you some typical Account Receuvable types of transactions that would affect the Accounts Receivable balnce for each customer.  I  hope that's what you were asking?

Maybe I'm not allowed to view screenshots yet?

Rob
Jeremy PoissonPresident

Author

Commented:
Thank you, Robert. It may be on my end (inability to view screenshot) so I'm going to try to upload more for review. Thank you for the feedback on the tables. Of course, the linchpin to all of this is the columns I need to join on. Of course, we'll have primary tables of customer and invoices, but getting the proper joins of the tables beneath - the left outer joins, is where I'm stuck.
Oh Boy Jeremy, I have an accounting background.  I was just looking at the tables.  I clicked om your link.
I'm not sure and too familiar with joins.  Here's where I get kind of lost..  Left Outer Joins.??  Sorry, but I will see what I can tell you.


Rob
Jeremy PoissonPresident

Author

Commented:
No worries, Robert. Any/all help is greatly appreciated! The joins are specific to where the data exists. Meaning, Customers will, of course l, exist in the customer table (so this would be a primary table) then, all customers would definitely have invoices BUT, customers may NOT have credits or discounts, so the 'left outer join' is just another way of saying 'IF this customer exists in these tables, then 'join this data'. I just don't know WHICH tables I need to include to get the right data. I'll figure it out if I go through them another few hundred times but, was hopeful that the experts could push me along in the right direction!
Bill BachPresident and Btrieve Guru

Commented:
It's amazing how many people don't read the text of the original question.  While I agree that it is silly to reinvent the wheel, if you have your own reasons for building your own AR reports instead of using QB's, then so be it.

In this case, you need to start with the Customers table.  
    SELECT ... FROM Customers
The primary key field is called ID.  To find the customer's invoices, join in the Invoices table like this:
    LEFT OUTER JOIN Invoices ON (Customers.ID = Invoices.CustomerId)
You then need to bring in the Credit Memos table like this:
    LEFT OUTER JOIN CreditMemos ON (Customers.ID = CreditMemos.CustomerId)

Depending on which fields you pull the data from (InvoiceTotal or OpenBalance), you may also need to pull out payments, refunds, and other such transfers that would impact the totals.  If it were me, though, I'd use the Invoices.Balance field, and skip everything else, as QB has already calculated this all for you.  (*I am assuming, of course, that CData is replicating it properly -- I am also a QODBC user and query directly from the QB database.)

Getting data into buckets is a non-trivial exercise, though, there are many examples available on the web if you do a search.  Here's one example (specific to GP, but the bucket calculation is what I want you to see):
   https://victoriayudin.com/2013/05/01/sql-view-for-current-receivables-aging-detail-in-dynamics-gp/

Once you have written your SQL query, compare the results against the internal QB report.  If they match, you're good.  If not, try drilling down into the specific buckets that don't match and see if you can tell how/why they differ, then fix the issues up one at a time.
Jeremy PoissonPresident

Author

Commented:
Hi Bill - FANTASTIC feedback. This is exactly the nudge I needed (I think). I'm reading, and reading again, then will complete the statement and comp to QB. I will follow-up with results as soon as I have something to share.

Thank You!!

Jeremy
Jeremy PoissonPresident

Author

Commented:
Also, @Bill - the reason we're straying from the stock QB reports, is that I'm consolidating 4 companies' AR aging across multiple ERPs.
Bill BachPresident and Btrieve Guru

Commented:
Perfectly reasonable explanation to me.
Hi Jerermy,
 
A late reply on my part.  Thanks for understanding.  As you noted. Bill, has given you great feedback.  I agree that his suggestions are very good!


Just one thing to add if I may.  If a customer has been refunded after the payment was made, typically the A/R balance will be 0 for that invoice if the received payment was applied.   What this means is the refund would have to Debit Sales Returns and the corresponding inventory (if they returned an item(s).  So, what I'm saying is that returning the money back to the customer should not have an effect on A/R.  I was incorrect on my original post.  Not too worry about this at all.  The only other thing I can think of is that Payment Method, may be another table you might have to consider? I don't think this will be a factor for A?R, but there is a relationship there as well.


Bill has done explained it very well as you already commented on.


Good luck.


Rob
Jeremy PoissonPresident

Author

Commented:
Hi Rob - thank you for the great feedback! You've provided even more to consider so I cover all of my bases. I'm still working through the process but will keep everyone posted.


Jeremy
You're welcome Jeremy.  Great to hear from you.  I'll keep you posted too if anything comes to mind.

Roib
Jeremy PoissonPresident

Author

Commented:
Team - I ended up resolving this after going through all of the tables and found the proper way to join them properly to get then open balance values. I apologize for the delay as I had several emergency issues with clients to attend to.

Thank you all for your valuable feedback and time!

Jeremy
Good going Jeremy!
Jeremy PoissonPresident

Author

Commented:
Thank you very much! I went through all of their online documentation and finally found something useful! Very happy to have this 3 company consolidated AR dashboard finally out of the way!
President
Commented:
Found detailed schema framework for AR from vendor website that allowed me to join tables in proper order to get the correct open balance for each job/customer in QuickBooks!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial