Return all rows instead of just one through JavaScript

  • 2
  • Question
  • Updated 3 years ago
I am repurposing the script. Currently it only pulls the first row but I need it to pull all rows in the aggregate. I have tried getting it to work but all my changes break it. I need to go through and sum two different fields and then I will do some math with the totals. This currently works with the first line in the aggregate but I need it to total all lines in the aggregate.

Here is the script. 
//COMPONENT NAME is what you put in the custom page element 'component type' property. 
skuid.componentType.register('TotalMargin',function(element){      

var $ = skuid.$;
var m = skuid.model.getModel('InvoiceLine');  //Name of your model
var row = m.getFirstRow();    //Using an aggregate model that only returns one row. 
//Field name from your aggregate model
var cost = row.sumCost; 
var total = row.sumTotalDue;


var MathValue =  (total-cost).toFixed(2);  //Do your math here
element.append(
    m.mergeRow(row,MathValue)
    );
});
Photo of Tami Lust

Tami Lust

  • 5,280 Points 5k badge 2x thumb

Posted 3 years ago

  • 2
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
Tammy,

Try replacing the middle section of your code with something like this:

var $ = skuid.$;
var m = skuid.model.getModel('InvoiceLine');  //Name of your model var cost = 0, total = 0;
$.each(m.getRows(),function(i,row){ cost += row.sumCost; total += row.sumTotalDue; });
Photo of Tami Lust

Tami Lust

  • 5,280 Points 5k badge 2x thumb
Thanks Matt! I tried this before and ran into the same problem I am running into now. I get an error on line 14. Which is the element line. It expects to see ")"

