Multi Value Fields - How to List All Values (Urgent help required) 1

[tobypsl]

Hello - I am using CR version 8.5 with a Paradox Database, this is probably fairly simple but my skills are limited, ideally need a solution by monday morning - so any help very much appreciated !!

I have 3 tables.

J00090 Rapps RPHONES
ObjectID ObjectID ObjectID
SearchNo Name Description
Date Searched Surname Num


J00090 contains a list of people that have been found to meet certain criteria.
Rapps contains the names of those people.
RPHONES contains the contact details listed for those people. Each 'Description' eg. mobile, home, work etc is listed as a separate row.

I would ike to generate a report that lists the name of each persom in the J00090 table with all of their available contact info.

The report I currently have creates a row for each decsription as follows

Name Surname Description Num
Joe Bloggs Mobile 0777******
Joe Bloggs Home 01********
Mary Bloggs Mobile 0777******
Mary Bloggs Home 01********
Mary Bloggs Work 012*******
Mary Bloggs Other 0777******

I would like something along the lines of :-

Name Surname Mobile Home Work Other

Joe Bloggs 0777*** 01**
Mary Bloggs 0777*** 01** 012** 0777***


OR

Joe Bloggs 0777**** (mobile) 01*** (home)
Mary Bloggs 0777**** (mobile) 01***(home) 012**(work) etc


Each person has several contact nos. Unfortunately the DB user can call the contact nos. they add anything they like so the Description field will have up to 20 different descriptions - some of which mean the sqame thing eg email and e-mail etc For this reason I would prefer to just list the details as in example 2.

Any suggestions on how to achieve this would be very well received. Thankyou in advance !!

 

[lbass]

Since you prefer the second method, try the following. First insert a group on {table.objectID}, assuming this is the unique ID per person. Then create three formulas:

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

//{@accum} to be placed in the detail section:
whileprintingrecords;
stringvar contact := contact + {RPhones.Num} +" ("+{RPhones.Description}+ ") ";

//{@display} to be placed in the group footer:
whileprintingrecords;
stringvar contact;

Place the name fields in the group footer next to {@display} and then suppress the group header and footer.

Another approach would be to insert a crosstab in the group footer which uses no row, and uses the following formula for the column field:

{RPhones.Num} +" ("+{RPhones.Description}+ ") "

You would then insert a maximum on the same formula as the summary. Then you would suppress the row and column labels and in the customize style tab within the crosstab expert, check "Suppress row totals". Then go to "format grid lines"->uncheck "show grid lines". This might give a more consistent display and also will work if you run into the 254 character limit in 8.5.

-LB

 

[tobypsl]

thanks for the solution lbass - I went with the first option it works very well !! The only remaining issue is that of sorting the data.

The J00090 table has a 'status' field which is either R (reject) H (hold) A (Accept) P(Possible) or Blank if the details have not yet been reviewed. I can cut out the Rejects from the list using the select function - this also cuts out those with a blank field but presumably I can put some form of 'If Is Null' expression in to replace blank fields with an arbitrary letter.

What I would like to be able to do is sort the report table by this status field if possible ? I remember this not being possible with a previous report I had to make. Are there any ideas for sorting groups ?

 

[lbass]

To include the people with null status and exclude those with "Reject", use a record selection formula like:

isnull({J00090.status}) or
{J00090.status} <> "R"

Create a formula {@status}:

If isnull({J00090.status}) then "Not Yet Reviewed" else
if {J00090.status} = "A" then "Accepted" else
if {J00090.status} = "P" then "Possible" else
if {J00090.status} = "H" then "Hold"

You can insert a group on {@status} and toggle it so that it is the outer group (report->change group expert->use arrow keys, or use the mouse on the gray area to the left of the canvas to drag the group so that it is group #1). If you want the groups in a certain order, when you insert the group on {@status}, use specified order and select the options from the dropdown to order your groups.

-LB