GROUP BY problem..

[nikol]

Hi,
I have created a report where I have a GROUP BY SECTION with uid & DETAIL SECTION is supressed.
LIKE:
UID 1 PHONE 510-897-8976 STATE CA
----------------------------------------------------------
UID 2 PHONE 925-976-9087 STATE CA
---------------------------------------------------- & SO ON...

NOw I want to insert a new column "name" in the existing report.
UID name
1 Peter
1 408-976-8778
1 jill
1 789-9765
2 Mike
2 650-987-9766
2 Jelly
& so on...when I try to insert"name" in the report in GROUP SECTION its giving me only "Peter" rest nothing is coming..
I want whole list as:
UID 1 NAME PETER; 408-976-8778 Jill; 789-9765....

I can not unsupreess the detail section..
Any help..What should I do?

 

[Turkbear]

Hi,
Not sure exactly you are doing..The simple addition of 1 more field in the Group Header is not usually the cause of a problem..Could it, perhaps, be overlaying the others?

I am assuming, that in the design mode, you just selected the name field from the explorer and placed it in the GH alongside the others..







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[nikol]

I have a report where I want to add new field
NAME PETER; 408-976-8778 Jill; 789-9765....
in this format but in db table it is in
UID NAME
1 peter
1 408-976-8778
1 jill
1 789-9765
When I insert this field from explorer into the GROUP HEADER
of UID its giving me only "peter"nothing else. Is there any other way or any formula to do this..I hope I'm able to explain my problem

 

[lbass]

Are you saying that both names and phone numbers appear in the name field??? I think you must mean they are coming from separate fields. You could create a formula like the following:

//{@reset} to be placed in the UID group header:
whileprintingrecords;
stringvar nameno := "";

//{@accum} to be placed in the detail section (even though suppressed):
whileprintingrecords;
whileprintingrecords;
stringvar nameno := nameno + {table.name} + " "+ {table.phone}+ "; ";

//{@display} to be placed in the UID group footer:
whileprintingrecords;
stringvar nameno;
left(nameno,len(nameno)-1)

-LB

 

[nikol]

Name & Phone number r in same field as varchar.
The last formula is giving error
"string or less then 0

 

[lbass]

How do you know what phone number goes with what name? And are either the name or the phone number ever null?

-LB

 

[Turkbear]

Hi,
Just to be sure I understand that strange data model:
To get ALL the info for Peter (UID 1 - which apparently is also info for Jill ) you will need to read 4 records?
Then somehow determine which is Peter?

What purpose does the UID field serve ( since it is not a unique identifier)
- what are the field names?

( BTW, If your group by UID, then only the first NAME value will be shown in th GH for UID - In the Details for that UID you will see all of the records with that UID)







To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[nikol]

UID is the CLIENT_ID & name are its references (like who referred them)
CLIENT_ID (ie UID 1 is referred by 2 persons peter & jill
& I have there phone numbers.) Like
UID Question_id NAME
1 556 peter
1 601 408-976-8778(Peter'sphoneNumber)
1 556 jill
1 601 789-9765(Jill's phone number)
I know somebdy designed the database very badly. I cant do anything about that.
I have 1 more field Question_id
Question_id Name
556 Referral's name
601 referral's phone number
May this will help to solve the problem..

 

[nikol]

Hi Ibass,
When I run the formula it gives the error: "String length is less then zero or not as Integer

 

[nikol]

Hi,

I got my half of the answer.
UID NAME
1 peter;715-987-8656
1 jill;910-987-8778
2 mike;510-758-9767
2 mott;345-987-0876
Now is there any way I can do the following

UID NAME
1 peter;715-987-8656 jill;910-987-8778
2 mike;510-758-9767 mott;345-987-0876

 

[lbass]

Not sure what your formula was to get that, but starting afresh, if you use formulas like this, you should get the correct result:

//{@reset} to be placed in the UID group header:
whileprintingrecords;
stringvar nameno := "";
numbervar cnt := 0;

//{@accum} to be placed in the detail section (even though suppressed):
whileprintingrecords;
numbervar cnt;
stringvar nameno := nameno + {table.name} +
(if remainder(cnt,2) = 0 then "; " else ", ");

//{@display} to be placed in the UID group footer:
whileprintingrecords;
stringvar nameno;
left(nameno,len(nameno)-2)

This would give a result like:

peter, 715-987-8656; jill, 910-987-8778

-LB