[Bug] Duplicate Entries for General Ledger Transaction ID's for two Accounts Receivable accounts

[mikerenderco]

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

Example transaction ID 150975 has duplicate entries in Bigquery General Ledger. Account_id 501 and account_id 500 are creating duplicate entries. The data from fivetran DBT job for the General Ledger is creating duplicate data for account id 501 and is causing our data to not reconcile. When you look in Quickbooks that isn't the case. See attached screen shots and advise.

Query Export.csv

Relevant error log or model output

No response

Expected behavior

We would only want to have entries for Account ID 500 in the General Ledger.

dbt Project configurations

vars:
using_credit_card_payment_txn: true
using_purchase_order: true

Package versions

packages:

• package: fivetran/quickbooks
  version: [">=0.11.0", "<0.12.0"]

What database are you using dbt with?

bigquery

dbt Version

dbt Version: 1.5

Additional Context

SELECT * FROM back-bar.prod_quickbooks.quickbooks__general_ledger where transaction_id = '150975'

Are you willing to open a PR to help address this issue?

• Yes.
• Yes, but I will need assistance and will schedule time during our office hours for guidance
• No.

[fivetran-jamie]

Hey there @mikerenderco thanks for taking the time to open this and for your continued engagement with the packages!

I'd like to understand this a bit better. So in the csv you attached, should the records where account_id = 501 not be there?

[mikerenderco]

No Problem! Yes they should not be there. I'm not sure why the GL logic is adding values to this account.

…

On Tue, Sep 12, 2023 at 1:59 PM Jamie Rodriguez ***@***.***> wrote: Hey there @mikerenderco <https://github.com/mikerenderco> thanks for taking the time to open this and for your continued engagement with the packages! I'd like to understand this a bit better. So in the csv you attached, should the records where account_id = 501 not be there? — Reply to this email directly, view it on GitHub <#104 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/A23MTHYQ35NHDA3RMST63BTX2DEJLANCNFSM6AAAAAA4T5P3SU> . You are receiving this because you were mentioned.Message ID: ***@***.***>

-- Mike Lambeth 775.813.0944 ***@***.***

[fivetran-jamie]

Interesting...I think there may be an issue with the invoice double entry model

Specifically, it seems we are assuming that there is only one Accounts Receivable account per connector based on this CTE and how we join it in later only based on source_relation. I think this is where account 501 is sneaking in, since it is an AR account.

I see in your csv that the account_name for 501 is null.. would it happen to be deleted?

[mikerenderco]

Jamie so it isn't a deleted account and yes it is labeled as null. The company doesn't use this AR account. As I think through this issue, it may be a choice not to fix this by Fivetran, but Quickbooks doesn't limit you from creating more than one AR account, this isn't typical to have more than one AR account. We totally get why five tran is assuming only one AR account but the DBT job is duplicating it because there is more than one AR account. I'd be ok closing this ticket but let me know if its a scenario you feel like the DBT job should solve for even though it might be a rare occurrence.

…

On Tue, Sep 12, 2023 at 4:08 PM Jamie Rodriguez ***@***.***> wrote: Interesting...I think there may be an issue with the invoice double entry model Specifically, it seems we are assuming that there is only one Accounts Receivable account per connector based on this CTE <https://github.com/fivetran/dbt_quickbooks/blob/main/models/double_entry_transactions/int_quickbooks__invoice_double_entry.sql#L86-L94> and how we join <https://github.com/fivetran/dbt_quickbooks/blob/main/models/double_entry_transactions/int_quickbooks__invoice_double_entry.sql#L195-L196> it in later only based on source_relation. I think this is where account 501 is sneaking in, since it is an AR account. I see in your csv that the account_name for 501 is null.. would it happen to be deleted? — Reply to this email directly, view it on GitHub <#104 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/A23MTHYBX4IS6ONERSW3DG3X2DTO5ANCNFSM6AAAAAA4T5P3SU> . You are receiving this because you were mentioned.Message ID: ***@***.***>

-- Mike Lambeth 775.813.0944 ***@***.***

[fivetran-jamie]

Ah yeah I came across this from Quickbooks

QuickBooks Online isn't designed to work with multiple Accounts Receivable (AR) and Accounts Payable (AP) accounts. QuickBooks Online encourages their customers to merge their accounts payable and receivable accounts into one.

