Linked Transactions Sales Order to Invoice | CLEARIFY

I am trying to build a Power BI Dashboard but more importantly I am trying to figure out the objects and relationships needed to tie Sales Orders to Invoices which could be 1:1 or 1:x

I am reviewing the linked transaction area from the WIKI (https://clearify.com/wiki/view/204/linked-transactions) but there are no clear example showing the relationships and objects needed.

Our apologies for missing this reply earlier.  Probably too late, but this is a good question to be answered.

The problem with this relationship, is that it could be:

• One sales order to one invoice
• One sales order to multiple invoices
• Multiple sales orders to one invoice
• Multiple sales orders to multiple invoices

To further complicate issues, you might have the same item on the sales order (and/or invoice) twice - or more.

QuickBooks does not store any line item links underneath the hood - for any transaction.   However, the Intuit SDK (Software Development Kit) does create a link at the header level; and that is what we have in QQube.

In the Power BI examples, we don't include those out of the box - and for one reason:  If you have multiple links say one invoice, and multiple sales orders, then the original amount on the invoice would be doubled, unless you invoked some sort of distinct clause.

For most users, this is too complicated.

However, for someone as probably knowledgeable as you, you can just create the inner join from the main FACT table (vf_SALESDETAILS) to the vf_SALESLINKEDTXN table via "LinkToTxnID" and "LinkForTxnID" respectively.

In a true data warehouse, all joins are inner joins.  Data on the left is not excluded by any filters on the right, because a data warehouse creates a dummy marker to fill in the holes.

Because of the nature of how users can use QuickBooks, and because of how QB actually stores information underneath the hood, this linkage can be problematic for even QQube users.