Formatting the Output of the SUM workaround

[kmullins]

In the post SUM lookup column ? an excellent solution for using the hooks folder to add a SUM to a lookup field was shown, and I have been able to make it work, except I would like to format the output in US dollars, and I can't figure out the best way to do it.

Thanks in advance for any assistance.

Code: Select all

			case 'tableview':
				$sum=sqlvalue("SELECT sum(Donations.Amount) from Supporters, Donations where Supporters.ID = Donations.SupporterID");
				$header="<%%HEADER%%><script type=\"text/javascript\">
							\$j(function(){
								\$j('td.Supporters-TotalDonated').last().text(".$sum.").addClass('text-right');
							});
					</script>";
				break;

Screenshot 2023-12-18 104845.png (42.24 KiB) Viewed 5263 times

[pbottcher]

Hi,

you can try

Code: Select all

case 'tableview':
	$sum=sqlvalue("SELECT sum(Donations.Amount) from Supporters, Donations where Supporters.ID = Donations.SupporterID");
	$header="<%%HEADER%%><script type=\"text/javascript\">
				\$j(function(){
					\$j('td.Supporters-TotalDonated').last().text(parseFloat(".$sum.",2).toLocaleString('en', { style: 'currency', currency: 'USD', useGrouping: true })).addClass('text-right');
				});
		</script>";
	break;

[kmullins]

Thanks for the idea. unfortunately, I couldn't get it to work. Any time I try any formatting, the field goes blank, so I am just going to keep it without the $.

[jfischer]

Hello Kmullins

Formatting as $ goes to Data Format to set. He also takes care of that in total.

Grüße Josef

[kmullins]

Thanks jfischer,

Unfortunately, that field is a calculated field, and I am using the following formula to format the calculation.

Code: Select all

SELECT CONCAT('$', FORMAT(COALESCE(SUM(`Payments`.`PaymentAmount`), 0.0), 2)) AS `Total`
FROM `Registrations`
LEFT JOIN `Payments` ON `Payments`.`RegID`=`Registrations`.`ID`
WHERE `Registrations`.`ID`='%ID%'

If I set the data type for that field to currency, that field turns to $0.00 instead of the calculated value.

Thanks though.