Bulk Callout

A Bulk Callout validation rule is a SQL query-based validation that is performed once for whatever group of records are being validated, such that:

1. Individual record: the SQL is limited to just that record.
2. Saved set: the SQL is limited to the records in the saved set.
3. 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

Example queries:

2 Stage record must have Manufacturer:

select InternalRecordId,

cast([Manufacturer] as nvarchar(30)) + ' has no reference in Manufacturer repository' as ErrorMessage

from item where Manufacturer is not null

and InternalRecordId not in

(select i.InternalRecordId from Item i inner join Manufacturer b on i.[Manufacturer]=b.[Manufacturer Name])

3 All stage items require same SKU Group:

select m.InternalRecordId, 'SKU Group:'+m.[SKU Group]+' must have the same Taxonomy:'+m.Taxonomy as ErrorMessage

from Item m, (select distinct a.internalRecordId internalId from item a inner join Item b on a.[SKU Group]=b.[SKU Group]

where a.[sku group] != 'DISCONTINUED' and a.Taxonomy != b.Taxonomy) t1 where m.InternalRecordId=t1.InternalId

4 Marketing Ad 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