Workflow wrong counts

[Milan_Vucetic]

Hi all,

we have noticed on workflow level a bad count in outbound transition when we send Email delivery to Prospects. 

This is usually case when personalization returns more values per one user but here we do not have personalization.

On the picture, count of incoming records is 8.538 and count of outbound records is 69.670.080 after delivery node (Later on delivery level the correct number of user is targeted and notified therefore no need for any concern but this issue of wrong count is present only on workflow level).

After analysis, we found that our broad-log table is extended with Account ID field which exist in Recipient table. For real company users this column is populated correctly, but for our prospects this column is set to 0.

An Email node in workflow is generating provided SQL code below which returns square number of records where Account ID is the same.

(ex. If you have in incoming transition 2000 records and all of them are with Account ID = 0 then outbound transition will show 4.000.000 records (2000^2) due to last condition in the query below:

Question is how we can change above SQL code which is under Delivery node and sent towards DB in order to decrease time of join and show correct count?

This should be changeable as broad-log table by default does not contain Account ID field therefore this SQL was changed when the field is introduced.

Regards,

Milan

[Jonathon_wodnicki · Accepted solution]

Hi,

Use a different target mapping for prospect deliveries?

I.e. duplicate the existing mapping, delete the account id map, create a prospects template that uses it.

Thanks,

-Jon

[Milan_Vucetic]

Hi wodnicki,

thanks, that could be one solution.

After detailed testing I found that root cause for this issue is in (collection) link in Recipient table.
Recipient table is extended with the following link:

<element externalJoin="true" label="messages (via AccountID)" name="FK_Recipient_BroadlogRcp"
revExternalJoin="true" revLabel="NONE" revLink="_NONE_" target="nms:broadLogRcp"
type="link" unbound="true">
<join xpath-dst="@accountId" xpath-src="@accountId"/>
</element>

This above give us a collection link to the Broadlog table from Recipient table.
After removing this link, count in the workflow is shown properly. (Condition with account Id is replaced with Recipient Primary key and working perfectly)

This solved my issue.

Regards,

MIlan