Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Running total off of comment field...

Status
Not open for further replies.

jcrawford08

Technical User
Nov 19, 2008
71
US
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
 
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
 

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


 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top