Connector Improvement: Workday Financial Management Supplier Invoice Adjustment table and Supplier Invoice JOIN Yields Too Many Rows - Suggest Using GUID
Answered
The join suggested in Fivetran support ticket #265527 is a many-to-many join. The original supplier_invoice_adjustment table has only about 1,000 records -- we don't want that to balloon into 5.6 million records. We suggest in this feature request for Fivetran to adjust their code so that the original_invoice_id field from supplier_invoice_adjustment pulls the guid values that would make the primary key (ID) field in the SUPPLIER_INVOIICE. (Whereas currently fivetran is pulling something like: SUPPLIER_INVOICE-9-12345
-
Official comment
Hi Bryan,
Do you mind sharing an example with me? I'm not seeing that there are multiple supplier invoices with the same code. Are you removing instances of null to null joining?
Frank
-
I just found another field that we can join on (see highlights in SQL below). This is not intuitive based on what is shown in the ERD, but it works. This ticket can be closed.
SELECT count(*)
FROM shca_source_data.workday_financial_management_shc.supplier_invoice_adjustment adj
INNER JOIN shca_source_data.workday_financial_management_shc.supplier_invoice inv
ON adj.original_invoice_id = inv.supplier_invoice_code
Please sign in to leave a comment.
Comments
2 comments