topic JavaScript Connector handling null values in Workato Pros Discussion Board

JavaScript Connector handling null values

jharrison — Wed, 08 Mar 2023 15:59:50 GMT

Has anyone had luck handling null values in a JavaScript Connector? I have tried all these below but the error remains "Cannot read properties of null (reading 'regulation')"

regulation = arrayValue.regulation
regulation = arrayValue.regulation || 0
regulation = arrayValue[0].regulation || 0
if(arrayValue.regulation === null){regulation = "null"} else {regulation = arrayValue.regulation}
if(arrayValue.regulation != null){regulation = arrayValue.regulation} else {regulation = "null"}

Re: JavaScript Connector handling null values

gary1 — Wed, 08 Mar 2023 17:45:28 GMT

Can you share more of the script? After you define "regulation", what are you doing with it?

Re: JavaScript Connector handling null values

jharrison — Wed, 08 Mar 2023 17:54:13 GMT

https://www.workato.com/product-hub/semi-structured-data-tamed-with-javascript/

I am following this article on Semi-structured data tamed with JavaScript

Below is the JS note my attempts are all commented with errors noted at end. If I comment out the nested array part if works great.

const createRecord = (employee) => {
row = {}

// Map top level fields - simple
row.FirstName = employee.FirstName
row.LastName = employee.LastName
row.EmployeeId = employee.EmployeeId

// Map name - nested array
const Status = employee.Status
row.regulation = employee.Status.Regulation
//let reg = "reg"
//row.regulation = Status.regulation
//row.reg = Status.regulation || "null" //Cannot read properties of null (reading 'regulation')
//regulation = if(Status.regulation === null){"null"}else {Status.regulation}
//row.regulation = Status[0].regulation || 0
//if(Status.regulation === null){row.regulation = "null"} else {row.regulation = Status.regulation}
//if(Status.regulation !== null){row.regulation = "null"} else {row.regulation = Status.regulation}
//if(Status.regulation.length === 0){row.regulation = "null"} else {row.regulation = Status.regulation}
//if(Status[0].length === 0){row.regulation = "null"} else {row.regulation = Status.regulation} Cannot read properties of undefined (reading 'length')

return row
}

exports.main = ({ input_json }) => {
const input = JSON.parse(input_json)
const rows = []
input.forEach(employee => {
rows.push(createRecord(employee))
})
return {rows: rows}
}

Re: JavaScript Connector handling null values

gary1 — Wed, 08 Mar 2023 18:23:30 GMT

Is this an accurate representation of input_json? Not sure if Status is an object or an array.

[{
  "FirstName": "Spider",
  "LastName": "Man",
  "EmployeeId": "123",
  "Status": {
    "Regulation": null
  }
}]

Re: JavaScript Connector handling null values

jharrison — Wed, 08 Mar 2023 19:07:59 GMT

Here is the Schema

[
{
"LastName": "",
"FirstName": "",
"Status": [
{
"Regulation": ""
}
]
}
]

Re: JavaScript Connector handling null values

jharrison — Wed, 08 Mar 2023 19:09:53 GMT

Yes, minus a couple square brackets [].

[{
"LastName": "",
"FirstName": "",
"Message": null,
"Status": [
{
"Regulation": ""
}
]
}]

Re: JavaScript Connector handling null values

gary1 — Wed, 08 Mar 2023 19:21:33 GMT

Using that exact array as input, this script works fine on my end (I really didn't make any changes).

Is Regulation always present in the first index of the Status array? If not, then you may need to loop through Status to find an object with Regulation.

Either way though, I'm not getting any errors when the value is present, blank, or null.

const createRecord = (employee) => {
row = {}
// Map top level fields - simple
row.FirstName = employee.FirstName
row.LastName = employee.LastName
row.EmployeeId = employee.EmployeeId
// Map name - nested array
row.regulation = employee.Status[0].Regulation
return row
}

exports.main = ({input_json}) => {
const input = JSON.parse(input_json)
const rows = []
input.forEach(employee => {
rows.push(createRecord(employee))
})
return {
rows: rows
}
}

1. Input with blanks

[{
"LastName": "",
"FirstName": "",
"Status": [{
"Regulation": ""
}]
}]

Output:

2. Input with nulls:

[{
"LastName": null,
"FirstName": null,
"Status": [{
"Regulation": null
}]
}]

Output:

3. Input with values:

[{
"FirstName": "Spider",
"LastName": "Man",
"EmployeeId": "123",
"Status": [{
"Regulation": test
}]
}]

Output:

4. Input with an empty object leading the Status array:

[{
"FirstName": "Spider",
"LastName": "Man",
"EmployeeId": "123",
"Status": [{},{
"Regulation": "test"
}]
}]

Output:

Re: JavaScript Connector handling null values

jharrison — Wed, 08 Mar 2023 20:23:13 GMT

Hi Gary1,

Well that is annoying!

I tried the index suggestion and it gave me a Regulation undefined error. So at least it's not a null.

I've given up for now.

Thanks for your assistance

Jim

Re: JavaScript Connector handling null values

gary1 — Thu, 09 Mar 2023 02:32:57 GMT

Very weird! Even if you copy/paste my exact script it still throws the error? If that's the case, there must be something up with the input.

Re: JavaScript Connector handling null values

jharrison — Thu, 09 Mar 2023 20:12:56 GMT

Hi Gary1,

For fun I tried the copy and paste and the error changed to "Cannot read properties of undefined (reading 'Regulation')".

I think the input is the problem as well. The input JSON has forward slashes in front of all the quotes. I am attempting to get them removed.

I resorted to a different solution, I nested two for loops and got that to work. I feel like the JS may be more efficient over the for loops, which is why I wanted to try to get it to work.

If I get the JSON cleaned up, I'll try this recipe again and see if the JS solution works. I have it saved.

Regards,

Jim Harrison

Portland, OR

Re: JavaScript Connector handling null values

gary1 — Thu, 09 Mar 2023 20:22:09 GMT

Ah, is the entire input_json escaped with slashes, or only the Status array? Or is it the data in the Status array?

Chances are you probably only need to run one of the above through JSON.parse again. This is all guesswork without seeing the recipe and the payload, but it might be worth a quick try.

Any chance you can share a redacted screenshot or copy/paste of the input_json?

const createRecord = (employee) => {
row = {}
// Map top level fields - simple
row.FirstName = employee.FirstName
row.LastName = employee.LastName
row.EmployeeId = employee.EmployeeId
// Map name - nested array

// ====== UPDATES HERE ====== 
//try this first
row.regulation = JSON.parse(employee.Status)[0].Regulation
//maybe try this is the above doesn't work
//row.regulation = JSON.parse(employee.Status[0]).Regulation


return row
}

exports.main = ({input_json}) => {
const input = JSON.parse(input_json)
const rows = []
input.forEach(employee => {
rows.push(createRecord(employee))
})
return {
rows: rows
}
}

Re: JavaScript Connector handling null values

jharrison — Mon, 13 Mar 2023 13:44:25 GMT

Found it! The JSON had spacing that was different than the sample file I was using. I realized that all these attempts at finding the correct path to the key were where I needed to focus. So I went back and looked more closely at the parse JSON Output and noticed the spaces in the Output that I had not placed in the sample document of JSON parse, which is why it kept resulting in 'undefined'. Added the spacing and it worked.

Thanks for your help

Jim

Re: JavaScript Connector handling null values

maryhodges — Mon, 13 Mar 2023 17:18:38 GMT

Hey Jim,

Here are a couple threads that might be helpful!

Cheers,
Mary