How do I create a related record from csv flat file data?

Help Center Get Answers
1

I receive  a csv flat file that contains the data for a complete invoice.   I have created a relational database with 5 objects that have connections between them that makes up my invoice data base.  Is there an automated method of inputting this data into the various objects so they form a related record.  The way I do this now is to open a form,  manually enter data into the fields and then when I have done this I submit the form,  by doing this Knack sets up,  behind the scenes,   the necessary primary and foriegn key fields that relate the data correctly together.   Is there a way to do it automatically using a scripting program or other means.

Thank you    

2

Hello Franbill,

 

I droped a mail you at given address.

Let me know if you got it or not. 

Regards,

Sunny Singla

ssingla1985@gmail.com

+919855089359

3

Thank you so much,  yes I tried to email you to ask for further assistance but I didn't hear back,  so please email me at franbill@bell.net.  

I look forward to contacting you.

Fran

4

Hello Franbill,

 

Yes this can be achive using Custom Javascript code like below. 

var Object_FiledIds = ['field_123', 'field_456'];
var File_Headers = ['KEY', 'VALUE'];

var FieldToUpdateIfUnique = 1;
var object_id = 'object_123';
var fileSeperator = ",";

// var x = 0;
var FileInputs = [];
var header_arr;
var processedrows = 1;
var UpdateRecords = 0;
var InsertedRecord = 0;


$("#view_123 input[type=submit]").live("click", function () {
FileInputs = [];
UpdateRecords = 0;
InsertedRecord = 0;
processedrows = 1;
UniqueColumns = [];
LoadFile();
Knack.showSpinner();
getRecords(1);
return false;
});

var UniqueColumns = [];

function RunOneByOne(y) {
if (FileInputs[y] == undefined)
return;
var col_arr = FileInputs[y].split(fileSeperator);
var obj_insert = {};
var isFound = 0;
var id = "";

for (var z = 0; z < col_arr.length; z++) {
//console.log(Object_FiledIds[z]);
if (col_arr[z] != "")
obj_insert[Object_FiledIds[z]] = $.trim(col_arr[z].replace(/"/g, ''));
}

//console.log(obj_insert);

InsertNewRecord(obj_insert);
}

function getRecords(pageNumber) {
var getUniqueColumns = baseUrl_toFindDuplicate.replace("[OBJ]", object_id).replace("page=1", "page=" + pageNumber);
$.ajax({
url: getUniqueColumns,
type: 'GET',
headers: {
'X-Knack-Application-Id': API_ID, // change to your app's API info
'X-Knack-REST-API-Key': API_Key
},

success: function (response) {
var objects = response.records;

for (var x = 0; x < objects.length; x++) {
var Unique = {};
Unique.value = objects[x][Object_FiledIds[UniqueColumn]];
Unique.id = objects[x].id;
UniqueColumns.push(Unique);

}
if (pageNumber < response.total_pages) {
getRecords(pageNumber + 1);
}
else {
RunOneByOne(1);

}
}
});
}

function LoadFile() {
var fileInput = document.getElementById('field_bcbc_upload');

var file = fileInput.files[0];
if (file == undefined) {
alert('Please upload file first!');
return false;
}

var reader = new FileReader();

reader.onload = function (e) {
var Rawarray = reader.result.split("\n");
FileInputs = Rawarray.filter(function (entry) { return entry.trim() != ''; });;
//console.log(FileInputs);

var header_arr = FileInputs[0].split(fileSeperator);
//console.log(header_arr, File_Headers);
for (var x = 0; x < 5; x++) {
//console.log(File_Headers[x].toString(), header_arr[x].toString());
if (File_Headers[x].toString().replace(/ /g, "") != header_arr[x].toString().replace(/ /g, "")) {
//alert('header not match!!');
return false;
}
}
}
reader.readAsText(file);
}

function InsertNewRecord(object) {
$.ajax({
url: 'https://us-api.knack.com/v1/objects/' + object_id + '/records',
type: 'POST',
data: object,
headers: {
'X-Knack-Application-Id': API_ID, // change to your app's API info
'X-Knack-REST-API-Key': API_Key
},

success: function (response) {

processedrows = processedrows + 1;
RunOneByOne(processedrows);
if (processedrows >= FileInputs.length - 1) {
Knack.hideSpinner();
$(".kn-form-confirmation").show();
$(".kn-form-confirmation .kn-message p").html('Upload Successful:<br/>Records Updated ' + UpdateRecords + ' <br/> Records Inserted ' + InsertedRecord);
$("#view_123 form[method=post]").hide();
$(".kn-form-reload").hide();
//setTimeout(function () {

// location.href = '#claims-master/';
//}, 3000);
}
}
});
}

Mail me for further assistance .

 

Thanks,

Sunny Singla

ssingla1985@gmail.com

+919855089359