Avatar of PorterConsultingLLC
PorterConsultingLLC
Flag for United States of America asked on

SELECT WHERE Formula in Crystal ReportsXI

In Crystal Report XI,  I am trying to find an equivalent for a SQL command to put into a formula field.  THis is what I want to do:

SELECT {TABLE_B.FILE_NO}
      WHERE {TABLE_B.FILE_NO}<>(SELECT {TABLE_A.FILE_NO} WHERE {TABLE_A.INFO_CODE} = "BINGO")
Crystal ReportsProgramming Languages-Other

Avatar of undefined
Last Comment
PorterConsultingLLC

8/22/2022 - Mon
Mike McCracken

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

mlmcc
GJParker

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

ASKER
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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Mike McCracken

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
PorterConsultingLLC

ASKER
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.
Mike McCracken

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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
PorterConsultingLLC

ASKER
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.
Mike McCracken

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
PorterConsultingLLC

ASKER
I'm sorry, I don't know what you mean by create a COMMAND.  Where, and in what language?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
PorterConsultingLLC

ASKER
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.
Mike McCracken

Can you create a report against the database?

How do you select the tables?

DO you see ADD COMMAND as an option?

mlmcc
GJParker

you should still be able to select tables, join them abd add filters using ta selection formula.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
PorterConsultingLLC

ASKER
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?
ASKER CERTIFIED SOLUTION
Mike McCracken

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
PorterConsultingLLC

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

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.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
PorterConsultingLLC

ASKER
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.
Mike McCracken

You can only use SQL with SQL databases.

Did you try my idea?

mlmcc
PorterConsultingLLC

ASKER
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
GJParker

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



Mike McCracken

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
Mike McCracken

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
Your help has saved me hundreds of hours of internet surfing.
fblack61
PorterConsultingLLC

ASKER
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.
Mike McCracken

How are you selecting the files for the report?

mlmcc
PorterConsultingLLC

ASKER
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Mike McCracken

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

mlmcc
PorterConsultingLLC

ASKER
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.