[Bug] Missing Account IDs for multiple transactions

[JordanWRender]

Is there an existing issue for this?

• I have searched the existing issues

Describe the issue

We (myself and @mikerenderco) have 2 transactions in our gl that do not have associated account_id's. We are seeing the debit transactions correctly but the credit transactions for them are not coming in with account information which is causing our financials to not balance properly.

Here is an image of how the records show up in our general_ledger. These transactions should be associated with the account_ids in the following image (this comes from the invoice_line table):

Relevant error log or model output

No response

Expected behavior

We expect for all of the posting transactions to have an associated account_id (and relative account information) in the general_ledger. These transactions should have either a 43 or 48 for the account_id.

dbt Project configurations

No specific configurations outside of the standard package

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.7 (latest)

Additional Context

No response

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-catfritz]

Hi @JordanWRender another user had a similar issue in this thread. Their findings (this comment here) was that the value of the associated item_expense_account_id had been changed after the transaction, causing the inconsistency. I believe they were able to verify this in quickbooks. Would you be able to check if the same thing is happening with your quickbooks?

[JordanWRender]

Hi @fivetran-catfritz . We actually aren't seeing that in our case here. The transactions have actually never been edited (they are also fairly old transactions). We are just seeing nulls from them as opposed to the expected account_ids. We have other transactions that hit the same accounts and are showing up normal as well.

[fivetran-catfritz]

Hi @JordanWRender thanks for the additional information! In this case it would be helpful to look at this issue live to see if we can trace where the nulls are coming from. Would you be open to a call? If so, feel free to book time via my calendar!

[fivetran-catfritz]

Hi @JordanWRender Happy New Year! Just touching base on this--I will be out of the office in the coming days, but if you wish to schedule a call, my calendar is udpated with my availability.

[fivetran-catfritz]

Hi @JordanWRender ahead of our call I took a another look at this issue. I think I see what we need to update, so I created a test branch. You can install it using the code snippet below in place of your current quickbooks code in your packages.yml. We can try this out live during the call since I know this is last-minute.

packages:
  - git: https://github.com/fivetran/dbt_quickbooks.git
    revision: bug/sales-item-account-id
    warn-unpinned: false

[fivetran-catfritz]

Hi @JordanWRender Thanks again for walking us through this issue with us today!

Notes from the call:

• The issue appears to be in lines 114 and/or 121 in int_quickbooks__invoice_double_entry.sql
• We will need to add invoice_lines.sales_item_account_id to the coalesce statement that determines the associated account_id.
• We will also need to do a little more research to make sure we update the correct lines. My test branch was for just testing, but did produce the correct results

[JordanWRender]

Hi @fivetran-catfritz, I was wondering if you were able to push this into a new version or if we still need to use the custom code that you provided during our call?

[fivetran-catfritz]

Hi @JordanWRender we haven't released the update just yet, though I anticipate it will be in the next week or so. I'll also reply here when we do release the update. Has the test branch been working out well for you?

[JordanWRender]

@fivetran-catfritz the fix seems to be working well. It solved the issue and nothing new has come up since!

[fivetran-catfritz]

Hi @JordanWRender thank you for confirming! We have released the latest updates today. It should get picked up the next time you run dbt deps using the below range in your packages.yml:

packages:
  - package: fivetran/quickbooks
    version: [">=0.12.0", "<0.13.0"]

I have closed the issue, but feel free to post back here with any comments.