Get rows from the transform and print to a JSON string

[Evren Yamin]

Hello,

I currently have a transform map that I used to load data in ServiceNow.  I now have a requirement get the rows of the loaded data and convert into a JSON string so I can store that into a field in ServiceNow.

This is the sample data:

number	short description	state	created
INC1089756	sample record	new	4/24/2024

I want to convert this to a JSON string that looks like this one:

{number: "INC1089756", short_description: "sample record", state: "state", created: "4/24/2024"}

And save the JSON string to a new field. Is this possible? 

Appreciate all the help.

[Deepak Shaerma]

Hi  @Evren Yamin 
yes you can convert your loaded data into JSON string and can store it in a custom field. There are different approaches, Create a business rule 
Table : Incident (choose your correct table)
When to run: After Insert and Update
Script: 

var data = {
        number: current.getValue('number'),
        short_description: current.getValue('short_description'), // Use internal field names
        state: current.getValue('state'),
        created: current.getValue('sys_created_on') // Adjusted to system field
    };

    // Convert object to JSON string
    var jsonString = JSON.stringify(data);

    // Update your custom field with the JSON string
    current.u_json_data = jsonString; // Replace u_json_data with your field name
    current.setWorkflow(false); // Avoids recursion and additional business rule triggers
    current.update();

Please Mark this Helpful and Accepted Solution. If this Helps you to understand. This will help both the community and me..
- Keep Learning ‌‌
Thanks & Regards 
Deepak Sharma 

[Evren Yamin]

Hello @Deepak Shaerma, 

Thanks so much for your response, can I do this as part of the transform? I was hoping I can get a result like this:

This is the sample excel data:

number	short description	state	created
INC1089756	sample record	new	4/24/2024
INC1089758	sample record2	in progress	4/23/2024

after transformation it will look like this on the target (incident) table

number	short description	state	created	json string
INC1089756	sample record	new	4/24/2024	{number: "INC1089756", short_description: "sample record", state: "state", created: "4/24/2024"}
INC1089758	sample record2	in progress	4/23/2024	{number: "INC1089758", short_description: "sample record2", state: "in progress", created: "4/23/2024"}

[Karunakaran]

if it is outside the instance you can use Table APIs. Please let me know your exact requirement.

[Evren Yamin]

Hello @Karunakaran, thanks so much for your response.

I was hoping I can get a result like this:

This is the sample excel data:

number	short description	state	created
INC1089756	sample record	new	4/24/2024
INC1089758	sample record2	in progress	4/23/2024

after transformation it will look like this on the target (incident) table

number	short description	state	created	json string
INC1089756	sample record	new	4/24/2024	{number: "INC1089756", short_description: "sample record", state: "state", created: "4/24/2024"}
INC1089758	sample record2	in progress	4/23/2024	{number: "INC1089758", short_description: "sample record2", state: "in progress", created: "4/23/2024"}