Need Help with Scheduled Job with REST API parsing some custom fields into an array

[mattmm]

Hi,

I have a Scheduled Job doing a REST API GET from an external Asset dbase (using RESTMessage V2). I am parsing the responseBody fine for named properties, but there is a section for "customFields" that groups together properties and I am not sure how to parse these in my script without bringing the lot in (see ResponseBody example further below)?

Scheduled job Script

try {
    var r = new sn_ws.RESTMessageV2('ConnectWise', 'Default GET');
    r.setStringParameterNoEscape('yesterdayFormatted', gs.getProperty('connectwise.currentDateTime'));
    var response = r.execute();
    var responseBody = response.getBody();
    var httpStatus = response.getStatusCode();
    var parsed = JSON.parse(responseBody);

    var statusCodesProperty = gs.getProperty('http.response.status.codes');
    var httpStatusCodeArray = statusCodesProperty.split(', ');

    //Check if httpStatus exists in the array
    if (httpStatusCodeArray.indexOf(httpStatus.toString()) !== -1) {
        var crImpSet = new GlideRecord('sys_import_set');
        crImpSet.initialize();
        crImpSet.mode = 'asynchronous';
        crImpSet.table_name = 'u_connectwise_asset_data';
        crImpSet.state = 'loading';
        crImpSet.insert();

        var restGR = new GlideRecord('u_connectwise_asset_data');
        for (var i = 0; i < parsed.length; i++) {
            restGR.initialize();
            restGR.u_config_recid = parsed[i].id;
            restGR.u_configuration_type = parsed[i].type.name;
            restGR.u_status = parsed[i].status.name;
            restGR.u_company = parsed[i].company.name;
            restGR.u_expires = parsed[i].warrantyExpirationDate;
            restGR.u_serial_number = parsed[i].serialNumber;
            restGR.u_model_number = parsed[i].modelNumber;
            restGR.u_vendor = parsed[i].vendor.name;
            restGR.u_tag_number = parsed[i].tagNumber;
            restGR.u_contact = parsed[i].contact.name;
            restGR.u_installed = parsed[i].installationDate;
            restGR.u_locations = parsed[i].location.name;
            restGR.u_site_name = parsed[i].site.name;
            restGR.u_purchased = parsed[i].purchaseDate;
            restGR.u_manufacturer = parsed[i].manufacturer.name;

            restGR.sys_import_set = crImpSet.sys_id;
            restGR.insert();
        }
    }
    crImpSet.state = "loaded";
    crImpSet.load_completed = gs.nowDateTime();
    gs.setProperty('connectwise.currentDateTime', crImpSet.load_completed.getDisplayValue());
    crImpSet.update();

    var transformer = new GlideImportSetTransformer();
    transformer.transformAllMaps(crImpSet); //Transform the import set rows
    if (transformer.isError()) {
        gs.error('Error executing the transform');
    }
} catch (ex) {
    var message = ex.message;
}

A sample ResponseBody below shows 4 of the fields I am parsing without issue (id, status, company, contact), but I don't know how to parse the custom fields at the bottom called "Disposal Date" and "Date Last Sighted". I am a beginner at API scripting, so any help much appreciated. Please see ResponseBody below, but also the Scheduled Job Script above.