//COMPONENT NAME is what you put in the custom page element 'component type' property. 
skuid.componentType.register('TestMargin',function(element){      
var $ = skuid.$;
var m = skuid.model.getModel('InvoiceLine');  //Name of your model
var cost = 0, total = 0;
$.each(m.getRows(),function(i,row){
   cost += row.sumCost;
   total += row.sumTotalDue;
   
MathValue =  'Total Margin:' + " " + (cost/total*100).toFixed(0) + '%' ;  //Do your math here
}
element.append(
    m.mergeRow(row,MathValue)
    );
});
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
Tammy,

I think you just need to close your .each() loop before you start calculating MathValue.

//COMPONENT NAME is what you put in the custom page element 'component type' property. 
skuid.componentType.register('TestMargin',function(element){      
var $ = skuid.$;
var m = skuid.model.getModel('InvoiceLine');  //Name of your model
var cost = 0, total = 0;
$.each(m.getRows(),function(i,row){
   cost += row.sumCost;
   total += row.sumTotalDue;
});
MathValue =  'Total Margin:' + " " + (cost/total*100).toFixed(0) + '%' ;  //Do your math here

element.append(
    m.mergeRow(row,MathValue)
    );
});
Photo of Tami Lust

Tami Lust

  • 5,280 Points 5k badge 2x thumb
Thanks for working this through with me. I have it so there are no errors in the code however it breaks the page.


//COMPONENT NAME is what you put in the custom page element 'component type' property. 
skuid.componentType.register('TestMargin',function(element){      
var $ = skuid.$;
var m = skuid.model.getModel('InvoiceLine');  //Name of your model
var cost = 0, total = 0; //setting variables for the two fields we are summing
$.each(m.getRows(),function(i,row){ //get multiple rows in the model
   cost += row.sumCost; //get the cost row and assigning them to the variable
   total += row.sumTotalDue; //get the total row and assigning them to the variable
}); //close the each
var MathValue =  'Total Margin:' + " " + (cost/total*100).toFixed(0) + '%' ;  //Do your math here

element.append(
    m.mergeRow(row,MathValue)//this is what writes to component
    
    );

}); //closes the whole script
(Edited)
Photo of Tami Lust

Tami Lust

  • 5,280 Points 5k badge 2x thumb
So I realized that I was using the wrong alias name, I was using "sumTotalInvoice" when I should have been "sumTotalDue" DUH!!!

The lesson here is check your alias name's so you don't spend all night banging your head against the wall.

Here is the final code that worked:

// Total Margin


skuid.componentType.register('TotalMargin',function(element){
    var $ = skuid.$;
    var invoiceline = skuid.model.getModel('InvoiceLine');
    var render = function(){
        var row = invoiceline.getRows();//get rows in model
        var total = 0, cost = 0;
        
    
        $.each(invoiceline.data, function (i, row){	//loop through rows
        //adding the rows. use the alias name for the field
            cost += row.sumCost; 
            total += row.sumTotalDue; 
        });
        //debug
        console.log('Cost' + cost);
        console.log('Total' + total);
        
        var value = (cost/total*100).toFixed(0);//Do Math here
        var text = 
            'Total Margin:' +" " + value + '%';//This what gets rendered in component
        element.append(
            invoiceline.mergeRow(row,text)//This is writting to the component
        );
    
    };

    skuid.events.subscribe('models.loaded',function(updateResult){
       if (updateResult.models.InvoiceLine)  {
           render();
       }
    });



    render();
    
});    
Photo of Barry Schnell

Barry Schnell, Champion

  • 18,076 Points 10k badge 2x thumb
Great news, glad you got it sorted Tami.  

I'm not exactly sure how you are building your models and how rows get populated in those models so one thing you might want to do is make this modification:

$.each(invoiceline.data, function (i, row){ //loop through rows
        //adding the rows. use the alias name for the field
            cost += row.sumCost; 
            total += row.sumTotalDue; 
        });
to
$.each(invoiceline.data, function (i, row){	//loop through rows
        //adding the rows. use the alias name for the field
            cost += (row.sumCost || 0); 
            total += (row.sumTotalDue || 0);  
});

This will ensure that if/when sumCost or sumTotalDue are ever null/undefined, that you'll get a zero (0) instead.
Photo of Tami Lust

Tami Lust

  • 5,280 Points 5k badge 2x thumb
Thanks for that tip Barry. I have added your suggestion into the code. This has been a great learning experience for me!

I ran into one problem this morning. I added a filter to the table and when I filter the data the new result in the component is not replaced instead it is added to.

I realized that I was missing "element.empty();"

I added the updated code below. There are tons of comments to help me understand everything that is going on.

 Prior to adding that element this is how the result would display:


After the data displayed replacing the prior result showing only 1 result:



// Total Margin


skuid.componentType.register('TotalMargin',function(element){
    var $ = skuid.$;
    var invoiceline = skuid.model.getModel('InvoiceLine');
    var render = function(){
        element.empty();//this empties the element so a new value can replace the prior value
        var row = invoiceline.getRows();//get rows in model
        var total = 0, cost = 0;
        
    
        $.each(invoiceline.data, function (i, row){	//loop through rows
        //adding the rows. use the alias name for the field
            cost += (row.sumCost || 0); 
            total += (row.sumTotalDue || 0); 
        });
        //debug
        console.log('Cost' + cost);
        console.log('Total' + total);
        
        var net = (cost-total).toFixed(0);//Do Math here
        var margin = (1-net/total*100).toFixed(0);
        var text = 
            'Total Margin:' +" " + margin + '%';//This what gets rendered in component
        element.append(
            invoiceline.mergeRow(row,text)//This is writting to the component
        );
    
    };

    skuid.events.subscribe('models.loaded',function(updateResult){
       if (updateResult.models.InvoiceLine)  {
           render(); //Displays result on requery
       }
    });



    render();//Gets result on page load
    
});    
Photo of Matt Sones

Matt Sones, Champion

  • 31,478 Points 20k badge 2x thumb
Nice work, Tami! We learn by doing.
Photo of Rob Hatch

Rob Hatch, Official Rep

  • 44,006 Points 20k badge 2x thumb
Yes Tami.  Thanks for sharing!  Welcome to the Dark Side of Javascript.  We have better cookies.