Same field ... different data

[BasilFawltytoo]

I've got a report that requires the same field to be printed on one page. There is one field 'Contact'. There are two other fields 'A' and 'B'. One contact is related to other contacts if the value in 'A' is the same as the value in 'B' of other contacts. It is a one to many relationship. No contact can have a value in both 'A' and 'B'. So ... if Bob has '1234' in field 'A' and Mary has '1234' in field 'B' and Jane has '1234' in field 'B' then I'd like the report to look like this:

Mary: Bob
Jane: Bob

And so on for all contacts. Any idea?

 

[lbass]

You could try adding the table a second time. Then link {Table.A} to {Table_1.B}. Group on {Table.Contact} and then create a formula for the detail section:

{Table_1.Contact}+": "+{Table.Contact}

I think this should work, but you also might try the hierarchical grouping option (report->hierarchical grouping options).

-LB

 

[hilfy]

You don't say much about what the actual table structure is behind this data, but I'm going to answer based on some assumptions here.

[u]Table        Fields                   [/u]
Person       Person_Key
             Person_Name

Contacts     From_Person_Key  (your field A)
             To_Person_Key    (your field B)

You can include a table twice in your report by using an alias. So, from my example structures above, you would have the Person table twice and the Contact table once. They would be linked something like this:

Person.Person_Key -> Contacts.From_Person_Key and
Contacts.To_Person_Key -> Person1.Person_Key

Person1 is a second "copy" of the Person table that has been aliased as Person1. When you try to add a table to a report, Crystal will check to see if it's already there. If it is, it will give you a message saying that it is and asking you to give it an alias.

-D

 

[BasilFawltytoo]

Sorry for not being clear. All fields are on the same table. For simpicity sake, lets pretend there are only 3 fields. The table for the above example would look linke this:

Contact A B
Bob 1234
Mary 1234
Jane 1234

The alias idea sounds like it would work though. How would I set that up?

 

[lbass]

If you just go to database->add database and select the table again, an alias name for the table will automatically be generated. The default is to add "_1" to the end of the original table name.

-LB

 

[BasilFawltytoo]

Ok, Alias is set up. The information from the alias table is the same as the original table ... like this

Mary: Mary
Jane: Jane

How do I get it to look at different lines on the same table?

 

[lbass]

You should be linking field "A" from the original table to field "B" on the alias table. Then follow my previous post.
I tested this before responding.

-LB

 

[vidru]

Go to the Database menu > Visual Linking Expert. Hit the 'Clear Links' button to get rid of the default links, then link the tables manually by dragging A from Contacts to B in Contacts_1.

-dave

 

[BasilFawltytoo]

That worked! Thanks guys. I didn't need to group though, just put contact1 and contact1_1 on the same line and supressed if A<>B. The report is extremely slow, however, because there are 50,000 entries, so for EACH contact1, it goes through 50,000 contact1_1 to see if they fit. Would grouping (or any other trick) help that?