Xero Connector - Linking Journals to Contacts

[m_wretham]

I'm currently working with the Xero ERD Xero ERD - Google Slides to try understand the 'contact' that is attached to a journal entry for amounts payable, in order to build a table through Fivetran of incoming expenses filtered by type.

Looking at the ERD, I cannot see any connection between the JOURNAL_ID and the CONTACT_ID related to those journals; nor can I find anywhere to run a listing of bills.

We're able to create relationships between JOURNAL_LINE_HAS_TRACKING_CATEGORY and JOURNAL_LINE_HAS_TRACKING_CATEGORY and JOURNAL_LINE to pull this into relevant amount groups by using ACCOUNT_CODE and ACCOUNT_NAME; but we can't seem to link the journals to a contact to show the totals by relevant supplier.

Does anybody know how to create the relationship between JOURNAL_LINE and CONTACT? Can somebody explain the table linkages to result in this link.

[SVijay]

Hey @m_wretham, there doesn't seem to be a direct relationship between the two tables, but you can probably use the ACCOUNT table as a buffer between the two.

You can probably filter JOURNAL_LINE by account type and join it with ACCOUNT on account_id

You can then identify the contacts via account code / transaction description (from JOURNAL_LINE)

You can then filter the joined data based on expense type (categories within the account code or description) and aggregate the data (sum amounts) for each expense type.

Hope this helps.

Thanks

[Toby-Metcalf]

Thank you for the question @m_wretham and I hope @SVijay 's response is helpuful.
If you have additional questions, please ask via a comment below. If you are all set, please accept his post as a solution; doing so makes it easy for all Community members to find the answers.

Best,
Toby

[DerekMoore · Accepted solution]

The SQL for this is nicely covered in the Fivetran DBT Xero Model The code for the general ledger shows that the contact id can be obtained from the invoices/credit_notes/bank_transactions table depending on the `SOURCE_TYPE` set on the `JOURNAL` table. 

[Toby-Metcalf]

Thank you for this contribution @DerekMoore - thank you @m_wretham for highlighting the answer.
Cheers!

Toby