duplicate id numbers

[begley]

I have two oracle personnel files, linked by ID number. The 1st file has person info, the 2nd has work and home phone numbers. I want to print everyone's info all on 1 line with both work and home phone. I am getting two lines for each person.

Thanks Amanda

 

[Madawc]

Do you mean that each person has one personal details records and two sets of phone details, one for home and one for work?

If so, you could group by ID on the phone details, and attach the personal details. Suppress the detail line and show the details in the group header or footer. Use the command Next({phone.detail}) to get the home or work detail, whichever sorts second.

Madawc Williams
East Anglia, Great Britain

 

[begley]

Thanks for the reply, but I am sorry to report that I could't get it to work. I am sure it is me not doing what I should be doing. I am just starting to use Crystal. I did group by ID number (and yes, there is one personnel record for two or three phone number records, ie; work, home, pager,cell) and put it in the groupheader that worked, but I only get one phone number. Also I did not know where to use the "Next" command.

Thanks Amanda

 

[Naith]

From what you have described, I don't get why you cannot do this:

Details: {File1.Name} {File2.WorkNo} {File2.HomeNo}

without the need for any groups or anything. Can you explain how you are currently laying fields in your report which is giving you two lines per person? Or if what you're saying is that the 2nd line is a duplicate of the first, then you need to address your linking strategy between the two files.

Naith

 

[begley]

In the oracle table for the phone numbers, each record has only one phone number in it with the type of phone such as H for Home, W for Work. I think the table should have been set up to have all the phone numbers in one record, but I didn't set up the table. The way it is now there could be up to 4 records in the phone table for each id number to one record in the personnel table.

 

[lbass]

You should be able to do this with a variable. Group on {table.personID} and then create three formulas:

{@resetphone} to be placed in group header:

whileprintingrecords;
stringvar phone := "";

{@phonedetail} to be placed in the detail section:

whileprintingrecords;
stringvar phone := phone + {table.phone}+" (" + {table.phonetype}+&quot" + ", ";

{@displayphone} to be placed in the group footer:
whileprintingrecords;
stringvar phone;
phone [1 to (length(phone)-2)];

Place name and other information in group footer along with the {@displayphone}. Multiple phone numbers for one person will appear on this one line.

-LB

 

[Madawc]

I'm surprised the earlier suggestions didn't work, but here's another possibility.

In Crystal 8.5, you can process the same "table" twice using an alias. Try this with the phone numbers and do 'select' on each, one just for H and the other for W. Then you should be able to bind them to the personal details.


Madawc Williams
East Anglia, Great Britain

 

[Naith]

I'm still a little bit confused.

It doesn't matter how many tables you're using. As long as you link them all accurately, and group by your unique Person ID field as previously suggested, you should be able to put table1, 2, 3, ...n fields on the same detail line.

Confirm that you have done this and state what problems you're getting back, if any.

Naith

 

[Reebo99]

Naith:

Let me help you out here..... As I understand this the data is not stored in :

{File1.Name} {File2.WorkNo} {File2.HomeNo}

as you previously advised, but :

{File1.Name} {File2.Number}

begley:

Use the solution provided by lbass, it will give you what your after.


Reebo
Scotland (Sunny with a Smile)

 

[kenhamady]

Try this:

Write 2 formulas:

/@home
if {type} = "H"
then {Number}
else ""

/@Work
if {type} = "W"
then {Number}
else ""


Place these on the details band and hide it. Group by the person's ID and hide the Group Header. Do a maximum of each formula for the group. You will need the person's name on the Group Footer.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[Reebo99]

Ken:

begley wrote:
"The way it is now there could be up to 4 records in the phone table "

Your solution won't work if there are 2 records of type "H".

begley:
Again, use the solution provided by lbass....

Reebo
Scotland (Sunny with a Smile)

 

[Naith]

Reebo,

Thanks for trying to explain earlier. I'd obviously not grasped the initial scenario very well.

Ken's solution does work, if you mimic the approach to cover "P" - pagers, and "C" - cells. lbass' solution will also work, but will not allow you to indicate which numbers are work/home/cell/pager related.

Naith

 

[kenhamady]

Actually it WOULD work. If there are two work numbers it will print one of them.

But his description doesn't indicate multiple records of the same type, just other types that he doesn't want to show. He could show all types by writing a formula for each type he wants to show.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[Reebo99]

Ken:

Whoops....sorry about that.

Regardless of whether or not there SHOULD be 2 work numbers doesn't take away from the fact that IF there are 2 data items of "H" then it would only show one of them. I like to point out the limitations so the people who ask these questions get a full response on when they can and can't use the solution.

Naith:

lbass has already allowed for the Phone Type in the solution :

whileprintingrecords;
stringvar phone := phone + {table.phone}+" (" + {table.phonetype}+&quot" + ", ";




Reebo
Scotland (Sunny with a Smile)

 

[Naith]

You're absolutely right regarding the phonetype. I stand corrected.

 

[Reebo99]

Naith,

I thought you would have realised by now...I always am

Except when I'm not, of course.....

Reebo
Scotland (Sunny with a Smile)

 

[begley]

Okay everyone I finally got back to my report and I did what lbass suggested. It worked with only one glich. Here is a sample of the data:

rec 1 H (317)555-1211, W (317) 555-8459, P (317) 555-9898
rec 2 H (317)555-1234, C (317) 555-7689, P (317) 555-1267
rec 3 P (317)555-7845, H (317) 555-8467, W (317) 555-8765

I believe what is happening is it is bringing the records in the order they are being read. For easier reading, I would like the 1st number to always be the home, 2nd pager, etc. What if I just want to bring out the home and pager numbers on the report?

Thanks in advance. I am learning!

 

[lbass]

Go to report->sort records and enter {table.phonetype} as your sort field. This should put the entries in order.

If you only want home and pager numbers, then use that in your record selection formula:

{table.phonetype} in ["H","P"]

-LB

 

[begley]

THANKS LB.. That worked!

I continue to need help though. Now that I have the phone numbers the way I want, the order of the report is not correct. It should be in alphabetical order by name. I have in the record sort order the name and then the phone as the fields to sort by, but the 1st line shows the group id number and won't let me change that! Can you advise me how to get the report in order by name?

Thanks

 

[lbass]

You can either replace your existing ID group with a group based on the concatenation of the person's name and ID (a good idea if you have a large number of people in your database with the potential of duplicate names):

{table.lastname}+", "+{table.firstname}+" "+totext({table.personID})//remove the "totext" if the ID field is already a string instead of a number

...or you can create a higher order group based on the person's name alone. Once you have added the name group, go to report->change group expert->and use the direction key to make the name group the highest order group. Names will then sort in alphabetical order. And I think you meant {table.phonetype} as the sort field within groups, right? You want to sort the "H" and "P", not the number itself.

-LB