OnBefore Transform Script - DONOT Insert/update those consultant record whoseEnd date is before 2020

[rishabh31]

Dear Team,

I am new to transform script thing and have a requirement where we do not insert/update those consultant record whose end date is before year 2020.

Below is the loaded excel data (Source table) screenshot mapped to sys_user table (Target table)

Target Table-

Field Mapping as shown below (coalesce true in User Id <> PIN)

As shown in above screenshot for excel source data the first user record has end date before 2020, so this record should not be inserted even after the PIN (UserID) of this user is not in sys_user records (as coalesce is true for UserID) just because its end date is before 2020.

PIN	Last name	First name	Manager	Supervisor	Type	Start date	End date
PQ42658	Eric	Jack	Alma Joseph	Alva Pennigton		12-10-2006	04-06-2009

So requesting to please help me with the onbefore transform script to check End Date is either before 2020 or after 2020 at source level, if End date has after 2020 then Populate the Source data End date to Target table Termination date field for respective user.

Please help

[Eshwar Reddy · Accepted solution]

Hi @rishabh31 

Create a OnBefore Script
use the below code

var endDate = new GlideDateTime(source.end_date);

var cutoffDate = new GlideDateTime("2020-01-01 00:00:00"); 

if (endDate.getNumericValue() < cutoffDate.getNumericValue())

{

ignore = true;

}

Please mark this response as Correct and Helpful if it helps you can mark more that one reply as accepted solution

Thanks
Esh

[Eshwar Reddy · Accepted solution]

Hi @rishabh31 

Create a OnBefore Script
use the below code

var endDate = new GlideDateTime(source.end_date);

var cutoffDate = new GlideDateTime("2020-01-01 00:00:00"); 

if (endDate.getNumericValue() < cutoffDate.getNumericValue())

{

ignore = true;

}

Please mark this response as Correct and Helpful if it helps you can mark more that one reply as accepted solution

Thanks
Esh

[rishabh31]

Hi @Eshwar Reddy do we need to convert the format for source data end date to YYYY-MM-DD? as it seems currently not matches with target end date.

[Ashish Parab · Accepted solution]

Hello, @rishabh31 

I don’t think we need to change the format to YYYY-MM-DD, as I tried using your provided data and got the expected result. Below is the OnBefore script I used:

(function runTransformScript(source, map, log, target) {

    // Check if 'end_date' is before 2020
    var endDate = new GlideDateTime(source.u_end_date);  // Replace 'end_date' with your actual field name
    var cutoffDate = new GlideDateTime('2020-01-01 00:00:00');

    // If the end date is before 2020, skip the insert/update
    if (endDate.before(cutoffDate)) {
        log.info('Skipping record for consultant with end date before 2020');
        target.setAbortAction(true);  // Prevents insert/update
    }

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

Please mark this response as Correct and Helpful if it helps you can mark more that one reply as accepted solution

Thanks,
Ashish Parab

[rishabh31]

Hi @Ashish Parab , when using 

target.setAbortAction(true);

it triggers error (shown below) during import

Instead used 

ignore = true;

Gives Error free import and provides desired result.

Marking your response as helpful and correct along with Eshwar's response