question how to validate empty data in transform map? in ITSM forum

how to validate empty data in transform map?

jobin1 — Fri, 11 Mar 2022 12:29:44 GMT

Hi All,

I need to validate below excel data while doing transform load (before loading to table)

Validation should be like if the bae64 data is empty then it should not allow to load data.

Re: how to validate empty data in transform map?

jobin1 — Fri, 11 Mar 2022 12:30:37 GMT

@Ankur Bawiskar Any idea?

Re: how to validate empty data in transform map?

Anshu_Anand_ — Fri, 11 Mar 2022 12:34:50 GMT

In transform map before script, you can use

if(source.bae64_data === "")

{

ignore=true; // ignore entire row

}

Re: how to validate empty data in transform map?

Ankur Bawiskar — Fri, 11 Mar 2022 13:48:13 GMT

@jobin

you can use onbefore transform script

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

	// Add your code here
	if(source.u_base64_data.nil())
		ignore = true;

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

Regards
Ankur

Re: how to validate empty data in transform map?

jobin1 — Mon, 14 Mar 2022 06:34:54 GMT

@Ankur Bawiskar Also I need to show an error message in error reason column of staging table. I tried with below code its not working. Please help.

On before transform script

var cat_num = source.u_number;
var cat_item = source.u_item;
var fname = source.u_filename;
Var ritmstate = source.u_state;
var contenttype = source.u_content_type;
var bae64 = source.u_base64_data;

//Source table map - ends here...

var category1,sub_category1,subcategory22,user_id,username,country,company,phone,assignment_group,company_sysid,asigntosysid,stateid,openstateid,rescatgsysid,clscode,urgcyvalue;
var errornote = " ";

//State validation starts......

if (ritmstate != '' && ritmstate != null && ritmstate != undefined) {
var stateval = new GlideRecord('sys_choice');
stateval.addQuery('name', 'sc_req_item');
stateval.addQuery('element', 'state');
stateval.addQuery('label', ritmstate);
stateval.query();
if (stateval.next()) {
stateid = stateval.value;
} else {

stateid = '';
}
}
//State validation ends........

//Error message update in staging table starts
else
{
source.u_error_state = true;

var invaliderror = "";
var mandatoryerror = "";
var invalidmessage = "";
var statemessage = "";
var mandmessage = "";

if(ritmstate == 'Closed Complete' || ritmstate == 'Closed Incomplete' || ritmstate == 'Closed Skipped' || ritmstate == 'Cancelled')
{
statemessage = "RITM cannot be created in " + ritmstate +" State.";
}

if(source.bae64_data == "")

{
statemessage = "RITM cannot be created.Missing Bae64data ";
errornote = statemessage;
source.u_error_reason = errornote;
}

if(source.u_filename === "")

{
statemessage = "RITM cannot be created.Missing file name ";
errornote = statemessage;
source.u_error_reason = errornote;
}
if(source.u_content_type == "")

{
statemessage = "RITM cannot be created.Missing content type ";
errornote = statemessage;
source.u_error_reason = errornote;
}

Re: how to validate empty data in transform map?

Ankur Bawiskar — Mon, 14 Mar 2022 06:39:35 GMT

So what is not working?

if you are using onBefore then it should set the staging table field with the error message

Regards
Ankur

Re: how to validate empty data in transform map?

jobin1 — Mon, 14 Mar 2022 06:43:32 GMT

@Ankur Bawiskar So the code which I posted is corrected you're telling?

Regards,

Jobin

Re: how to validate empty data in transform map?

Ankur Bawiskar — Mon, 14 Mar 2022 07:33:55 GMT

Hi,

your code is already correct.

is it not working?

are you using correct field name for import set?

regards
Ankur

Re: how to validate empty data in transform map?

jobin1 — Mon, 14 Mar 2022 11:22:05 GMT

Yes its correct. I have passed empty bae64 data in excel but it created new record and "error reason" also not updated. Please find the screenshots.

Re: how to validate empty data in transform map?

Ankur Bawiskar — Mon, 14 Mar 2022 11:30:15 GMT

Hi,

inside each if statement you should be adding ignore=true;

Regards
Ankur

Re: how to validate empty data in transform map?

jobin1 — Mon, 14 Mar 2022 11:35:51 GMT

@Ankur Bawiskar I tried with bae64 -ignore true.it not worked let me try to give for all then.

Is the below format is fine?

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

var cat_num = source.u_number;
var cat_item = source.u_item;
var fname = source.u_filename;
var ritmstate = source.u_state;
var contenttype = source.u_content_type;
var bae64 = source.u_base64_data;

//Source table map - ends here...

//Error message update in staging table starts
else
{
//source.u_error_state = true;

var invaliderror = "";
var mandatoryerror = "";
var invalidmessage = "";
var statemessage = "";
var mandmessage = "";

if(ritmstate == 'Closed Complete' || ritmstate == 'Closed Incomplete' || ritmstate == 'Closed Skipped' || ritmstate == 'Cancelled')
{
ignore = true;
statemessage = "RITM cannot be created in " + ritmstate +" State.";
}

if(source.u_base64_data == "")

{
ignore = true;
statemessage = "RITM cannot be created.Missing Bae64data ";
errornote = statemessage;
source.u_error_reason = errornote;
}

if(source.u_filename === "")

{
ignore = true;
statemessage = "RITM cannot be created.Missing file name ";
errornote = statemessage;
source.u_error_reason = errornote;
}
if(source.u_content_type == "")

{
ignore = true;
statemessage = "RITM cannot be created.Missing content type ";
errornote = statemessage;
source.u_error_reason = errornote;
}
}
})(source, map, log, target);

