Empty Area Problem With Merging Multiple InfoTables

[mberber1]

Hello To Everone.

I have 4 database tables (core, car_list, gallery_list, personal_list) which i use SQL Query to Fetch Data and output as InfoTable. After that i want to merge them. But even i started from scratch i had encounter a problem which i mentioned in the topics title.

This is the GetSales Service's result. And the JavaScript Code. I dont know what cause this .

Any suggestion will help.

Thank you.

var params = {
  infoTableName : "InfoTable",
  dataShapeName : "GetResult"
};
var result = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape(params);
//DB Results
  var CoreData = me.coreDB();
  var CarData = me.carDB();
  var GalleryData = me.galleryDB();
  var PersonalData = me.personalDB();
//DB Results
//Loop Level 1
var tableLength = CoreData.rows.length;
for (var x = 0; x < tableLength; x++) {
  var row = CoreData.rows;
//Entry Level 1
var newEntry = new Object();
newEntry.ID = row.ID; // INTEGER - isPrimaryKey = true  
newEntry.Car_ID = row.Car_ID; // INTEGER
newEntry.Gallery_ID = row.Gallery_ID; // INTEGER
newEntry.Personal_ID = row.Personal_ID; // INTEGER  
//Loop Level 2  
var tableLength2 = CarData.rows.length;
for (var y = 0; y < tableLength2; y++) {
  var row2 = CarData.rows;
    if(row.Car_ID == row2.Car_ID){
//Entry Level 2  
newEntry.Car_Name = row2.Car_Name; // STRING  
newEntry.Car_Category = row2.Car_Category; // STRING
newEntry.Car_Price = row2.Car_Price; // STRING  
    break;
}
//Loop Level 3
  var tableLength3 = GalleryData.rows.length;
for (var z = 0; x < tableLength3; z++) {
  var row3 = GalleryData.rows;
    if(row.Gallery_ID == row3.Gallery_ID){
    //Entry Level 3 
newEntry.Gallery_Name = row3.Gallery_Name; // STRING  
newEntry.Gallery_Location = row3.Gallery_Location; // STRING
       break;
    }
//Loop Level 4
    var tableLength4 = PersonalData.rows.length;
for (var c = 0; c < tableLength4; c++) {
  var row4 = PersonalData.rows;
    if(row.Personal_ID == row4.Personal_ID){
    //Entry Level4
  newEntry.Personal_Name = row4.Personal_Name; // STRING
  newEntry.Personal_Category = row4.Personal_Category; // STRING
  break;
    }
}
}
}
    result.AddRow(newEntry);
}

[PaiChung · Accepted solution]

You might be hitting into the break stopping the looping short?

Now if all these tables are on the database, I recommend doing this sort of combining Database side (even creating a procedure)

on the Thingworx side, the looping seems a bit expensive, did you look into InfoTable Functions like Intersect?

[mberber1]

Hi Pai;

Thank you for the suggestion. I solved the problem.

var params = {
    infoTableName: "InfoTable",
    dataShapeName: "GetResult"
};
var result = Resources["InfoTableFunctions"].CreateInfoTableFromDataShape(params);
    var coreData = me.coreDB();
    var carData = me.carDB();
    var galleryData = me.galleryDB();
    var personalData = me.personalDB();
var tableLength = coreData.rows.length;
//Start of Level 1 - Core Loop
for (var x = 0; x < tableLength; x++) {
    var row = coreData.rows;
    var newEntry = new Object();
    newEntry.ID = row.ID;
    newEntry.Car_ID = row.Car_ID;
    newEntry.Gallery_ID = row.Gallery_ID;
    newEntry.Personal_ID = row.Personal_ID;
    //Start of Level 2 - Car Loop
    var tableLength2 = carData.rows.length;
    for (var y = 0; y < tableLength2; y++) {
        var row2 = carData.rows;
        if (row.Car_ID == row2.Car_ID) {
            newEntry.Car_Price = row2.Car_Price;
            newEntry.Car_Name = row2.Car_Name;
            newEntry.Car_Category = row2.Car_Category;
        }
        // Start of Level 2 - Gallery Loop
        var tableLength3 = galleryData.rows.length;
        for (var z = 0; z < tableLength3; z++) {
            var row3 = galleryData.rows;
            if (row.Gallery_ID == row3.Gallery_ID) {
                newEntry.Gallery_Location = row3.Gallery_Location;
                newEntry.Gallery_Name = row3.Gallery_Name;
            }
            // Start of Level 2 - Personal Loop
            var tableLength4 = personalData.rows.length;
            for (var a = 0; a < tableLength4; a++) {
                var row4 = personalData.rows;
                if (row.Personal_ID == row4.Personal_ID) {
                    newEntry.Personal_Name = row4.Personal_Name;
                    newEntry.Personal_Category = row4.Personal_Category;
                    break;
                }
            }
        }
    }   
//End of Level 2 - Car loop 
    result.AddRow(newEntry);
}
//End of Level 1- Core Loop