How to delete the existing value of date field and update the value using transform script?

[abhi710]

Hi All,

I have a requirement in which the cast(date field) existing value from all record in table must be removed before transforming and update the new value. How to achieve this?

Thanks,

Abi

[Tony Chatfield1]

Hi, unfortunately your requirement is missing clear details.
Is the value the field that is being updated included in your import file? if yes and in the correct format you should be able to simply map the source field to the target field.
If the values exists but in incorrect format you could correct the field value with a transform map script, or in a beforeTransform script.

Or if the value is not included in your import file you could look it up, if it exists elsewhere in the platform, and your import file contains suitable unique identifier.

Or you may be able to create the value in a beforeTransform script IE if you want the value to be the date\time that the transform is run.pt.

Exactly what\how will depend on requirements\details that are not included in your post and you will need to provide clear and specific requirements if you need additional assistance.

[abhi710]

Hi @Tony Chatfield1,

The value is included in same format in import file. Everyday when the upload happens i want to delete the value for that date field for all the records in business application table and update the value only for the application in import file. I have this onbefore Script:

var gr = new GlideRecord('cmdb_ci_business_app');
gr.query();
while (gr.next()) {
gr.u_date.setDisplayValue('');
gr.update();
}

This clears all the value and gets transformed further the field is becoming empty again.

Can this be used in onstart script?

How to achieve the requirement ? 

Thanks,

Abi

[Tony Chatfield1 · Accepted solution]

Hi, I think an onStart script would be the right place for this.
but I would replace gr.u_date.setDisplayValue(''); 
with

gr.u_date = '';

as you are not setting a display value, you are clearing the field.
Also, unless you need cmdb_ci record BR's to run after the field is changed, I would also disable BR functionality with

gr.setWorkflow(false);

[Amit Gujarathi]

HI @abhi710 ,
I trust you are doing great.

you can follow these steps:

1. Identify the target table where the date field exists. Let's assume the table name is "TargetTable" and the date field is "date_field".

2. Create a new Business Rule or Script Include in ServiceNow to perform the required transformation. Let's name it "DateFieldTransformation".

3. Within the Business Rule or Script Include, retrieve all records from the "TargetTable" using a GlideRecord query.

var targetTableGR = new GlideRecord('TargetTable');
targetTableGR.query();

1. Iterate through each record and set the date field value to null.

while (targetTableGR.next()) {
  targetTableGR.date_field = null;
  targetTableGR.update();
}

1. After setting the date field to null for all records, you can then update the date field with the new desired value. This step depends on how you are obtaining the new value, so you would need to modify the code accordingly. Let's assume the new value is stored in a variable called "newDateValue".

while (targetTableGR.next()) {
  targetTableGR.date_field = newDateValue;
  targetTableGR.update();
}

1. Save the Business Rule or Script Include and test it by triggering the transformation process.