Stop the entire transformation if condition is satisified

MaharshiC · ‎02-19-2025

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 · ‎02-21-2025

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);

View solution in original post

MaharshiC · ‎02-19-2025

Hi,

I changed the script to this and OnStart but now it is not identifying any columns in source like even if there are records matching the condition it is not going inside. It is executing till this - gs.addInfoMessage("22Entire transformation will be ignored");

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

	// Add your code here
	gs.addInfoMessage("11Entire transformation will be ignored+"+import_set.getUniqueValue());
	var match = false;
	var gr = new GlideRecord("x_iem_tqa_tqa_staging_request_items");
	gr.addQuery("sys_import_set", import_set.getUniqueValue());
	
	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;
		log.info("Entire transformation will be ignored");
		gs.addInfoMessage("43Entire transformation will be ignored");
	}


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

Kieran Anson · ‎02-19-2025

Is x_iem_tqa_tqa_staging_request_items an extension of import set row? Is it the import set table you've created?

What are you wanting to achieve? Explain clearly and we can offer you a solution

MaharshiC · ‎02-19-2025

Hi,

Yes 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 · ‎02-19-2025

You don't need to to a while loop, as you're just wanting to check if any row has a column of other. Instead add

.addQuery('request_item_type','Other');

.setLimit(1)

Then you can use

if(gr.hasNext())

ignore = true;

MaharshiC · ‎02-19-2025

Hi,

Then this part I will not be able to do right as I need to read the first data - 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