Deleted Line Transaction Record Issue

[fraserward]

This issue can arise when an invoice is raised with two or more line transaction and then at a later time one or more line transactions are deleted and then again at an even later time the invoice date and/or due date are changed.

The DWH takes line transaction dates from the invoices table to populate the Invoice Management Fact.

If a CRM front end user was to delete the line transaction then the 'date modified' field would be updated this time (but never again as this record is now flagged as deleted).

Now if a front end user modifies anything else on this invoice (i.e. due date etc) then this update will not be added to the deleted line transaction record as the 'modified date' is static since the record was flagged as deleted.

This will result in 'line items' in the Invoice Management Fact table that relate to the same invoice having different dates for 'invoice raised' and 'invoice due'. When running an invoice report the query is will find multiple dates for the 'invoice raised' and 'invoice due' date. The report can only have one date for each of these fields. Whatever date is obtained first will be the date returned on the report which will likely be incorrect.

Need to discuss best way to resolve this and possible implications of any fix.

[harrisward]

Discussed 19th May:

The solution here is to update all records associated with the invoice to use the latest Invoice dates even if the record has been already deleted

[fraserward]

Theoretical.

• Invoiced raised 10.01.21.
• On 15.01.21 a line item is deleted. All records update (deleted item updates for the last time).
• On 22.01.21 a correction is made, bringing the invoice date forward. (new date corrected to 05.01.21).

All records update to 05.01.21 for that invoice (except the deleted item date which is static on 15.01.21).

The correct date for this record is not the latest invoice date (15.01.21) the correct date is the earlier date (05.01.21).

[harrisward]

We will create a new adjustment transformation for the invoice management fact that will align deleted records data.

we will execute this transformation in the main populateFacts job directly after the populateInvoiceManagementFact transformation

The transformation will be called adjustInvoiceManagementFact

The transformation will load all deleted records and lookup the MAX date of its associated NON DELETED records from the fact table. We will then use this date to update the deleted record date

[fraserward]

updated InvoiceManagementFact transformation, Fact job, Fact DDL, Add adjustInvoiceManagementFact transformation

Pull Request: https://github.com/ivylabs/suitecrm-data-integration/pull/169

[johnM2401]

Hey @harrisward

Looks like the issue has been partly resolved

Looks to be resolved for "Invoice Date" However, the issue still seems to exist for "Due Date"

For example, this shows in Analytics:

Where all Date values are 4th July:

The DB Rows appear as follows:

Where each non-deleted item gets updated. However, the value in the Invoice report appears to be taken from the top-most item, (which is deleted). So, the value is wrong in Analytics.

[fraserward]

This is an issue and I have raised a new ticket for it here.... https://github.com/ivylabs/suitecrm-data-integration/issues/179

[johnM2401]

Looks good, both this and Due Date have been tested as per #179