Gift Receipt

[Former Member]

Hopefully someone can help me with this issue.

I'm using Crystal Reports XI and have modified a gift receipt that was developed by my predecessor. The subreports are located in one large detail section. The main issue I am having is that the subreport is duplicating data on the main report. I have tried moving the subreport into separate group headers/footers but then the main report has all the subreport data listed for each constituent (even those it should not apply to). I have also tried splitting each subreport into a separate detail line but the duplication on the main report still happens.

I also have some basic SQL Expressions to pull the correct nametype into formulas that are in the main report:

Honor Roll Name (working properly): (select "NAME_HONOR_ROLL"."NAMEFORMN" from "MILLORA"."NAME_HONOR_ROLL" "NAME_HONOR_ROLL" where "NAME_HONOR_ROLL"."NAMEID" = "GENERIC"."ID_NUMBER")

Preferred Name (working but occasionally get a blank name): (select "NAME_PREFERRED"."NAMEFORMN" from "MILLORA"."NAME_PREFERRED" "NAME_PREFERRED" where "NAME_PREFERRED"."NAMEID" = "GENERIC"."ID_NUMBER")

Formula:

@Name to Use: If {GIFTS.GIFTYESNO} = 'N' then {NAME_PREFERRED.NAMEFORMN} else {NAME_PREFERRED.NAMEPLURAL}

A secondary issue is that the wrong nametype is pulling into the @name to Use. In the result set attached, you will see that sometimes the honor roll name (has years or formal title as suffix) is where the Preferred name should be even though I have a formula dictating where the Preferred name should be.

Let me know if I can provide more information. See attached result set (last page is where the duplicate resides).

Thank you.

[abhilash_kumar · Accepted solution]

Hi Hiruy,

Is the Subreport in question 'related' to the data that prints on the Main report's details section?

If yes, then you would need to 'link' the Subreport with a field or fields from the Main Report so that each iteration of the Subreport prints separate data based on the details section records of the Main Report (More like a correlated subquery).

Right-click the Subreport > Select Change Subreport Links > Select a field from the pane on the left and move it to the right > From the drop-down at the bottom right that says 'Select data in Subreport based on field', choose the corresponding field from the dropdown that can link to the field you just selected'.

If the Subreport accepts prompts then you can directly link a value to the prompt so that the filter is processed on the database side. This option is towards the bottom left of the same window (Subreport parameter field to use).

-Abhilash

[Former Member · Accepted solution]

Abhilash,

Thank you for the prompt response. Yes they are related and were setup as linked subreports. I checked the subreport links and it is as follows:

Subreport 1 - Linked Name (Matching Gift): Gifts.Giftkey --> Gifts.Giftkey

Subreport 2 - Linked Name (Gifts in Honor or Memory of)- Gifts.Giftkey  --> Gifts.Gifthnrkey

Subreport 3 - Non-Consituent (Gift in Honor or Memory of) - Gifts.Gifkey --> Honoree.honlinkkey

Subreport 4 - Quid Pro (Tax Statement) - Gifts.Giftkey --> Quidpro.Quidlnkky

My issue is that instead of printing Subreport 4 Quid text one time, it is printing 2-3 times. It should also be noted that the Group is being selected by Gifts.Gifttrnum (transaction number).

See attached.

[abhilash_kumar · Accepted solution]

I see that all Subreports all placed in the same 'Details Section'. Subreport 4 alone cannot print 2-3 times unless the Main Report's details section also prints those many times.

I suspect that the records 'inside' Subreport 4 are duplicating and you might want to check the query in this Subreport to make sure it returns unique records.

-Abhilash

[Former Member · Accepted solution]

Ok, what would cause the details section to print 2-3 times when the Group is being selected by a unique number (Transaction Number)?

For the record, subreport 1 and 4 are both duplicating. I've edited Subreport 1 to only pull the preferred name from a view which only has one preferred name per person (Name_Preferred_Nameformn). See images attached..

[abhilash_kumar · Accepted solution]

How many records does the Main Report return?

If it returns 3 records, then the details section (and hence the Subreports on the details section) will print thrice.

-Abhilash

[Former Member · Accepted solution]

The report returns the correct amount of receipts (1 per person) but when a person has a matching gift, quid pro quo, etc then the subreport is duplicating 3 times. When I tried moving the subreports into separate group headers or footers, then even when a matching gift, quid pro quo was not applicable it still printed once per user.

[abhilash_kumar · Accepted solution]

Double-click the Subreport > Once you're inside the Subreport click Refresh > You should be prompted for the fields you've linked on > Enter the values > How many records does the Subreport return?

-Abhilash

[Former Member]

It only returns one record per constituent. Since the Group is being selected by Gift Transaction Number (Gifts.gifttrnum) which is unique, only one transaction row should return for the main report which is what happens. The subreports link by a giftkey which is also unique for each row.

[abhilash_kumar]

Would you be able to share the report with saved data please?

-Abhilash

[Former Member]

Yes of course, I have created a dummy table called Receipt Gifts and changed the Datasource location in the Generic Table to point to that table.

Where can I attach the file since .rpt isn't a format that is allowed on this forum? Can I send it via email?

[Former Member]

Unfortunately I am running into issues saving data with the report. I am getting a 'Fail to save document' error followed promptly by a 'Not Supported' error.

[abhilash_kumar]

You may send it to my e-mail (e-mail is in the profile).

When you try to save with data, make sure you browse all the way to the last page and then save.

-Abhilash

[Former Member]

I sent instructions on how to log into my machine remotely. Did you receive them? I cannot save this file for some reason. Please I need to make this report run correctly asap.