Running total off of comment field...

[jcrawford08]

Hi all,

the basics - running CR XI in a SQL environment

I've got a conundrum for you:

A comment field where users will enter the amounts of payments received in the following format:

$10567.56|$112.52|$57.12

I need to sum off of this field and display in currency format; however, there may be anywhere from 1-25+ payments loaded into this comment field, so I'm suspecting the use of a variable array, but you guys are the experts, let me know your thoughts!


Thanks,

Jcrawford08

-jcrawford-

Not in word only, but in deed also... 1Jn3:18

 

[jcrawford08]

Alright, so I made it this far, which will display $0 if no payment has been received:


currencyvar money;

local stringvar x:={My_Field.Comments};
local numbervar i;
local numbervar cnt;
local numbervar cntslsh;
for i:=1 to len(x) do(if x = ["$"] then cnt:=cnt+1; if x=["|"] then cntslsh:=cntslsh+1);


if cnt<1 then money:=ccur(0.0) else

How would I then total the amounts made by splitting the array at every instance of "|"?

-jcrawford-

Not in word only, but in deed also... 1Jn3:18

 

[briangriffin]

See what this does for you:

whileprintingrecords;
stringvar array v_comment := split({YourCommentField},"|");
currencyvar v_total := 0;
numbervar x := 1;

while x <= ubound(v_comment)
do
(
v_total := v_total + tonumber(v_comment[x]);
x := x + 1
);

v_total

 

[jcrawford08]

Thanks Brian,

It worked pretty good, I did have to mix up the order a bit, as there are some instances which we have not yet received any funds, and therefore need to display "$0.00" - I had to move the variable declaration around a little to get it to display properly, so it was augmented as follows:

whileprintingrecords;
stringvar array v_comment;
currencyvar v_total:=0;
numbervar x:=1;

if isnull({My_Field.Comments}) then comment:='0' else v_comment:=split({My_Field.Comments},"|");


while x<=ubound(v_comment)
do
(
v_total:=v_total+tonumber(v_comment[x]);
x:=x+1);
v_total


Thanks for all your help, I am humbled by the expertise of those around me

-jcrawford-

Not in word only, but in deed also... 1Jn3:18