one record per group when field is null

[todder]

I want to display one line for each record that has a null value in a particular field. I also want to display one "summary" line for all records that have duplicate data in the field.

My instinct is to set up a group on the field, but then all the records where the field is null are grouped together.

Is there a way to set up a group so that each time the grouping field is null, it will be grouped with one record per group?

Or am I barking up the wrong tree?

I have an idea of how to do it using formulas, but I am hoping for a more elegant, Crystaline solution.

Thanks for any ideas.

 

[IdoMillet]

If you group by that field, where do you want all the cases with a Null value to go? In other words, why shouldn't they all belong to one NULL group?

Perhaps if you give a concrete example it would be easier to understand.

Cheers,
- Ido

ixm7@psu.edu

 

[todder]

Each null record should be diplayed as one line, or in a group of one record depending on how we solve the problem.

 

[IdoMillet]

OK, Let's assume these records have a unique column called {rec_id}

Create a formula of:

IF IsNULL({my_table.my_column}) THEN {rec_id} ELSE
{my_table.my_column}

This would produce what you had in mind since now each Null record would belong to its own group.

Cheers,
- Ido ixm7@psu.edu

 

[todder]

Doesn't appear to be a unique identifying column for each record.

My best idea so far is to create a global counter variable:

global numbervar aNum := 0;

Then create formula called groupingFormula:

global numberVar aNum;
if isnull(myTable.myField) then (
    aNum := aNum + 1;
    totext(aNum);
)
else
    myTable.myField;

And use this formuLa as the field to group on. Each null record gets a unique string value, so each null record will end up in its own group.

I wonder if this is the best possible way?

Thank you idoMillet

 

[IdoMillet]

Here's an alternative solution:

Group by the original column:
{my_table.my_column}

Suppress the detail section with:
NOT IsNULL({my_table.my_column})

Suppress the group header & footer with
IsNULL({my_table.my_column})

Cheers,
- Ido
ixm7@psu.edu