using instr to display a field if not blank

[ntbs2012]

Good day. 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 shows this field {ap_cheque_comments.apchc_comments } if it is present for the particular cheque that was issued.

If InStr({ap_cheque_comments.apchc_comments}, "" ) =1 then {ap_cheque_comments.apchc_comments}

However, when I insert this formula into the report, it duplicates the amounts for each invoice that is being paid.
So a one page report turns into a 13 page report, and the totals increase accordingly.

I have tried inserting the formula in various parts of the report (page header, details, page footer) and it still cause the report to have duplicate values. Any ideas or assistance would be appreciated.

 

[MCuthill]

ntbs2012,

The duplication makes me think it is more of an issue with your Table Joins than the formula itself, but hard to say based on what you have provided. Can you provide more detail around the structure of the data and what is used on the report?

That aside, I have never seen InStr() used to look for Nulls, which *could* be a concern, but don't know for certain. How I have always done checks of this nature is like:

IF ISNULL({Table.Field}) THEN "" ELSE {Table.Field}

However, this is dependent on the data structure. I would *assume* that you have "Cheque" table, and a "Comments" table, and the comments table is only populated if a Cheque has a comment -- otherwise, I am unsure why you would need to build this check into the report, as an empty space would appear if a (Comment) field on the same table as the other cheque data was null.

Another consideration is you have a Inner Join between your Cheque and Comments table (again, assuming the data structure)... if you change this to a Left Outer, you can likely disregard adding the conditional functionality, as the field will be blank when no Comment is found with a Left Outer join.

Please ALWAYS ensure to provide a description of the data structure/environment as it can have a significant impact of potential solutions.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[ntbs2012]

I don't know if the following information is pertinent or not. The first lot of data is the SQL Query without the "Comment" formula inserted into the Report Footer:
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'
ORDER BY "ap_invoices_details"."apid_supplier_id"

Now here is the query with the "comment" inserted:
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", "ap_cheque_comments"."apchc_comments"
FROM (("sisl_data02"."dbo"."ap_invoices_details" "ap_invoices_details" INNER JOIN "sisl_data02"."dbo"."ap_cheques" "ap_cheques" ON "ap_invoices_details"."apid_supplier_id"="ap_cheques"."apch_supplier_id") INNER JOIN "sisl_data02"."dbo"."suppliers" "suppliers" ON "ap_invoices_details"."apid_supplier_id"="suppliers"."su_supplier_id") INNER JOIN "sisl_data02"."dbo"."ap_cheque_comments" "ap_cheque_comments" ON "ap_cheques"."apch_internal_seq"="ap_cheque_comments"."apchc_transint_seq"
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'
ORDER BY "ap_invoices_details"."apid_supplier_id"
There are extra lines in the query after the "comment" was inserted, and extra INNER JOINS, but what this means, I have no idea.If there is further information that you need, please advise & I will try to get it.
Thanks,
Norm

 

[MCuthill]

ntbs2012,

I'm not an SQL expert, but from looking at this I think that your comment data is on a separate table from your other report information and I would suspect that a record only exists in the comments table IF there is a comment, and nothing exists for a parent records when there is no comment. As you are getting some wild duplication, I am thinking a join is missing and you will have to talk to your Database Administrator to find out what the other Key might be. I would think there is an element that the tables should be linked on that they are not, something that would attach it to a specific cheque. I see it is linked/joined on a Sequence ID - is this unique? The link on supplier ID, I am unsure of... but without knowing the data structure, it is hard to assume from the SQL.

Instead of the SQL, I was looking for something about the layout of the report itself... like what Grouping you have on and how the data "should" interact. The SQL is only really helpful if you know the actual data structure (ie: is Cheque linked on sequence, a cheque number, invoice number, or a combination of various ID fields).

My apologies for the vague answer on this one, but hopefully helps you out in your troubleshooting.

Cheers,



Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."