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
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.
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
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.
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?
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.
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.
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.
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!
If so you could use a SQL expression
mlmcc