Vault CRM integration
The following enhancement is available for customers who integrate Network with Vault CRM.
Vault CRM Bridge record counts
Network Administrators can now see the number of unchanged records that were upserted to Vault CRM in a bridge job. The counts for records added and updated have also been enhanced so they more accurately reflect the data.
This enhancement is enabled by default.
Job details
After a Vault CRM Bridge job runs, the Bridge Summary on the Job Details page displays a count of records that were added, updated, and had errors.
The Unchanged column is added to the table.
-
Adds - Upserted records were created in Vault CRM.
-
Updates - Upserted records were updated in Vault CRM.
-
Errors - Records failed to be upserted to Vault CRM.
Click Download Error Report Log for details.
-
Unchanged - Records were upserted to Vault CRM but did not contain changes.
Report on upserted records
Advanced reporting users can report on the data from the Bridge Summary. In the SQL Query Editor (Reports), the Job Stats table is updated to include unchanged records.
Example query
Use this query to understand the impact of a bridge job.
SELECT
job.job_id,
job.job_type,
job.subscription,
RIGHT( job.subscription, 2 ) AS "country",
job.job_system,
job.status,
job.start_time,
job_stats_summary. "hco.adds",
job_stats_summary. "hco.updates",
job_stats_summary. "hco.unchanged",
job_stats_summary. "hco.errors",
job_stats_summary. "hcp.adds",
job_stats_summary. "hcp.updates",
job_stats_summary. "hcp.unchanged",
job_stats_summary. "hcp.errors",
job_stats_summary. "address.adds",
job_stats_summary. "address.updates",
job_stats_summary. "address.unchanged",
job_stats_summary. "address.errors",
job_stats_summary. "parenthco.adds",
job_stats_summary. "parenthco.updates",
job_stats_summary. "parenthco.unchanged",
job_stats_summary. "parenthco.errors"
FROM
job LEFT JOIN (
-- This optimized subquery now also pivots the error metrics
SELECT
job_id,
-- HCO
SUM (
CASE
WHEN metric = 'hco.adds'
THEN counter
ELSE 0
END
) AS "hco.adds",
SUM (
CASE
WHEN metric = 'hco.updates'
THEN counter
ELSE 0
END
) AS "hco.updates",
SUM (
CASE
WHEN metric = 'hco.unchanged'
THEN counter
ELSE 0
END
) AS "hco.unchanged",
SUM (
CASE
WHEN metric = 'hco.errors'
THEN counter
ELSE 0
END
) AS "hco.errors",
-- HCP
SUM (
CASE
WHEN metric = 'hcp.adds'
THEN counter
ELSE 0
END
) AS "hcp.adds",
SUM (
CASE
WHEN metric = 'hcp.updates'
THEN counter
ELSE 0
END
) AS "hcp.updates",
SUM (
CASE
WHEN metric = 'hcp.unchanged'
THEN counter
ELSE 0
END
) AS "hcp.unchanged",
SUM (
CASE
WHEN metric = 'hcp.errors'
THEN counter
ELSE 0
END
) AS "hcp.errors",
-- Address
SUM (
CASE
WHEN metric = 'address.adds'
THEN counter
ELSE 0
END
) AS "address.adds",
SUM (
CASE
WHEN metric = 'address.updates'
THEN counter
ELSE 0
END
) AS "address.updates",
SUM (
CASE
WHEN metric = 'address.unchanged'
THEN counter
ELSE 0
END
) AS "address.unchanged",
SUM (
CASE
WHEN metric = 'address.errors'
THEN counter
ELSE 0
END
) AS "address.errors",
-- ParentHCO
SUM (
CASE
WHEN metric = 'parenthco.adds'
THEN counter
ELSE 0
END
) AS "parenthco.adds",
SUM (
CASE
WHEN metric = 'parenthco.updates'
THEN counter
ELSE 0
END
) AS "parenthco.updates",
SUM (
CASE
WHEN metric = 'parenthco.unchanged'
THEN counter
ELSE 0
END
) AS "parenthco.unchanged",
SUM (
CASE
WHEN metric = 'parenthco.errors'
THEN counter
ELSE 0
END
) AS "parenthco.errors"
FROM
job_stats
GROUP BY
job_id
) AS job_stats_summary
ON job.job_id = job_stats_summary.job_id
WHERE
job.job_type = 'bridge'
AND datediff (
'hours',
job.end_time,
getdate ()) < 72
Example results
