Good Morning - I have been struggling with this all week. I thought it would be easy... but alas, it is not.
I have a jot form (electronic form). We typically process the submissions into ServiceNow via email. But, Jotform no longer supports plain text which makes getting through all the HTML a pain. I have discovered that they have an api which seem to work really well, although the payload seems to be in a form that ServiceNow finds difficult to parse. I have created a custom data source that sends a get request to the api. It pulls in data and I have been able to get..something into the import sets...but no rows are being created. I'm not sure what I am missing or even where to look. Here is my script:
(function loadData(import_set_table, data_source, import_log, last_success_import_time) {
var jotFormAPIKey = 'my_api_key';
var formId = '233303679353155';
var endpoint = 'https://api.jotform.com/form/' + formId + '/submissions?apiKey=' + jotFormAPIKey + '&limit=3';
var restMessage = new sn_ws.RESTMessageV2();
restMessage.setHttpMethod('get');
restMessage.setEndpoint(endpoint);
try {
var response = restMessage.execute();
var responseBody = response.getBody();
var httpStatus = response.getStatusCode();
gs.info("HTTP Status: " + httpStatus);
if (httpStatus == 200) {
var jsonData = JSON.parse(responseBody);
gs.info("Number of submissions: " + jsonData.content.length);
jsonData.content.forEach(function(submission) {
var gr = new GlideRecord('u_pfr_insight_2024_orders');
gr.initialize();
// Standard field mappings
gr.u_address = ''; // Add logic to extract address if available
gr.u_city = submission.answers['9'].answer.city || '';
gr.u_email = submission.answers['11'].answer || '';
gr.u_first_name = submission.answers['6'].answer.first || '';
gr.u_last_name = submission.answers['6'].answer.last || '';
gr.u_phone_number = submission.answers['10'].answer.full || '';
gr.u_state = submission.answers['4'].answer || '';
gr.u_submission_id = submission.id;
gr.u_total_number_of_attendees = parseInt(submission.answers['146'].answer) || 0;
// Checking for meeting location
var meetingLocationFields = {
"5": "arLocationsdates6",
"7": "iaDatetimelocation",
"8": "ilDatetimelocation",
"18": "inDatetimelocation",
"19": "ksDatetimelocation",
"20": "kyDatetimelocation",
"21": "miDatetimelocation",
"8": "mnDatetimelocation",
"22": "moDatetimelocation",
"23": "msDatetimelocation",
"24": "neLocationsdates24",
"25": "ohLocationsdates25",
"26": "sdLocationsdates26",
"27": "tnLocationsdates27",
"46": "wiDatetimelocation",
// Add the rest of the field numbers and their corresponding field identifiers
};
var meetingLocationFound = false;
for (var fieldNumber in meetingLocationFields) {
if (submission.answers[fieldNumber] && submission.answers[fieldNumber].answer) {
gr.u_collapsed_column = submission.answers[fieldNumber].answer;
gs.info("Meeting Location Found: " + gr.u_collapsed_column);
meetingLocationFound = true;
break;
}
}
if (!meetingLocationFound) {
gs.info("No meeting location found for this submission");
}
try {
var sys_id = gr.insert();
gs.info("Record inserted with sys_id: " + sys_id);
} catch (insertError) {
gs.error("Error inserting record: " + insertError.message);
}
});
} else {
import_log.error('Error in REST call: ' + httpStatus);
}
} catch (ex) {
import_log.error('Error in loadData script: ' + ex.message);
gs.error('Error in loadData script: ' + ex.message);
}
})(import_set_table, data_source, import_log, last_success_import_time);
I have the limit set to 3 so that I am not creating 1000's of records every time I run to test.
So, when I click to load all records - this is the result:
When I click import sets, I see this is created:
but when you look in there....there are no rows... I would have expected 3. There is also nothing under Import Set Runs or Import Log.
But, when you click on 'Loaded Data' in the first screenshot, it shows you that three more rows have been added to the ISET
I'm not sure why it's 1 and not 3. I don't know why it's ignored:
But when you click on one of the 3 row numbers added to the ISET, it does show that it somewhere got the submission data... There is a comment that said it was ignored by an onbefore script... I don't know where that is coming from:
Can anyone tell me what I am missing? fill in the gaps? I would really appreciated it!! Thank you!!
