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]

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