SELECT WHERE Formula in Crystal ReportsXI

Does that return a single value?
If so you could use a SQL expression

mlmcc

Please explain in more detail what you are tyring to achieve as this is unclear,

This should return a list of values.  I am working from a flat-file legacy database and am trying to create a list of files that do not have the value "BINGO" in any of their sub-table records.  Most files have multiple records, one or more of which may have the "BINGO" value in this field.  It is like an invoicing db, with one table for the invoice header (Inv_no, client name, address, etc.) and another table with all of the invoice lines on it, including code fields like "BINGO."  

The problem arises in that you cannot simply program it to disallow any records with the "BINGO" field -- you then get the invoice returned without BINGO on it.  I need any invoice with BINGO on it NOT to appear at all.

You can't use SQL in a formula field unless it is a SQL expression and that must return just 1 value not a list of values.

How do you intend to use the list?
You could use the SQL as the source for a subreport and show the list of value sthere.

mlmcc

In the SQL statement above, I can create a subset listing ANY invoice with "BINGO" in it and SELECT only the Invoice file numbers NOT in that list.

Because my legacy database is flat-file and NOT SQL, i have to do all of this in Crystal, rather than creating a view in SQL and choosing from a limited subset in the report.

Do you want this as a list for parameters?
If so just create a COMMAND and include it in your report then base the parameter on the COMMAND

mlmcc

I am trying to create a report listing information regarding  the clients who did not have "BINGO" in the specific field in the subtable.

I'm hoping that you know of a way to create a formula field or group that will allow me to do this.

You can use the SQL above assuming it works in a query analyzer and then use the SQL as the source for the report through a COMMAND.

mlmcc

I'm sorry, I don't know what you mean by create a COMMAND.  Where, and in what language?

Because I am working with a non-SQL-based database, I am not certain where I could create the command.  Is there somewhere in a Parameter field or a criteria field I could put this?  I was trying to create a formula field that would return only the file numbers of the records that had BINGO so that I could exclude those file numbers.  I cannot seem to get that to happen.

Can you create a report against the database?

How do you select the tables?

DO you see ADD COMMAND as an option?

mlmcc

you should still be able to select tables, join them abd add filters using ta selection formula.

how about if I concatenate all the values in each invoice's records then do a suppress if LIKE *BINGO*?  How could I go about concatenating all of those records, each invoice having only one concatenated field instead of a variable number of records?

I do have a working report, so I will try this.  THANK YOU!!

Can you not just sjoin the 2 tables together in the report ?

Your original SQL
SELECT {TABLE_B.FILE_NO}
      WHERE {TABLE_B.FILE_NO}<>(SELECT {TABLE_A.FILE_NO} WHERE {TABLE_A.INFO_CODE} = "BINGO")

could also be represented as

SELECT TABLE_B.FILE_NO
FROM TABLE_B INNER JOIN
TABLE_A ON TABLE_B.FILE_NO = TABLE_A.FILE_NO
WHERE TABLE_A.INFO_CODE <> "BINGO"

If you can do this then this would be the more efficient way of producing teh correct recordset for teh report.

Could you uploada copy of your report on to here I would be intersted to see how the report is designed.

Because the company I work for does some sensitive government work, I cannot upload a copy of the report.  The tools in Crystal are the only tools available to me for report creation and data manipulation.  
The tables have been joined in the report, but when the report is run, requiring that all Invoice records with "BINGO" in an invoice line be suppressed, only the invoice LINES with "BINGO are suppressed, NOT the Invoice itself, which is the desired end-result.  This happens because we are dealing with a flat-file database, not a related one.  
There is no place to use the SQL code as I creted it or as you created it.  I'm trying to find a way to put a Basic version of the Select Where statement into Crystal Reports' designing.

You can only use SQL with SQL databases.

Did you try my idea?

mlmcc

mlmcc:
The problem with your solution is that there are other values in that field in most of the invoice line records, so that the sum would not be "0" or "1" when the calculating was done.  It would be something like 13 or 22.  This is the frustration of working with a flat-file.

Unless there is something else you haven't explained then this should work

If {TABLE_A.INFO_CODE} = "BINGO" Then 1 Else 0

If you are seeing values of 13 or 22 then this should mean that you have that number of invoice lines with a code of BINGO in the same invoice group. If the field holds any other value then the result will be zero, so as long as none of the invoice lines in a group contain the code of BINGO then the sum of this formula should = 0

If there are no "BINGO" records, then the sum of that formula will be 0 for that group and that group will be selected.  If there is 1 or more "BINGO" records the sum will not = 0 and thus that group won't be selected for the report.

It doesn't matter what values are iin the records for that field.  You are testing for BINGO records.  The group selection selects all the records for the group or none of the records for that group.

mlmcc

The SUM summary function only works on the specified field.  In this case the {@BingoFormula} formula.  The sum is also restricted to the group

The SUM summary does not add all the values in the record together.

mlmcc

How do I tie in table A's file number with Table B's?  this is where I want the "Select Where" clause, but cannot get it.

How are you selecting the files for the report?

mlmcc

The 3 tables needed have four fields in common if they are regarding the same file/invoice.  Using the Database Expert, I have chosen and linked using Left Outer Joins so that the Invoice Header is the Left-most table, with the tables holding the invoice lines and invoice remarks as equal next-tier tables.  The criteria (Select Expert) include the account number and the date field.  
I tried to group with a Select Distinct on the Invoice Number and exclude any Invoice Numbers that had a remark field of "BINGO", but what happened is that a distinct instance was created for each of the Invoice lines that did not include "BINGO" and the Invoice was included in the report.  

Do you think that we could use a Shared or Global Variable to get around this?  I.e.:  Create a Shared Variable that lists all Invoice Numbers that have a BINGO field and exclude it in the list of invoices?  If so, Can you help me with it?  I am not good at doing the variables.

no.  A shared variable is evaluated well after the selection of records is done.

mlmcc

I was blanking out on what was being explained, but when I was away from it a bit and went back I realized what mlmcc was trying to tell me and GOT IT!

Thanks for your patience.