Need Help - Date validation in import API onbefore Transform script | Service Portal

[Maulik176]

Hello Everyone,

I am facing an issue with Import Sets and Transform Maps related to validating date ranges. I am importing an Excel file into a custom table that contains two date columns called Cost Start Date and Cost End Date. The table also has a reference field to the ast_contract table, which contains Contract Start Date and Contract End Date.

My basic import pipeline (Data Source, Import Set, Transform Map) is working, and the cost dates are being inserted correctly through field mapping.

The problem is that I need to validate that the Cost Start Date and Cost End Date always fall within the selected Contract's Start Date and End Date. If the cost dates fall outside the contract period, the record should not be imported.

I found a similar Community post suggesting using an onBefore Transform Script, but it did not work for me:
https://www.servicenow.com/community/developer-forum/date-validation-in-import-api-onbefore-transfor...

The challenge is that I also have a REST API import triggered from a Service Portal widget using a Script Include. For this reason, I need a consistent way to enforce the validation rules so that

Invalid records are not inserted into the target table

The import stops or skips invalid rows cleanly 

A clear error message can be shown to the end user, especially when the upload happens through the Service Portal

Please help
Maulik Ranadive

[Its_Azar · Accepted solution]

Hi there @Maulik176 

I think you’re on the right track doing this in an onBefore Transform Script – that’s the one place that will fire for both the Excel upload and the REST import from your portal, so you don’t need two validation paths. In that script you just need to: 1) look up the selected contract, 2) compare dates as GlideDateTime, and 3) skip the row when it’s invalid. Something like:

    if (!target.u_contract)    // your ref to ast_contract
        return;

    var c = new GlideRecord('ast_contract');
    if (!c.get(target.u_contract))
        return;

    var costStart = new GlideDateTime(target.u_cost_start_date);
    var costEnd   = new GlideDateTime(target.u_cost_end_date);
    var contStart = new GlideDateTime(c.u_start_date);
    var contEnd   = new GlideDateTime(c.u_end_date);

    if (costStart.before(contStart) || costEnd.after(contEnd)) {
        ignore = true;   // skip this row
        map.addErrorMessage("Row skipped: cost dates must be within contract " + c.number);
    }

[Maulik176]

Hi @Its_Azar,

Your suggestion helped a lot – the onBefore Transform Script with ignore = true is working and rows that fall outside the contract date range are now being skipped. So that part is solved, thank you.

I still have one open problem: I want to show a clear message back to the user in the Service Portal when some rows are skipped.

I tried using map.addErrorMessage("Row skipped...") in the onBefore script, but that throws this error in the Import Logs:

TypeError: Cannot find function addErrorMessage in object com.glide.db.impex.transformer.GlideTransformMap@67cddfff. (sys_transform_script.f958f67f9365b2103d10f7ea3603d64d.script; line 25)

So it looks like map.addErrorMessage() is not available on that object.

Very briefly, here is how my service portal flow works (without exposing all the internal helper code):

From a Service Portal widget, the user uploads an Excel file.

• The widget calls a Script Include function processRateCardDataImportUsingAPI(file_name, base64Data, cntr_id, gr_req_id). the contract id meaning reference ast_contract record. 
• That Script Include writes the uploaded file as a sys_attachment on a custom “rate card import request” record.
• Then it calls a helper class (AVN_RESTHelper) which sends a REST call to the instance’s /sys_import.do?sysparm_import_set_tablename=x_aaven_avclm_rate_card_staging&sysparm_transform_after_load=true URL using sn_ws.RESTMessageV2, attaching the file as multipart.
• The Import Set is loaded and the Transform Map runs with the onBefore script you suggested, which skips invalid rows using ignore = true.
• In my Script Include I only check the HTTP status code (200/201 = success) and return a boolean isSuccessful back to the client.

What I would like to do is:

• Detect that some rows were skipped because of the date range validation, and
• Return that information back to the Service Portal so I can show an spModal / alert saying something like: “Import completed, but X rows were skipped because their cost dates were outside the contract period.”

Could you please advise what the best practice is in this scenario?

For example:

• Is there a supported way from the Transform Script to record a message or summary (maybe in sys_import_log or sys_import_set_run) that I can later read from my Script Include after the REST call finishes?
• Or is there a way for the transform to influence the response body from /sys_import.do so that I can parse it in my REST helper and pass the message back to the Portal?
• Right now I only have a simple success/failure flag based on the HTTP code, but I would really like to surface “partial success with skipped rows” to the end user.

Any guidance or pattern you recommend for sending this kind of validation feedback back to the client would be greatly appreciated.