Adding up money - onGridRowCheck

[CharlesParker]

Well, I tried...
here is the scenario. I have a grid that has two fields one for the balance and one for the total. For both fields I have
alltrim(str( convert_type(<value>,"N") ,250,2,",")) set as the Display format. The database is MySQL and the field type is Decimal(19,4) btw.


Now using the checkbox selector I am able to quickly compute summary totals based on the rows checked and this does work - up to a point.

Problem 1: I would really like to display the $ sign in my display format for the row as in
alltrim(str( convert_type(<value>,"N") ,250,2,"$(")) but if I do I get NAN as a result.

Problem 2: A value of 7,690.05 rounds to $7.00 and I want the exact number not rounded off like that - so in another example, the balance is 700.00 and the total is 1,350.00
The result is Total=$1.00 and Outstanding Balance=$700.00

Here is the OnGridRowCheck code:

var ele = '';var total = 0;
var total2 = 0;
var ele2 = '';
var ele3 = '';
for(var i = 1; i <= {grid.object}._rowsInGrid; i++) {
    ele = $('{grid.componentname}.V.R' + i + '.__ROWCHECKBOX');
    if( $gvs(ele) != '') {
        ele2 = $('{grid.componentname}.V.R' + i + '.GRAND_TOTAL')
        ele3 = $('{grid.componentname}.V.R' + i + '.REMAINING_BALANCE')
        total = total + parseFloat($gvs(ele2));
        total2 = total2 + parseFloat($gvs(ele3));
    }
}
total = total.toFixed(2);
total2 = total2.toFixed(2);
$svs('total','$'+total)
$svs('total2','$'+total2)

I tried to lookup toFixed and parseFloat in the documentation but couldn't find anything, searched the board and still scratching meh head. There doesn't seem to be much in the way of how to in this area - although I probably missed it!

Can you help?
math.JPG

[GGandhi]

Re: Adding up money - onGridRowCheck

