Question:
How to Query Integration Status 2 Records Based on Assignment Types in Veeva Align?
Answer:
Using the following VQL where clauses, the IS2 records can be extracted based on the following assignment types:
Be sure to include the CRM Record ID field in the result.
Territory Related IS2 records:
account__aln = null and territory__aln != null and roster_member__aln = null and product__aln = null
Roster Member Territory Related IS2 records:
account__aln = null and territory__aln != null and roster_member__aln != null and product__aln = null
Product Territory Related IS2 records:
account__aln = null and territory__aln != null and roster_member__aln != null and product__aln != null
Account Territory Related IS2 records:
account__aln != null and territory__aln != null and roster_member__aln = null and product__aln = null
Queries to extract all IS2 records which are relevant in case of duplicate CRM Record ID field values:
Territory Related IS2 record whose CRM DeletedDatetime field is null:
account__aln = null and territory__aln != null and roster_member__aln = null and product__aln = null and crm_delete_datetime__aln = null
Roster Member Territory Related IS2 records whose CRM DeletedDatetime is null:
account__aln = null and territory__aln != null and roster_member__aln != null and product__aln = null and crm_delete_datetime__aln = null
Product Territory Related IS2 records whose CRM DeletedDatetime is null:
account__aln = null and territory__aln != null and roster_member__aln != null and product__aln != null and crm_delete_datetime__aln = null
Account Territory Related IS2 records whose CRM DeletedDatetime is null:
account__aln != null and territory__aln != null and roster_member__aln = null and product__aln = null and crm_delete_datetime__aln = null
In the event further filtering is necessary and null CRM Record ID IS2 records are not relevant:
Territory Related IS2 record whose CRM DeletedDatetime field is null:
account__aln = null and territory__aln != null and roster_member__aln = null and product__aln = null and crm_delete_datetime__aln = null and crm_record_id__aln != null
Roster Member Territory Related IS2 records whose CRM DeletedDatetime is null:
account__aln = null and territory__aln != null and roster_member__aln != null and product__aln = null and crm_delete_datetime__aln = null and crm_record_id__aln != null
Product Territory Related IS2 records whose CRM DeletedDatetime is null:
account__aln = null and territory__aln != null and roster_member__aln != null and product__aln != null and crm_delete_datetime__aln = null and crm_record_id__aln != null
Account Territory Related IS2 records whose CRM DeletedDatetime is null:
account__aln != null and territory__aln != null and roster_member__aln = null and product__aln = null and crm_delete_datetime__aln = null and crm_record_id__aln != null
Related Documentation:
Align Help Documentation:
Knowledge Base: