Help needed to write Crystal Report using formulas with parameters

[ntbs2012]

Good day. I am using Crystal Reports 2008. I would appreicate any help that you can give me. I know my way around Crystal, but am certainly no expert. I have created a report for our accounts
payable to use when they do an Electronic Fund Transfer. The report has 3 paramaters:
Vendor ?Vendor Number
Check number ?Cheque number
Date check was printed.?Cheque date

With that information, I am able to display the invoice numbers paid on that check to that vendor, on that date. That report works fine.
However, there is a field that I also need to display that I am having trouble with. The field is called "apchc_comments" and is contained in a table called "ap_cheque_comments". There are 3 other fields in that table labelled "apchc_inv_internal_seq", "apchc_transint_seq", "apchc_clearcomments_log". The only field that appears linkable is the "apchc_comments" as there is a table called ap_cheques, that has a field called "apch_comments". I have linked both fields.

What I want to do is display the "ap_cheque_comments.apchc_comments" if the field is not empty, and have it list the comments below the invoice being paid.

However, if I insert that field into the report, it causes the invoice number field to duplicate itself countless times, and the check total increases as well, since not all invoices have a comment associated with them.

Could I write an IF, THEN, ELSE formula that looks for the Parameters for the check number, and the check date, and display the apchc_comments in the report if the field is not empty, and if it IS empty, just display nothing?
I hope that I was able to make myself understood. I am not a programmer by any means, just a fairly knowledgeable support person in our IT dept.
thanks,
Norm

 

[hilfy]

You're getting multiple records because the way you're linking to the comments table is pulling in multiple comments records per check.

What's in the apchc_comments field in the ap_cheques table? Is the apchc_comments a number or a code of some sort that you can use to link between the tables? If not, then I suspect that you need to link on one of the "...seq" fields. Do you have the ability to look at the data in a tool outside of Crystal to see what the correct linkage between the two is?


Based on the name of the 'apchc_inv_internal_seq' field, I suspect that this field might link to an invoice number, but without knowing the structure of your data, I can't tell for sure.

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com

 

[ntbs2012]

Thank you for your reply. Based on your suggestion, I was able to succesfully link the apchc_transint_seq field in the ap_cheque_comments table to the ap_cheques.apch_internal_seq field in the ap_cheques table.
The fields have identical values for the cheque numbers. And I can now insert the ap_cheque_comments.apchc_comments into the report, but I get every comment.
I need to filter it to only show the comment based on the Vendor ?Vendor Number,
Check number ?Cheque number, and Date check was printed.?Cheque date that the user chooses initially.
I have tried using a formula as shown:
{ap_invoices_details.apid_trans_date}={?Cheque date} and
{ap_invoices_details.apid_ref_num} = {?Cheque number} and
{ap_invoices_details.apid_supplier_id} = {?Vendor Number} and
{ap_cheques.apch_internal_seq}={ap_cheque_comments.apchc_transint_seq}
but I still get multiple comments for each invoice number.

Do you think I'm on the right track, or am I not giving you enough information?

 

[hilfy]

I think you're on the right track, but not knowing how that comments table is set up in relation to the check table, I'm not sure what else you might need in your link to get just the right data.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year

http://www.decisionfirst.com