Can we have a counter that counts the non-empty fields in one record?

[thebigbadwolf]

Can we have a counter that counts the non-empty fields in one record?

 

[Madawc]

I don't think there is a short-cut. If I had to do it, I would define one test per field, e.g.

if not isnull({a.field})
and {a.field} <> 0
then 1
else 0

One would then sum them:

@CountA + @CountB + @CountC .....

Hope this helps.

(Note that nulls in Crystal will stop any formula, unless you test for them before trying to do anything else with that field.)

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[thebigbadwolf]

By creating this formula we take a total count of the fields in the end of the record.

If we need to have something like indexes in the record (like 1, 2, 3 etc.) is that possible?

Thank you

 

[lbass]

Please explain in more detail what you mean by indexes and why you need them.

-LB

 

[thebigbadwolf]

I have split the details section on multiple parts (details a, details b, details c etc) and the data of details "a" is for example the code of the record and the "info1" field the data of details "b" is for example the code of the record (again) and the "info2" field etc.

In the print preview I need to have numbers 1, 2, 3, 4 etc and by selecting a simple counter I have 1, 1, 1, 2, 2, 2, 3, 3 etc.

I need to know if there is a formula which will give me the needed results (which have clear logic)

 

[lbass]

Add a separate formula to each detail section that corresponds to the field being tested, as in:

//detail_a:
whileprintingrecords;
numbervar cnt;
if not isnull({table.info1}) or
trim({table.info1}) <> "" //use this clause only for strings
then
cnt := cnt + 1;

//@detail_b:
whileprintingrecords;
numbervar cnt;
if not isnull({table.info2}) or
trim({table.info2}) <> "" //use this clause only for strings
then
cnt := cnt + 1;

etc.

The result will be zero if the field is null, so you could suppress the detail section if the formula = 0.

You need a reset formula for the group header (code?):

whileprintingrecords;
numbervar cnt;
if not inrepeatedgroupheader then
cnt := 0;

-LB

 

[thebigbadwolf]

THANK YOU!!!

 

[thebigbadwolf]

For the same report, as we count the lines, we need to have a total summary of every code (so we will use the reset formula)

so the formulas are:

//sum01:
whileprintingrecords;
numbervar sum_qty;
if not isnull({EQUIPMENT_SPARE.ETSP_STRING1})
//use this clause only for strings
then sum_qty := sum_qty + {TABLE1_01.FRT1_NUMBER3};

//sum02:
whileprintingrecords;
numbervar sum_qty;
if not isnull({EQUIPMENT_SPARE.ETSP_STRING1})
//use this clause only for strings
then sum_qty := sum_qty + {TABLE1_01.FRT1_NUMBER3};

etc

and the reset formula is

whileprintingrecords;
numbervar sum_qty;
if not inrepeatedgroupheader then sum_qty := 0;

All of these working fine, but I need to have only one summary (the final) in every group, printed ONLY on the first detail line of the group (and not all the phases of the summary line by line).

Have you got any ideas how I can do that?

 

[lbass]

The formulas I gave you were designed to give you the linenumbers that you wanted, but this method will not allow you to put a summary in the first detail section. You might have to use two different methods to meet these two goals.

What field are you grouping on? Why wouldn't you put the group summary in the group header section instead of the first detail section? Please show a sample of how your report currently looks, adding in the summary you want to see.

-LB

 

[thebigbadwolf]

sorry, I didn't wrote the formulas right

//sum01:
whileprintingrecords;
numbervar sum_qty;
if not isnull({EQUIPMENT_SPARE.ETSP_STRING1})
//use this clause only for strings
then sum_qty := sum_qty + {TABLE1_01.FRT1_NUMBER3};

//sum02:
whileprintingrecords;
numbervar sum_qty;
if not isnull({EQUIPMENT_SPARE.ETSP_STRING2})
//use this clause only for strings
then sum_qty := sum_qty + TABLE1_02.FRT1_NUMBER3};

etc

So this is something that I think that has the logic of the lines counter, with a need of reset on group header (EQUIPMENT_SPARE.ETSP_SPARE)

That's why I done it following the same logic

 

[lbass]

As I said, you cannot use this method to get a summary in the first line of the detail section, since the formula calculates "whileprintingrecords"--it doesn't have the results until all details have been evaluated. So, for further help, please respond to my earlier requests.

-LB

 

[thebigbadwolf]

Can I do something to get the summary at the last line before change of group (as I get it now) but without print the phases of the summary at the lines above?

 

[lbass]

Why wouldn't you just use a group footer section? You would add a display formula like this:

whileprintingrecords;
numbervar sum_qty;

-LB

 

[thebigbadwolf]

Is Working Fine. Thank you

I wonder if I can somehow print the summary on one of details line to avoid the empty columns in the line that has the summary in (when I export the results to excel file)

 

[lbass]

You could try placing this in the detail_b section:

//{@displgrpsumindet}:
evaluateafter {@sum2};
whileprintingrecords;
numbervar sum_qty;
if onlastrecord or
{EQUIPMENT_SPARE.ETSP_SPARE} <> next({EQUIPMENT_SPARE.ETSP_SPARE}) then
sum_qty

Then you can suppress zeros by formatting the formula->format->number->customize->suppress if zero.

-LB