Simple Formula (Working with Records in Fields)

[CF2201]

I have 3 numeric fields in a database i.e. {abc.a},{abc.b} & {abc.c}.

Data in RECORD fields {abc.a} + {abc.b} should equate data in record field {abc.c}.

To check this, I created a new report file (using the active database) in Crystal and named it Data_Check. Data_Check displays {abc.c} and the result of a + b for comparison.

The formula to find the result of a + b is {abc.a} + {abc.b}and the formula {@data_check}was created. I then placed an argument code in the details-format field-suppress-visibility {@data_check}= {abc.c} which should show all records where a + b <> c.

It worked, but some records with the same figures were still being displayed. The aim was to find the records where a + b <> c , records that did not match. I have checked my database (dBase) and found no errors.

Where am I going wrong?

Your help will be appreciated. Am using v8.5

Claude

 

[daaf]

Did you check if the fiels are null in the formula :

if isnull(a) then
if isnull(b) then
0
else
b
else
if isnull(b) then
a
else
a+b

 

[dgillz]

I have also seen values that display as 1.04, but the &quot;real&quot; value is 1.039999999. Round your fields to a significant # of decimals and try it again.

Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[CF2201]

To both dgillz and daaf, thank you for answering.

dgillz, I dlready tried changing the decimal points without much success - thanks anyway.

daaf, I did use your argument, but it returned 0...all the way. For what it's worth, here is the real thing that will not work...pick at it, please. As you can see, there's data in 7 fields that I want to add.

if isnull({Ccs.REV_WT}) then
if isnull({Ccs.IMP_DAM}) then
if isnull({Ccs.CL_AMT}) then
if isnull({Ccs.OLD_DAM}) then
if isnull({Ccs.UPGDE}) then
if isnull({Ccs.REBILL}) then
if isnull({Ccs.PTI}) then
0

else
{Ccs.REV_WT}
else
if isnull({Ccs.REV_WT}) then
{Ccs.OLD_DAM}
else
if isnull({Ccs.OLD_DAM}) then
{Ccs.UPGDE}
else
if isnull({Ccs.UPGDE}) then
{Ccs.REBILL}
else
if isnull({Ccs.REBILL}) then
{Ccs.PTI}
else
if isnull({Ccs.PTI}) then
{Ccs.CL_AMT}
else
if isnull({Ccs.Cl_Amt}) then
{Ccs.IMP_DAM}

else

{Ccs.REV_WT}+{Ccs.IMP_DAM}+{Ccs.CL_AMT}+{Ccs.OLD_DAM}+{Ccs.UPGDE}+{Ccs.REBILL}+{Ccs.PTI}

Do let me know what I am doing wrong.

Thanks

 

[daaf]

here is what i'm doing on reports (I'm using a global variable)
&quot;
WhilePrintingRecords;
Global numberVar TotFrais:=0;
if isnull({#SomFrais}) then
if isnull({VIEW_CR8_NOTACCPRO.MTTGS}) then
TotFrais := 0
else
TotFrais := {VIEW_CR8_NOTACCPRO.MTTGS}
else
if isnull({VIEW_CR8_NOTACCPRO.MTTGS}) then
TotFrais := {#SomFrais}
else
TotFrais := {#SomFrais}+{VIEW_CR8_NOTACCPRO.MTTGS}
&quot;

If it can help you ....

 

[CF2201]

daff, both your set of codes worked...and added the two fields. This, I checked in dbase via dbase easy commands e.g.

&quot;disp rev_est for rev_wt+imp_dam<>rev_est&quot;

My problem is getting the codes right past the fist two fields while using CR. As you can see, I have data in 7 fields that I would like to add. I think that the problem lies with the fact that, although they all count, NOT all the 7 fields contain data (per each record).
Using the little that I know of CR I have tried...alas.

I'm sorry if my ignorance is driving you mad, but can you persevere and have another look at my calvary of the moment.

Many thanks

Claude

 

[daaf]

So, you can try this, it will be easier :

WhilePrintingRecords;
Global numberVar Tot:=0;
Global numberVar Rev:=0;
Global numberVar Imp:=0;
Global numberVar AMT:=0;
Global numberVar DAM:=0;
Global numberVar UPGE:=0;
Global numberVar BILL:=0;
Global numberVar PTI:=0;

if not isnull({Ccs.REV_WT}) then Rev:= {Ccs.REV_WT};
if not isnull({Ccs.IMP_DAM}) then Imp := {Ccs.IMP_DAM};
if not isnull({Ccs.CL_AMT}) then AMT := {Ccs.CL_AMT};
if not isnull({Ccs.OLD_DAM}) then DAM := {Ccs.OLD_DAM};
if not isnull({Ccs.UPGDE}) then UPGE := {Ccs.UPGDE};
if not isnull({Ccs.REBILL}) then BILL := {Ccs.REBILL};
if not isnull({Ccs.PTI}) then PTI := {Ccs.PTI};

Tot:= {Ccs.REV_WT} +{Ccs.IMP_DAM}+ {Ccs.CL_AMT}+{Ccs.OLD_DAM}+{Ccs.UPGDE}+{Ccs.REBILL}+{Ccs.PTI};

 

[CF2201]

Daff, It worked perfectly....so easy yet so difficult for some (Gulp!)

I feel sorry for you had to type my funny field names. BTW, the Syntax &quot;Imp&quot; is used by CR I,therefore, changed it to IMPT.

I jumped up & down too quickly because when I applied the new codes, the results were the same - per my original question i.e. some figures that should not be shown are still there...in fact, the same figures, mistakes that prompted me to go on line for help.

I'll try a few more things and get back to you.

Kind regards.

Claude

 

[CF2201]

Daaf, I am still trying, but nothing worthwhile to report after having used my own or your codes.

There is no real pattern to the figures that I do not want to see. A few examples are:

a + b c
===============
73.85 73.85
158.45 158.45
65.85 65.85
67.85 67.85
39.73 39.73
40.80 40.80
129.73 129.73
297.03 297.03
64.35 64.35

That's off a list of some 50 records...the others were ok.

Can you make anything of this?

Thanks

Claude

 

[dgillz]

I have had problems with is IsNull() logic in the past and had to use if IsNull() or if length()=0

try that Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[CF2201]

Dgillz...It worked!

I used

&quot;if len(ToText({Ccs.REV_WT}))>0 then Rev:= {Ccs.REV_WT};&quot;
& etc. AND IT WORKED.

A 10000 thanks to you and Daaf for your support.

But, I must say that CR makes it very difficult for such an easy calculation. I did it first try with dBase iii.

It's nearly 4am DownUnder, so I'm off to bed...and happy that &quot;my calvery of the moment&quot; is over.

Thanks again.

Claude