Need help to display a field if criteria is true

[ntbs2012]

Hello. I am trying to add a field to a report. This field might be blank in some instances, or in others, contain text.
I have created a formula that I hoped would show this field {ap_cheque_comments.apchc_comments } if it is present for the particular cheque that was issued.
Here is the formula, but I get an error saying a boolean is required at the very end:

{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
IF {ap_cheques.apch_internal_seq}={ap_cheque_comments.apchc_transint_seq}
THEN {ap_cheque_comments.apchc_comments}
ELSE " "
Is it OK to use parameters in the formula? I wasn't sure.
Thanks in advance for any help that can be provided.

 

[ntbs2012]

Actually, the more I was thinking of this, I don't think I need the parameters in the formula, since the User has already entered that criteria.
Maybe all I need is something like:
IF {ap_cheque_comments.apchc_comments}> " "
THEN {ap_cheque_comments.apchc_comments}
ELSE " "
I will have to try this tomorrow at work.
I'll let you know.

 

[IanWaterman]

Null and "" are not the same, ifyou really have null then use this

IF isnull({ap_cheque_comments.apchc_comments})
THEN {ap_cheque_comments.apchc_comments}
ELSE " "

Ian

 

[ntbs2012]

Thanks, Ian. How can I tell if I really have null in that field.
I tried your formula- created a formula called "comment", and inserted it in the report, in the detail section,
and it doesn't show any comments, but duplicates the value of the invoice on the report by adding extra lines for each invoice paid, and the one page report is now 12 pages.
I also tried inserting the "comment" formula in the Report Footer, but same thing happens.

I also tried my idea
IF {ap_cheque_comments.apchc_comments}> " "
THEN {ap_cheque_comments.apchc_comments}
ELSE " "
and I am able to insert the "comment" formula into the details section, but it lists all comments ever made for any cheques ever issued. I need to tie it to the values that have been chosen with the parameters, so it only displays for those parameters.
But if I insert the parameters in the formula, I get an error message saying a Boolean is rquired here and it highlights the IF,THEN,ELSE portion of the formula

{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
IF {ap_cheque_comments.apchc_comments}> " "
THEN {ap_cheque_comments.apchc_comments}
ELSE " "

Any assistance would be appreciated.
thanks,
Norm

 

[IanWaterman]

When you say formula do you mean select statement.

Filters for elect must be either true or false

This
IF {ap_cheque_comments.apchc_comments}> " "
THEN {ap_cheque_comments.apchc_comments}
ELSE " "
will not return true or false

You could use a formula, using your commebts formula
@Comments
IF {ap_cheque_comments.apchc_comments}> " "
THEN {ap_cheque_comments.apchc_comments}
ELSE " "

YOu could then add this to select statement

{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
{@Comments} = " "

Ian

 

[ntbs2012]

Thanks, Ian. I tried that, and after adding to the Selection Record formula, and refreshing the report data, nothing shows at all including what used to show.
Guess it doesn't like that.
Thanks for trying.
Norm

 

[IanWaterman]

Not really sure what you are trying to achieve, can you let us have a sample of raw data as it currently shows and then how you would like it to appear. I am not clear on what you want to filter out.

Ian

 

[ntbs2012]

What do you need in raw data and how do I get it to you? I can do a screen shot, or export to a PDF, etc.

 

[IanWaterman]

Just some fictitious data entered onto TT and then how you want it to look. Just to give an idea of what you are trying to achieve.

 

[ntbs2012]

OK- here is an example of the data that I'm able to show:

Vendor Name: Kennametal Ltd Cheque number: MEFT030813

Date of Transfer: March 8, 2013

Invoice number being paid: 9864583 Invoice amount: $500.00
Invoice number being paid: 9864587 Invoice amount: $310.00
Invoice number being paid: 9864592 Invoice amount: $200.00
Total amount being paid: $1010.00

All of the above is handled with the existing parameters that the user chooses.
However, sometimes there is a comment on the cheque, sometimes not. If there is a comment, I need it to print, otherwise it could say "N/A"
Comment: 9864583 & 9873312 being paid

I hope that this is what you meant by raw data. If not, let me know & we'll go from there. Thank you for your assistance.
Norm

 

[IanWaterman]

Where is the Cheque comments held?

Where do you want to display the cheque.

Comment does not appear to relate to the invoices listed?

Ian

 

[ntbs2012]

Sorry I haven't posted anything until today, but I only work 3 days a week here.
Here is the SQL query used for this report:
SELECT "ap_invoices_details"."apid_supplier_id", "ap_invoices_details"."apid_invoice_num", "ap_invoices_details"."apid_total_amt", "ap_invoices_details"."apid_ref_num", "ap_invoices_details"."apid_chequetype_flg", "ap_invoices_details"."apid_trans_date", "ap_invoices_details"."apid_currency_id", "suppliers"."su_name", "ap_invoices_details"."apid_discount_amt"
FROM "sisl_data02"."dbo"."ap_invoices_details" "ap_invoices_details" INNER JOIN "sisl_data02"."dbo"."suppliers" "suppliers" ON "ap_invoices_details"."apid_supplier_id"="suppliers"."su_supplier_id"
WHERE ("ap_invoices_details"."apid_trans_date">={ts '2013-01-17 00:00:00'} AND "ap_invoices_details"."apid_trans_date"<{ts '2013-01-18 00:00:00'}) AND "ap_invoices_details"."apid_ref_num"='MEFTKENM06' AND "ap_invoices_details"."apid_supplier_id"='kenm'


The cheque comments are in a table called ap_cheque_comments. The field is {ap_cheque_comments.apchc_comments}
The problem that I see with this is that the cheque comment is entered at the time the supplier's invoice is entered in the system, not when the cheque is created. In this case, 6 invoices are being paid on one cheque, but only one invoice had comments. The invoice number is ap_invoices_details.apid_invoice_num.

I created a sub-report with a selection statement {ap_cheque_comments.apchc_comments} = {?Pm-?Vendor Number}
The paramenter is one of 3 that the user chooses in the main report. I put the sub-report in the Page Footer section, but it doesn't display anything. Again, I think that I have to reference the ap_invoices_details.apid_invoice_num but I'm not sure how to go about that.

I feel like I'm getting closer, but it sure is frustrating.

 

[IanWaterman]

Taking your subreport as a stanalone report what filters do you need to enter to get the specific comment you are seeking for a specific cheque.

Alternatively if you can not get the specific comment, if you can come up with a condition to identify the comment you can use shared variables. You might have to consider passing invoice number in shared var to the subreport and then bringing back the comment in another shared var.
NB yoo can only use this if Sub Report is plcaed after the data is executed in main report and then display the shared var from the SR after the SR has executed.

Ian

 

[ntbs2012]

thanks for continuing to try to help me, Ian. I cetainily appreciate it. If I read your post correctly, the use of variables and a formula in the subreport, and having it inserted after the data is executed in the main report is not do-able because the user of the report wouldn't be able to carry this out.
So.....
Here's a formula that I created in the subreport that actually will look at the invoice number to see if a comment was made, and will display it. If no comment was made, it displays N/A.
If {ap_invoices_details.apid_invoice_num}>{ap_cheque_comments.apchc_comments} then {ap_cheque_comments.apchc_comments}
else "N/A"

However, when I insert the subreport into the main one, and set up the field {ap_invoices_details.apid_invoice_num}to link
to, and click on Select data in subreport based on field: }>{ap_cheque_comments.apchc_comments}, nothing gets displayed
in the Page Footer.
I have tried liking to other fields in the main report, but with same results.

What am I missing here?
Norm

 

[IanWaterman]

Where is the SR located. Can you group data by invoice number and place SR in invoice grp header. Even if this is not what you want, do this as a test to ensure SR works properly. As I am guessing that in its present location its not finding the correct invoice number.

Ian

 

[ntbs2012]

The SR was located in the page footer. I inserted a Group Header for the invoice numbers, and then inserted the SR there.
I suspect I don't have the links set up right in the SR, as it only will show the column headings but no data.
Here's a wierd situation. In the CR that I use for the subreport, the invoice number is not the same one as the invoice number in my main report, even though the source field and table are the same.
These other invoice numbers are coming from cheques issued 3 years ago. I have filtered the date to only show since 01/01/2013, but the invoice numbers still aren't the same. I'm giving up for today & going home.
I'll try again tomorrow.
Norm

 

[ntbs2012]

Tried various scenarios yesterday at work, and couldn't succeed in having either the subreport or a formula show the data that I need. I even tried using the main report with it's parameters, as a subreport. I guess that I will shelve this for now, but keep coming back at it on occasion. If I ever get it to work, I will post the solution. Thanks for your help, Ian.
Norm

 

[ntbs2012]

Solved- linking Sub report parameters to main report paramaters.
I finally had success in getting this report to work.
My main report was always OK. But I needed to insert a comment into the main report, only if the comment field contained something.
So I inserted a new SR, with database fields containing the cheque comments, and invoice details. In the details portion, I inserted the invoice number and the comments, as well as the vendor and date of the cheque.
I linked on ?Vendor Name from main report, and selected data in the SR based on the
{ap_cheque_comments.apchc_comments}
I placed the SR in the report footer of the MR.
Then I added 2 parameters in the SR that duplicated the ones in the MR.
So my selection formula in the MR is:
{ap_invoices_details.apid_supplier_id} = {?Vendor Number} and
{ap_invoices_details.apid_ref_num} = {?Cheque number}and {ap_invoices_details.apid_trans_date}={?Cheque date}
and in the SR:
{ap_invoices_details.apid_trans_date}={?Cheque date} and
{ap_invoices_details.apid_supplier_id}={?Vendor Number}

Now, the user has to enter some of the data twice in the initial Enter Values screen, (the date and vendor), but the report returns exactly the information that I need. In retrospect, it all seems simple, but I've danced around this thing for months, with excellent suggestions from Ian and others at other sites.
thanks to all!
Norm