2 Replies Latest reply on Jun 8, 2017 6:34 AM by mberber RSS
    mberber Newbie

    Empty Area Problem With Merging Multiple InfoTables

    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.

     

    test.PNG

    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[x];
       
    //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[y];
        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[z];
        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[c];
        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);
    }
    
      • Re: Empty Area Problem With Merging Multiple InfoTables
        pchung Collaborator

        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?

          • Re: Empty Area Problem With Merging Multiple InfoTables
            mberber Newbie

            Hi Pai;

             

            Thank you for the suggestion. I solved the problem.

            sss.PNG

             

            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[x];
            
            
                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[y];
            
            
                    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[z];
            
            
                        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[a];
            
            
                            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