Crystal Reports - Problem with Conditional Note 1

[lm1340]

I am trying to build a custom receipt and I am having issues conditionally placing a note on the receipt.

If customer made multiple transactions during a specified time period, I want all transactions to be on the receipt.
If the department of any of the transactions is "XYZ," I want to add an additional note to the entire receipt, not just on the transaction.

Every time I try to enter the condition into an "If- Else" statement, I'm getting bad information. Even when the database tables show that the department is changing, if I add the database field to the crystal report, it is always showing the same value. It recognizes under "Browse Data" that there are multiple values that it could be, but in output is always putting the first value.

Any clue what is going wrong? I appreciate any help!

 

[CoSpringsGuy]

Do you have groups in this report? Maybe for customer?
If so change the formula you are using to

If {yourfield} = "XYZ" then 1 else 0

Then create a formula similar to this and place in the group header or footer

if sum({yourfield},{fieldgroupedon}) >0 then "Text of the note you want to add" else ""

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[lm1340]

Yes, I have it grouped by customer and by transaction.

Where do I need to change the formula to If {yourfield} = "XYZ" then 1 else 0?

Right now it is calling all departments "XYZ" even when they aren't.

Thanks for your help.

 

[CoSpringsGuy]

ok i see that was a little unclear .. my apology

Create a new formula called xyz (or whatever you want to call it

If {yourdeptfieldfromtable} = "XYZ" then 1 else 0

Create another formula

if sum({@xzy},{fieldgroupedon}) >0 then "Text of the note you want to add" else ""

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[lm1340]

I'm still having the problem where it is calling all departments "XYZ," even when it isn't.

The report is grouped on (1-primary) Customer and (2- secondary) Transaction.

I created a formula:

I created formula:

I placed the new formula under the Customer group header/footer. Since I want to put the note per customer if any of their transactions have the departmental attribute of "XYZ." The customer can have multiple transactions across multiple departments.

I am getting the note on every receipt in the series, even though I know most of them aren't from department "XYZ." And when I look into the "Browse Data" of the Department database field, more than 1 department is listed.
I also have a question regarding the second formula: won't the sum always be greater than 0 since I will always have a customer and/or transaction??

Forgive me- I have no formal training in this, so this may all just be my own ignorance.

 

[CoSpringsGuy]

Trying to understand your issue...

If you group by #1 Customer and #2 Transaction then drop the department field from your data table in the detail section are you saying they ALL show XYZ?

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[lm1340]

That is correct.

 

[CoSpringsGuy]

Do you have multiple tables? Are they linked correctly?

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[lm1340]

I can't see any issues.

 

[CoSpringsGuy]

Are you using a record select formula? if so, please show it

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[lm1340]

I didn't do any record select, since I wanted all records to be included... I just need to add an additional note for a specific department.

 

[kenhamady]

It sounds like the main problem is that you are only getting one department and you don't know why. You can't get the note right if the department field is not right. So, go to the database menu and select "Show SQL Query". Copy the SQL shown there and paste it in your next post exactly as it appears in Crystal.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[CoSpringsGuy]

beat me to it Ken.. Im not sure I would have thought to ask but I was wondering what the query looked like.

_____________________________________
Crystal Reports 2011 and XI
Intersystems Cache 2012 ODBC connection

 

[kenhamady]

Oh, and lm1340, make sure you are taking the SQL AFTER you add the field and start getting the bad information. That is the SQL that should point us to the problem.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[lm1340]

The field name that corresponds with the department is: GfFnds_1FnAtrCat_1_Descr


SELECT `GfCnBio`.`GfCnBio_ID`, `GfCnAdrSal`.`GfCnAdrSal_Addressee`, `GfCnAdrPrf`.`GfCnAdrPrf_Addrline2`, `GfCnAdrPrf`.`GfCnAdrPrf_Addrline1`, `GfCnAdrPrf`.`GfCnAdrPrf_City`, `GfCnAdrPrf`.`GfCnAdrPrf_State`, `GfCnAdrPrf`.`GfCnAdrPrf_ZIP`, `GfCnAdrSal`.`GfCnAdrSal_Salutation`, `Gf`.`Gf_Gift_ID`, `Gf`.`Gf_Date`, `Gf`.`Gf_Amount`, `Gf`.`Gf_Fund`, `Gf`.`Gf_Type`, `Gf`.`Gf_Installmnt_Frqncy`
FROM ((((`GfCn` `GfCn` INNER JOIN `GfCnAdr` `GfCnAdr` ON `GfCn`.`GfCnAdr_LINK`=`GfCnAdr`.`GfCnAdr_LINK`) INNER JOIN `GfCnAdrSal` `GfCnAdrSal` ON `GfCn`.`GfCnAdrSal_LINK`=`GfCnAdrSal`.`GfCnAdrSal_LINK`) INNER JOIN `GfCnBio` `GfCnBio` ON `GfCn`.`GfCnBio_LINK`=`GfCnBio`.`GfCnBio_LINK`) INNER JOIN `Gf` `Gf` ON `GfCn`.`GfCn_LINK`=`Gf`.`GfCn_LINK`) INNER JOIN `GfCnAdrPrf` `GfCnAdrPrf` ON `GfCnAdr`.`GfCnAdrPrf_LINK`=`GfCnAdrPrf`.`GfCnAdrPrf_LINK`
ORDER BY `GfCnBio`.`GfCnBio_ID`, `Gf`.`Gf_Gift_ID`


SELECT `GfFnds_1FnAtrCat_1`.`GfFnds_1FnAtrCat_1_Description`
FROM `GfFnds_1FnAtrCat_1` `GfFnds_1FnAtrCat_1`

 

[kenhamady]

These tables look like MDB extracts from Raiser's Edge. The second SELECT indicates that you are pulling Depts from a separate MDB extract. Is it possible that you are using the wrong MDB to pull in the Dept Description?

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[lm1340]

It is MDB extracts from Raiser's Edge. I don't think it could be pulling from a separate MDB-- all the data was pulled into a single MDB extract out of RE. I recreated the export from RE as a .csv export and it is correctly pulling the department field onto the proper transaction, but not doing to in crystal.

 

[kenhamady]

I suppose the other possibility is that you didn't link the `GfFnds_1FnAtrCat_1` table at all. If you go into the database expert and look at the LINKS tab, what is the link between the `GfFnds_1FnAtrCat_1` table and the others?

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/

 

[lm1340]

 

[kenhamady]

Your bottom 4 tables are not connected to the rest of the query, so whenever you add a field from one of those tables you probably get a cartesian product of the two data sets (every record from one matched to every record from the other). You probably need to join GF to the GFFunds_1 table. I would look in GF for a field that has the same name as the yellow tagged field in GFFunds_1 and link those (dragging FROM GF to GFFunds_1. If there is no matching field then there might be another table in the extract that you need to add. Usually the automatic linking gets everything right in these extracts. Then I would change all the joins to left outer joins. That last step is something I only find necessary in these extracts.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/