It's interesting that QB doesn't block you from creating multiple AR accounts though...I think what we'll do is keep this issue open but mark it as wontdo. That way if this setup isn't completely rare and other users come across this issue, they can chime in here and we can potentially move forward with a solution for this use case.

Would the remaining duplicates block you from using the package however? If so, I'd recommend overriding the stg_quickbooks__account model to filter out account 501

[mikerenderco]

Sounds good Jamie! No this doesn’t block us from using the package. I am filtering the 501 account out. If we run accross more of these in our work for our clients I will update this ticket. But I’m good with not solving this one. Thanks for your eyeballs on this and your continued support.

…

On Wed, Sep 13, 2023 at 11:35 AM Jamie Rodriguez ***@***.***> wrote: Ah yeah I came across this from Quickbooks QuickBooks Online isn't designed to work with multiple Accounts Receivable (AR) and Accounts Payable (AP) accounts. QuickBooks Online encourages their customers to merge their accounts payable and receivable accounts into one. It's interesting that QB doesn't block you from creating multiple AR accounts though...I think what we'll do is keep this issue open but mark it as wontdo. That way if this setup isn't completely rare and other users come across this issue, they can chime in here and we can potentially move forward with a solution for this use case. Would the remaining duplicates block you from using the package however? If so, I'd recommend overriding <https://community.fivetran.com/t5/user-group-for-dbt/overriding-fivetran-dbt-package-default-models/td-p/517> the stg_quickbooks__account model to filter out account 501 — Reply to this email directly, view it on GitHub <#104 (comment)>, or unsubscribe <https://github.com/notifications/unsubscribe-auth/A23MTH45RX6TTH6NRJST76TX2H4FNANCNFSM6AAAAAA4T5P3SU> . You are receiving this because you were mentioned.Message ID: ***@***.***>

[J-Sweeny]

Hi Jamie, It looks like we're experiencing this issue across a number of our customers at Runway. We first noticed it in the balance sheets of a handful of customers (they don't balance) and then discovered duplicate debit journal lines in several GL Accounts from the GL Transform tables. In the balance sheet the extra debits build up and create a negative offset that throws off the value for all GL accounts impacted.

It would be massive useful for us to get this solved and we'd be willing to apply engineering resources if that's a bottle neck. The screenshots below are for one impacted account code in the General Ledger table.

[fivetran-joemarkiewicz]

Hi @J-Sweeny thanks for chiming in here!

This original issue is actually an ongoing one we have been seeing in a number of other issues, such as Issue #115, where QuickBooks Online does not provide us with a scalable way via the API to map transactions to the appropriate AR or AP account. I can't 100% confirm that this same issue is the one you are experiencing, but it would be best for us to explore this further and confirm the origin of the issue and assess possible next steps.

In order for us to best support you and triage this issue, I would actually request you open a Fivetran Support Ticket and share the discrepancy you are seeing with the dbt package. Fivetran Support will then be able to get some additional information from you and include my team and I on the ticket in order to securely/best help address the issue you are experiencing.

Let me know if you have any questions, and once you open the support ticket. I can then jump in right away so we can triage and assess this issue ASAP. Thanks!

[robcurtis]

Hi @fivetran-joemarkiewicz - I'm having this issue too. Multiple AR accounts, but there's no indication on invoices or payments which AR account to apply the debit/credit to. There is an ar_account_id column on stg_quickbooks__customer and a deposit_to_account_id on stg_quickbooks__payment (both of which are always empty for all the accounts I'm working with).

Unfortunately, this is really messing up my Balance Sheet outputs when there are Multiple AR accounts. This link has to exist somewhere in Quickbooks Online, or there has to be some workaround. I understand that QBO recommends only one default AR account, but they allow for more than one AR account, and running the Balance Sheet report in their platform returns the right data.

We're trying to migrate off CData's Quickbooks ODBC connector because it's a pain to use and not scalable to whole team, which is why I've been exploring Fivetran, but I am not sure it will be viable if I can't get the books to balance (though I really want it to work).

I know this issue has come up before - has there been any insight from Quickbooks why the API won't return the ar_account_id or deposit_to_account_id? The mapping to AR account must be somewhere in their API if CData is able to make it balance.