ResponseBody

 {
        "id": 19192,
        "name": "CWRR213",
        "type": {
            "id": 25,
            "name": "PC/Notebook",
            "_info": {
                "type_href": ""
            }
        },
        "status": {
            "id": 1,
            "name": "Active",
            "_info": {
                "status_href": ""            }
        },
        "company": {
            "id": 2791,
            "identifier": "RANDOM",
            "name": "Random Ltd",
            "_info": {
                "company_href": ""
            }
        },
        "contact": {
            "id": 12225,
            "name": "Random",
            "_info": {
                "contact_href": ""
            }
         },
        "deviceIdentifier": "",
        "serialNumber": "1234567",
        "modelNumber": "ThinkPad L480",
        "tagNumber": "A0012345",
        "installationDate": "2019-06-04T10:51:33Z",
        "warrantyExpirationDate": "2022-07-08T00:00:00Z",
        "vendorNotes": "",
        "notes": "Location Jan 2023 Sesame St Site Audit: Admin Office     \nHad a Sticker that has since worn off (old Asset Tag: A004321)\n\n123456 - User Exit: Joe Bloggs - appears to be a shared machine ",
        "macAddress": "",
        "lastLoginName": "",
        "billFlag": false,
        "backupSuccesses": 0,
        "backupIncomplete": 0,
        "backupFailed": 0,
        "backupRestores": 0,
        "backupServerName": "",
        "backupBillableSpaceGb": 0.00,
        "backupProtectedDeviceList": "",
        "backupYear": 0,
        "backupMonth": 0,
        "ipAddress": "",
        "defaultGateway": "",
        "osType": "",
        "osInfo": "",
        "cpuSpeed": "",
        "ram": "",
        "localHardDrives": "",
        "manufacturer": {
            "id": 12,
            "name": "Lenovo",
            "_info": {
                "manufacturer_href": ""
            }
        },
        "questions": [
             
            {
                "answerId": 60742,
                "questionId": 289,
                "question": "IMEI",
                "sequenceNumber": 3.00,
                "numberOfDecimals": 0,
                "fieldType": "Text",
                "requiredFlag": false
            }
        ],
        "activeFlag": true,
        "managementLink": "",
        "remoteLink": "",
        "mobileGuid": "",
        "companyLocationId": 72,
        "showRemoteFlag": false,
        "showAutomateFlag": false,
        "needsRenewalFlag": false,
        "manufacturerPartNumber": "",
        "_info": {
            "lastUpdated": "2024-05-29T01:15:00Z",
            "updatedBy": "",
            "dateEntered": "2019-06-27T06:31:55Z",
            "enteredBy": ""
        },
 "customFields": [
 {
                "id": 55,
                "caption": "Disposal Date",
                "type": "Date",
                "entryMethod": "EntryField",
                "numberOfDecimals": 0
            },
            {
                "id": 80,
                "caption": "Date Last Sighted",
                "type": "Date",
                "entryMethod": "EntryField",
                "numberOfDecimals": 0,
                "value": "2024-05-29T00:00:00Z"
            }
			
			     ]
    },

Any Help is more than greatly appreciated!

[mattmm · Accepted solution]

I've stood on the soldiers of giants...thanks @Kris Moncada and have somehow solved this with the below code snippet

// Iterate over custom fields
			parsed[i].customFields.forEach(function(customfield) {
				if (customfield.caption === "Disposal Date") {
  					restGR.u_disposal_date = customfield.value;  } 
				else if (customfield.caption === "Date Last Sighted") {
  					restGR.u_date_last_sighted = customfield.value;  
  }
});

, which I've added after my other parsing. 

[Maik Skoddow]

As "customFields" represents an array you can iterate it pretty simple:

parsed[i].customFields.forEach(objFields) {
  gs.info(objFields.id + ': ' + objFields.caption);
});

[ChrisBurks]

One way this can be accomplished since "customFields" is an array is use built-in JavaScript array method "reduce".

//@fieldName = string "Caption of the field"
function getCustomFieldValue( fieldName ) {
    return function (value, field) {
       // from the looks of the sample, value isn't always there. So check if it's there before trying to assign it 
         value =  field.caption == fieldName && field.hasOwnProperty('value') ? field.value : "";
          //return the value
          return value;
    }
}

With that function use the reduce method to get the value of the custom field by passing in the specified name for which field you want to capture

...

restGR.u_disposal_date = parsed[i].customFields.reduce(getCustomFieldValue('Disposal Date'), "");
restGR.u_date_last_sighted = parsed[i].customFields.reduce(getCustomFieldValue('Date Last Sighted'), "");
...

Of course this example assumes they will be the same custom fields. But there are other ways to set this up as well.