I have read a couple of knowledge base articles but I am not completely clear on how to link sub-reports to the primary reports once the data has been exported to the Blackbaud Report Database.  I'm very familiar with Crystal Reports development, but I could use some pointers about what fields to link.

I am developing a Prospect/Proposal report.  The issue that I am running into is that some proposals have multiple solicitors.  The multiple solicitors are causing duplicates when the report runs.  I want to put the solicitors into a sub-report and join it back to the main report.  The table with the solicitor information has been called CnPrProp_2.  It contains the fields:

CnPrProp_2_IDKEY, ..._Solicitors, ..._LINK, ...Note_1_LINK

How do I link that table into the main report?  Can I just link CnPrProp_2_LINK from the sub-report to the same field in the main report?  If not, do I need to include the Cn table in the sub-report and then link Cn_IDKEY from the sub-report to Cn_IDKEY in the main report?

Thanks in advance for any help.

I posted a general description on how to do this at Blackbus http://www.blackbus.org/forum/crystal-reports/4344-multiple-phone-number-2.html#post22520

A lot depends on which tables are in the main report and which are in the sub-report.  In your case, you have the following tables:

Cn = Constituent
CnPr = Prospect Info
CnPrProp_2 = Proposal Info

Possibly links are

Cn.CnPr_LINK = CnPr.CnPr_LINK  -- Linking the constituent in the main report to the prospect info in the subreport.
CnPr.CnPrProp_2_LINK = CnPrProp_2_LINK -- Linking the prospect info in the main report to the Proposal Info in the subreport.

You don't want to link it to the same field (in the same table), you want to link it to the field in the parent table with the same field name.  If you link it to the exact same field, it won't resolve your duplicate problem.

Drew

Drew,

Thank you for the concise response.  I was able to link the solicitor sub-report to the main report and get rid of the dupes.  Now I am running into a problem with proposal notes causing duplications.  I followed what I think is the logic of the LINK fields, but I am still getting dupes.  I am going to try to insert a screen shot of the sub-report linking, but incase that doesn't work I will type it out here.

In the Field To Link To section I am linking to CnPrProp_2.CnPrProp_2Note_1_LINK.

From the subreport I am linked to that field with CnPrProp_2Note_1.CnPrProp_2Note_1_LINK.

(Unfortunately the forum only supports linking to an image URL.  I don't have anywhere to host the image.)

If the linking is correct, what else may be causing dupes?  Does it have to do with exporting multiple proposals for each constituent record?

I have half answered my own question.  I included the CnPrProp_2 table in the subreport and joined the CnProp_2Note_1 table to that.  Then I joined CnPrProp_2 to CnPr in the main report.  That worked to get rid of the duplication of the proposals.  Now I have a new problem.  Each proposal shows the notes from seperate proposals.

For example, a constituent has three proposals.  Each proposal has one note attached to it.  When the report runs, it will show one proposal with three notes.

At this point I think that I either have a linking problem, or I need to include some sort of condition in the Select Expert to limit the notes to the corresponding proposal.

Any suggestions?

There are a number of sites that offer free image hosting.  I primarily use Photo Bucket.

Is the duplication occurring in the main report or the sub-report?  What other tables in that part?  What information is duplicating?

Any time that you have a one-to-many relationship, you have the potential for duplicate information.  If you have multiple one-to-many relationships, then you compound this problem.  Ideally, you should only have one one-to-many relationship on the main report and you will generally, but not always, want to group on the one side of the relationship.

Drew

David Armstrong:

I have half answered my own question.  I included the CnPrProp_2 table in the subreport and joined the CnProp_2Note_1 table to that.  Then I joined CnPrProp_2 to CnPr in the main report.  That worked to get rid of the duplication of the proposals.  Now I have a new problem.  Each proposal shows the notes from seperate proposals.

Drew

Thanks for mentioning grouping.  I think that is going to solve the problem.