A Bulk Callout validation rule is a SQL query-based validation that is performed once for the group of records being validated, such as:
Individual record: the SQL is limited to just that record.
Saved set: the SQL is limited to the records in the saved set.
Full repository: the SQL query is run against the entire repository.
In each case, the results of the query are the records that violate the rule, which are listed in a temporary table. They are placed in the table so that when each record in the group is being validated, the temporary table is consulted instead of running the SQL again. When each record is validated for its other rules, the error table is consulted for any bulk callout violations. If there are any, their messages are added to the list of errors for that particular attribute.
The basic interface is that the callout needs to return one row for each record that has an error. The first column of the row is the internal ID of the record and the second column is the error message. The query itself needs to be formulated to find and return the offending rows.
Each query should be wrapped by the following outer query:
SELECT InternalRecordId, ErrorMessage
From (
<<… Actual query here >>
) as T1 WHERE 1=1
Note that EnterWorks references repositories by using their name in the default language, which is English. Also, the column names of the error table cannot be changed.
In the examples given below:
The repository named “Item_Staging” contains columns whose restricted names are “Manufacturer”, “Manufacturer_Name”, and “SKU_Group”.
The repository named “PIM_MarketingAd_Staging” has columns whose restricted names are “Marketing_Ad_ID”, “Marketing_Ad_ID”, and “Image_Context”.
Example queries:
Item_Staging records must have Manufacturer:
select InternalRecordId,
cast(Manufacturer as nvarchar(30)) + ' has no reference in Manufacturer repository' as ErrorMessage
from Item_Staging where Manufacturer is not null
and InternalRecordId not in
(select i.InternalRecordId from Item_Staging i inner join Manufacturer b on i.Manufacturer=b.Manufacturer_Name)
All Item_Staging records require the same SKU Group:
select m.InternalRecordId, 'SKU Group:'+m.SKU_Group+' must have the same Taxonomy:'+m.Taxonomy as ErrorMessage
from Item_Staging m, (select distinct a.InternalRecordId internalId from Item_Staging a inner join Item_Staging b on a.SKU_Group=b.SKU_Group
where a.SKU_Group != 'DISCONTINUED' and a.Taxonomy != b.Taxonomy) t1 where m.InternalRecordId=t1.InternalId
Records in PIM_MarketingAd_Staging must have a Main image:
select InternalRecordId,
'Marketing Ad: ' + Marketing_Ad_ID + ' has no Main image' as ErrorMessage
from PIM_MarketingAd_Staging
where InternalRecordId not in
(select ma.InternalRecordId from PIM_MarketingAd_Staging ma inner join DAMLink dl on ma.Marketing_Ad_ID=dl.PK1
where dl.Image_Context = 'Main')
If a bulk callout validation rule is to be conditionally applied, the conditions for that application must be encoded in the SQL – the rule conditions will be ignored. This is because those conditions are based on the current record, but the bulk callout must validate all records in the repository or saved set.