Re: how to validate empty data in transform map?

Ankur Bawiskar — Mon, 14 Mar 2022 11:55:08 GMT

yes looks fine.

Re: how to validate empty data in transform map?

jobin1 — Mon, 14 Mar 2022 12:22:39 GMT

@Ankur Bawiskar No luck. Still same

Re: how to validate empty data in transform map?

Ankur Bawiskar — Mon, 14 Mar 2022 13:17:42 GMT

did you check it went inside the if?

Re: how to validate empty data in transform map?

jobin1 — Mon, 14 Mar 2022 13:43:01 GMT

@Ankur Bawiskar No not went inside I guess

Re: how to validate empty data in transform map?

Ankur Bawiskar — Mon, 14 Mar 2022 13:48:42 GMT

alert is client side and your script is server side

so please use gs.info() to check logs

Regards
Ankur

Re: how to validate empty data in transform map?

jobin1 — Mon, 14 Mar 2022 13:54:09 GMT

@Ankur Bawiskar gs.info() also used but not getting inside

Re: how to validate empty data in transform map?

Ankur Bawiskar — Mon, 14 Mar 2022 14:17:02 GMT

the logic is correct

try to debug further

Re: how to validate empty data in transform map?

jobin1 — Tue, 15 Mar 2022 07:39:15 GMT

@Ankur Bawiskar Below code is working and I have removed few validation now bae64 and file name validation only in place.

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

var cat_num = source.u_number;
var cat_item = source.u_item;
var fname = source.u_filename;
var ritmstate = source.u_state;
var contenttype = source.u_content_type;
var bae64 = source.u_base64_data;

//Source table map - ends here...

var category1,sub_category1,subcategory22,user_id,username,country,company,phone,assignment_group,company_sysid,asigntosysid,stateid,openstateid,rescatgsysid,clscode,urgcyvalue;
var errornote = " ";

var invaliderror = "";
var mandatoryerror = "";
var invalidmessage = "";
var statemessage = "";
var mandmessage = "";

if(source.u_base64_data == "")

{
ignore = true;

statemessage = "Missing Bae64data ";
errornote = statemessage;
source.u_error_reason = errornote;
}

if(source.u_file_name == "")

{
ignore = true;
statemessage = "Missing file name ";
errornote = statemessage;
source.u_error_reason = errornote;
}
//}
})(source, map, log, target);

Re: how to validate empty data in transform map?

Ankur Bawiskar — Tue, 15 Mar 2022 09:35:30 GMT

@jobin

Glad to know that it worked.

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

Regards
Ankur