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.

[Niklas Peterson]

Hi @Sarah Bouil, 

This piece of code

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());
}
checks each value in aqccName varible separatly and updates isValidId variable everytime. The second value will overwrite the first etc. So if AQCCTest01, AQCCTest02 is the order then isValidId will be based on AQCCTest02.

And accordingly the next piece of code

if (isValidId == false) {
ignore = true;
status_message = aqccName + " Record has not been created due to invalid value in AQCC Name column";
}

Will set it ignore = true.

If you want to ignore only if all of them are false then you should not allow isValidId to be overwritten with false if it is already true.

Regards,
Niklas

[Sarah Bouil]

I want to ignore the update if the AQCC Name doesn't exist only in u_aqcc table while load the data thru excel. It will create a ghost record in u_aqcc table if you are trying to load AQCC Name which doesn't exist it.

Please help me with code changes, if possible.

[Ankur Bawiskar]

@Sarah Bouil 

So even if 1 value from incoming source is not present in the table you want to ignore the entire row?

[Sarah Bouil]

Hi Ankur,

Thank you for your reply. I have to update/insert entire row with available data. In my case I am sending  AQCCTest01 , AQCCTest02 from excel. So AQCCTest01 is present so it should insert a record into target table with 101 US AQCCTest01 True. AQCCTest02 doesn't exist so should not update, AQCCTest02 should be ignore alone.

Kindly help me with code changes from above code what was wrong with it.