In Data Load, Ignore the record update if the invalid data

[Sarah Bouil]

I have a field AQCC Name(u_aqcc_name) and the type of field is List type field(it's reference to 'u_aqcc' table) in Asset table and also the field AQCC Name(u_aqcc_name) will holds one or more AQCC Names data with comma(,) separated(like AQCCTest01, AQCCTest02).

If I load data into Asset table thru Transform Maps, if the AQCC Name exist(ex: AQCCTest01, loading thru excel file) in 'u_aqcc' table so the data(AQCCTest01) should be updated in AQCC Name(u_aqcc_name) field in Asset table .

If the AQCC Name(ex: AQCCTest02) does not exist in 'u_aqcc' table so it should be ignore to update in the same record.

I tried with below code but it is not working.

function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
var isValidId = true;
var isMandatory = false;
if (!source.u_imp_aqcc_name.nil()) {
isMandatory = true;
}
if (isMandatory == false) {
if (source.u_imp_aqcc_name.nil()) {
ignore = true;
status_message = "Record has not been created due to blank value in AQCC Name column";
}
}
if (isMandatory == true) {

var aqccName= source.u_imp_aqcc_name.split(',');
for (var i=0; i<aqccName.length;i++){
isValidId = new VFdpdExport().isActiveValidId('u_aqcc', 'u_active=true^u_displayname=' + aqccName[i].trim());
}
if (isValidId == false) {
ignore = true;
status_message = aqccName + " Record has not been created due to invalid value in AQCC Name column";
}
}
})(source, map, log, target);

I am getting error AQCCTest01, AQCCTest02 Record has not been created due to invalid value in AQCC Name column. But the AQCCTest01 record is valid and it's exist in 'u_aqcc' table. Kindly help what is wrong in my code.

[Ankur Bawiskar]

@Sarah Bouil 

you can check which one is available

share the script include function isActiveValidId

[Sarah Bouil]

Hi Ankur,

Below is the onBefore Transform Script:

-----

function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
var isValidId = true;
var isMandatory = false;
if (!source.u_imp_aqcc_name.nil()) {
isMandatory = true;
}
if (isMandatory == false) {
if (source.u_imp_aqcc_name.nil()) {
ignore = true;
status_message = "Record has not been created due to blank value in AQCC Name column";
}
}
if (isMandatory == true) {

var aqccName= source.u_imp_aqcc_name.split(',');
for (var i=0; i<aqccName.length;i++){
isValidId = new VFdpdExport().isActiveValidId('u_aqcc', 'u_active=true^u_displayname=' + aqccName[i].trim());
}
if (isValidId == false) {
ignore = true;
status_message = aqccName + " Record has not been created due to invalid value in AQCC Name column";
}
}
})(source, map, log, target);

And

Below is the Script Include:(isActiveValidId)

isActiveValidId: function(table, query) {

        var isValidId = new GlideRecord(table);

        isValidId.addEncodedQuery(query);

        isValidId.query();

        if (isValidId.next()) {

            return true;

        }

        return false;

    },

I feel my onBefore Tranasform Script need to be change, please help me on it. Script Include doesn't contain much.

[Ankur Bawiskar]

@Sarah Bouil 

update script include as well

isActiveValidId: function(table, query) {
	var isValidId = new GlideRecord(table);
	isValidId.addEncodedQuery(query);
	isValidId.query();
	return isValidId.hasNext());
},

Transform script

(function runTransformScript(source, map, log, target /*undefined onStart*/ ) {
	var isValidId = true;
	var isMandatory = false;
	if (!source.u_imp_aqcc_name.nil()) {
		isMandatory = true;
	}
	if (isMandatory == false) {
		if (source.u_imp_aqcc_name.nil()) {
			ignore = true;
			status_message = "Record has not been created due to blank value in AQCC Name column";
		}
	}
	if (isMandatory == true) {
		var invalidValues = [];
		var isValid = true;
		var aqccName= source.u_imp_aqcc_name.split(',');
		for (var i=0; i<aqccName.length;i++){
			isValid = new VFdpdExport().isActiveValidId('u_aqcc', 'u_active=true^u_displayname=' + aqccName[i].trim());
			if(isValid == false)
				invalidValues.push(aqccName[i].trim());
		}
		if (isValid == false) {
			ignore = true;
			status_message = inValidId.toString() + " Record has not been created due to invalid value in AQCC Name column";
		}
	}
})(source, map, log, target);

If my response helped please mark it correct and close the thread so that it benefits future readers.

[Sarah Bouil]

Hi Ankur,

I tried but I got below error.

Entire row has been ignored, but AQCCTest01 is exist and it's a valid data. It looks like onBefore transform script is missing something.

if(isValid == false)
				invalidValues.push(aqccName[i].trim());
		}
		if (isValid == false) {
			ignore = true;
			status_message = inValidId.toString() + " Record has not been created due to invalid value in AQCC Name column";
		}

Could you please review once.

[Sarah Bouil]

Also, I noticied if I have a invalid one first(in AQCC Name) and then valid one whole record is create or update with the both values.

But it should be create record with out AQCCTest02.