you need to do little bit more work to get this done, also toFixed and parseFloat are javascript, doubt alpha will have documentation for it.
since you have the amount displaying as $ it is a string ( i know you have problem displaying, i don't so i cannot comment on that)
you will need to remove the $ sign then convert it to decimal then add to the total.
so here is a suggestion:

var ele;
var total = 0;
var total2 = 0;
var ele2;
var ele3;
var amt1;
var amt2;
for(var i = 1; i <= {grid.object}._rowsInGrid; i++) {
    ele = $('{grid.componentname}.V.R' + i + '.__ROWCHECKBOX');
    if( $gvs(ele) != '') {
        ele2 = $('{grid.componentname}.V.R' + i + '.GRAND_TOTAL');
amt1 = $gvs(ele2);
amt1 = amt1.substring(1);
total = total + parseFloat(amt1);
        ele3 = $('{grid.componentname}.V.R' + i + '.REMAINING_BALANCE');
amt2 = $gvs(ele3);
amt2 = amt2.substring(1);
           total2 = total2 + parseFloat(amt2);
    }
}
total = total.toFixed(2);
total2 = total2.toFixed(2);
$svs('total','$'+total);
$svs('total2','$'+total2);

hopefully this will work, i don't have time to check this till pm.
if it does not post back then i will run a test this pm.

[Davidk]

Re: Adding up money - onGridRowCheck

I wouldn't bother going after all the displayed rows to determine if they're checked. Inside onGridRowcheck, as a row is checked, use it's e.checkboxValue to add to an array of checked rows. You also have access to e.rowNumber... so use that as well. I was sure I once saw a grid property that was an array of checked rows... but I can't find it... so we build our own.

You probably also want a comma in your formatted number.

In onGridRenderComplete... put this... setting up an array for selected rows.

{grid.Object}.__selectedRows = [];

In onGridRowCheck...

if(e.checkboxValue == true){
	{grid.Object}.__selectedRows.push(e.rowNumber);
}else{
	var index = {grid.Object}.__selectedRows.indexOf(e.rowNumber);
	{grid.Object}.__selectedRows.splice(index,1);
}

var custAmt = 0;
var totAmt = 0;
for(i=0;i<{grid.Object}.__selectedRows.length;i++){
	custAmt = custAmt + parseFloat({grid.Object}.getValue('G','CUSTAMOUNT',{grid.Object}.__selectedRows[i]));
	totAmt = totAmt + parseFloat({grid.Object}.getValue('G','TOTAL',{grid.Object}.__selectedRows[i]));
}

formatCustAmt = custAmt.toFixed(2).replace(/(\d)(?=(\d{3})+\.)/g, "$1,");
formatTotAmt = totAmt.toFixed(2).replace(/(\d)(?=(\d{3})+\.)/g, "$1,");

$svs('total','$ ' + formatCustAmt);
$svs('total2','$ ' + formatTotAmt);

Here we get the e.checkboxValue and if true we add e.rowNumber to our array of checked rows. If false, we remove it.

Now we only have to loop through that number of checked rows.

Because we now have the row numbers in an array we loop through using .getValue... adding to total values. In my case I'm going after CUSTAMOUNT and TOTAL fields... so you'll need to change the code for your field names.

Use parseFloat to turn those .getValues into decimal numbers.
Once all totalled, use .toFixed() with some RegEx to format the number.

gridSelectedTotals.PNG

[CharlesParker]

Re: Adding up money - onGridRowCheck

Seems like the problem with both of your solutions is that the value of Grand_Total and Remaining_Balance in MY situation have a display format that includes the dollar sign which throws the NAN error in the initial parseFloat of the value in the field. If I set the Display format to None - this works for me.

var total = 0;
var total2 = 0;
var ele2 = '';
var ele3 = '';
for(var i = 1; i <= {grid.object}._rowsInGrid; i++) {
	ele = $('{grid.componentname}.V.R' + i + '.__ROWCHECKBOX');
	if( $gvs(ele) != '') {
		ele2 = $('{grid.componentname}.V.R' + i + '.GRAND_TOTAL')
		ele3 = $('{grid.componentname}.V.R' + i + '.REMAINING_BALANCE')

		total = total + parseFloat($gvs(ele2));
		total2 = total2 + parseFloat($gvs(ele3));
	}
}

total = total.toFixed(2);
total2 = total2.toFixed(2);
$svs('total','$'+total)
$svs('total2','$'+total2)

So in short the problem seems to be stemming from the Display format of the field label to include a dollar sign and a comma, etc.
Unless I am mistaken neither of you are actually addressing that?
Am I right? Am I wrong?
Your help is very much appreciated...

[CharlesParker]

Re: Adding up money - onGridRowCheck

Also in reading about adding up money in javascript I read that it might be much smarter to convert dollars all to cents then multiply it back to dollars after - what say you guys? Not sure if I need to do that as these are final numbers, and nobody would pay a fraction as a payment.

[Davidk]

Re: Adding up money - onGridRowCheck

Sorry... I read the formatting thing and then didn't think of it again. Just remove all the formatting except the decimal... using replace()...

if(e.checkboxValue == true){
	{grid.Object}.__selectedRows.push(e.rowNumber);
}else{
	var index = {grid.Object}.__selectedRows.indexOf(e.rowNumber);
	{grid.Object}.__selectedRows.splice(index,1);
}

var custAmt = 0;
var totAmt = 0;
var fld1Amt;
var fld2Amt;
var new1Amt;
var new2Amt;

for(i=0;i<{grid.Object}.__selectedRows.length;i++){
	fld1Amt = {grid.Object}.getValue('G','CUSTAMOUNT',{grid.Object}.__selectedRows[i]);
	newAmt = fld1Amt.replace(/[^0-9.]/g, "");
	custAmt = custAmt + parseFloat(newAmt);
	
	fld2Amt = {grid.Object}.getValue('G','TOTAL',{grid.Object}.__selectedRows[i]);
	new2Amt = fld2Amt.replace(/[^0-9.]/g, "");
	totAmt = totAmt + parseFloat(new2Amt);
}

formatCustAmt = custAmt.toFixed(2).replace(/(\d)(?=(\d{3})+\.)/g, "$1,");
formatTotAmt = totAmt.toFixed(2).replace(/(\d)(?=(\d{3})+\.)/g, "$1,");

$svs('total','$ ' + formatCustAmt);
$svs('total2','$ ' + formatTotAmt);

[CharlesParker]

Re: Adding up money - onGridRowCheck

David, I inserted your code from post 3 subbing out my field name with the same results of NAN due to the display format (dollar sign) causing an issue with the parseFloat...so both yours and mine acheive the same results. Ghandi - I am not sure whats going on there with the substring as I get amt is undefined as an error which I take to mean it removed the 1 from amt1 and changed the name of the variable rather than the value of amt1 which I asume you mean tto trim off the dollar sign with.
Either way - still having an issue and I hope you guys can help me figure it out here...

[CharlesParker]

Re: Adding up money - onGridRowCheck

David your final code suggestion in post 6 worked, and I think I understand why since it strips out the NAN causing stuff with parse float first. I really think my code (taken from the alpha video on checkbx select) should work if I could figure out how to get rid of the NAN stuff in it.
Whew big day of learning for me here, lol.
Thanks for your help!

[Davidk]

Re: Adding up money - onGridRowCheck

Your code can't work because you're trying to parseFloat a formatted string. It needs a number. That's why getting rid of all the stuff that's not a number works. After converting money to cents I'd need to know the context of why that's being suggested... but it's not valid here. When you need to work with numbers... make sure that's what you've got.

[CharlesParker]

Re: Adding up money - onGridRowCheck

Yes, I understand that - thanks for your help on that. For some reason when I read "Display Format" I didn't know it meant that it was going to be a string I was working with - then I didn't know how to remove the stuff I just stuck in there. I just knew it was an issue. for kicks I am still trying to make my code work...lol

[GGandhi]

Re: Adding up money - onGridRowCheck

a display format that includes the dollar sign which throws the NAN error i

that is why i have removed the $ sign did you do that and still failed?

[GGandhi]

Re: Adding up money - onGridRowCheck

parseFloat a formatted string. It needs a number

not correct, the $ sign is the culprit.
parseFloat will successfully convert the string.

[GGandhi]

Re: Adding up money - onGridRowCheck

Yes, I understand that - thanks for your help on that. For some reason when I read "Display Format" I didn't know it meant that it was going to be a string I was working with - then I didn't know how to remove the stuff I just stuck in there. I just knew it was an issue. for kicks I am still trying to make my code work...lol

your code will work if you follow what i have modified, did you try?

[CharlesParker]

Re: Adding up money - onGridRowCheck

yep and I got the amt error as indicated in myprevious post

[CharlesParker]

Re: Adding up money - onGridRowCheck

looks like
amt2 = amt.substring(1);

should've read
amt2 = amt2.substring(1);
in your code...