Stop the entire transformation if condition is satisified

[MaharshiC]

Hi,

I want to stop the entire transformation if the conditions mentioned in the script is followed. So if there are 10 records and even for 1 record if the condition matches then no record should get added in the target table and the whole transformation should stop and alert should show. I have written the following script and i am getting the info message and going to the match=true   but it not stopping the transformation and records are getting created. This is onBefore transformation script

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {

	// Add your code here
	gs.addInfoMessage("11Entire transformation will be ignored+"+source.sys_import_set);
	var match = false;
	var gr = new GlideRecord("x_iem_tqa_tqa_staging_request_items");
	gr.addQuery("sys_import_set", source.sys_import_set);
	
	gr.query();
	while (gr.next()){
		gs.addInfoMessage("22Entire transformation will be ignored");
		if(source.request_item_type == 'Other' ){
			match = true;
			break;
		}
	}
	
	if(match){
		ignore = true;
		gs.addInfoMessage("43Entire transformation will be ignored");
	}


})(source, map, log, target);

[Kieran Anson]

"Now my complete requirement is basically i need to check if there is any request item type with value as = other and if there is any value matching then stop the entire transformation." From your post - you don't need to loop over each row. You can just glide query to see if the import set has any rows where the type is Other. If you have atleast one row, you set ignore to true to stop processing. No need to access the source variable in your script 

[MaharshiC]

Hi @Kieran Anson ,

Actually there is 2 parts to the requirements and so directly using the other is not possible. is there any way i can access the source fields using onstart script . This is the second part of the requirement - Also I need to check if there is any other value matching the first request item type which in our example is Tag . If there is any other value matching that then also we should stop inserting any record

[Kieran Anson]

Can you explain your full requirement, is quite frustrating trying to provide you with support when not being provided the full details 

[MaharshiC]

Hi @Kieran Anson ,

There are 2 parts of the requirements.

x_iem_tqa_tqa_staging_request_items is extended from the import set row table . Here is the screenshot of a test dataset. So once the data is loaded in the x_iem_tqa_tqa_staging_request_items table I have a transform map to create records in my target table. Now my complete requirement is basically i need to check if there is any request item type with value as = other and if there is any value matching then stop the entire transformation. no rows should get inserted . Also I need to check if there is any other value matching the first request item type which in our example is Tag . If there is any other value matching that then also we should stop inserting any record

[Kieran Anson · Accepted solution]

untested, but the logic I would apply to this scenario 

(function runTransformScript(source, map, log, target /*undefined onStart*/ , import_set) {

    //Check if the imported data has a request_type of Other
	//and abort if so
    var hasOtherTypeGR = getBaseQuery(false);
    hasOtherTypeGR.addQuery('request_item_type', 'Other');
    hasOtherTypeGR.setLimit(1);
    hasOtherTypeGR.query();
    if (hasOtherTypeGR.hasNext()) {
        log.warn('Import Set contains a request item type of Other, aborting')
        ignore = true;
        return;
    }

	//Check if there is more than one request type
	//of the first row
    var importSetFirstRowValue = getBaseQuery(false);
    importSetFirstRowValue.orderBy('sys_import_row');
    importSetFirstRowValue.setLimit(1);
    importSetFirstRowValue.query();
    if (!importSetFirstRowValue.next()) {
        //this shouldn't happen
        log.error('No Rows to process');
        ignore = true;
    }

    var firstRowRequestTypeValue = importSetFirstRowValue.getValue('request_item_type');
    var result = getAggregateByItemType(firstRowRequestTypeValue);

    if (result > 1) {
        log.warn("More than one row found with type of " + firstRowRequestTypeValue);
        ignore = true;
        return count;
    }

	//helper functions

	
    function getBaseQuery(isAgg) {
        var queryMethod = isAgg ? GlideAggregate : GlideRecord;

        var importSetQuery = new queryMethod(import_set.getValue('table_name'));
        importSetRows.addQuery('sys_import_set', import_set.getUniqueValue());
        return importSetQuery;

    }

    function getAggregateByItemType(requestType) {

        var rowAgg = new getBaseQuery(true);
        rowAgg.addQuery('request_item_type', requestType);
        rowAgg.addAggregate('COUNT', 'request_item_type');
        rowAgg.query();
        if (!rowAgg.next())
            return 0;

        return rowAgg.getAggregate('COUNT', 'request_item_type');
    }

})(source, map, log, target, import_set);