Display Item based on comparison with another field

[jspur2003]

Ive had problems with this before and have found partial solutions but havent totally solved my problem... Im using Crystal Reports 8.5 std. not exactly sure how to word this but let me try.. I want to print Items in a table and to print those items it needs to be compared with another field depending if the paramater = Yes or NO EX below
IF Yes
ITEM Date QTY SPLCHG
1001/p2 4/4/04 5
F 4/4/04 5 F

IF No
ITEM Date QTY SPLCHG
1001/p2 4/4/04 5
Im not the best formula writer in the world but this is how i have it currently but the problem im trying to avoid is having to update the selection formula everything we add a special charge code...

IF {?Include Special Charges}="NO"then
not ({LOT.ILT_ITEM} in ["F"]) and
{LOT.ILT_CUST#} = {?Customer} and
{@Begin Date} = {?Date Begin} or
{LOT.ILT_CUST#} = {?Customer} and
{@End Date} = {?Date End}
else
IF {?Include Special Charges}= "YES" then
{LOT.ILT_CUST#} = {LOT.ILT_CUST#} and
{LOT.ILT_CUST#}= {?Customer} and
{@Begin Date} = {?Date Begin} or
{LOT.ILT_CUST#} = {?Customer} and
{@End Date} = {?Date End}
The special charges field in the database is LOT.ILT_SPLCHG and its a string value and ive tried a few different ways but cant seem to get it to work properly it ususally ends up not displaying any items if we select NO... The code i had above it what we have now and we can insert the special codes into the Not IN section but everytime we add or subtract the code we have to go back in and edit the selection formula... As you can see we also are selecting by customer number and dates as well... Any help is greatly appreciated if you are unsure what im trying to get at please let me know and i will try to explain more... Thank you

 

[synapsevampire]

To simplify and better define the requirements, note the use of parentheticals in the following formula:

IF {?Include Special Charges}="NO"then
(
(
{LOT.ILT_ITEM} <> "F"
)
and
(
{LOT.ILT_CUST#} = {?Customer}
)
and
(
{@Begin Date} = {?Date Begin}
)
)
or
(
(
{LOT.ILT_CUST#} = {?Customer}
)
and
(
{@End Date} = {?Date End}
)
)
else
IF {?Include Special Charges}= "YES" then
(
(
{LOT.ILT_CUST#} = {LOT.ILT_CUST#}
)
and
(
{LOT.ILT_CUST#}= {?Customer}
)
and
(
{@Begin Date} = {?Date Begin}
)
)
or
(
{LOT.ILT_CUST#} = {?Customer}
)
and
(
{@End Date} = {?Date End}
)

As for your current requirements, your formula doesn't use LOT.ILT_SPLCHG, so I've no idea of what you intend, rather than trying to use text, simply post technical information:

Database/connectivity used
Example data (show the columns and example data)
Expected output (show the results based on the example data provided)

-k

 

[jspur2003]

Ok Im using Access and as the Database and connectivity used is just through the database file selection.. I havent tried ODBC or Active data..
Below an ex of a report

Cust# 1539
ITEM QTYCurrentYR QTYPreviousYR DIFF
1601 50 30 20
943/p2 30 35 (5)
1555/p4 15 10 5
F 1 0 1
I will have to edit the selection formula. In my access database named LOT along with ILT_ITEM and ILT_DATE and all the other fields in my database...I have a field named ILT_SPLCHG which is a string and it contains F,P F1,F2,F3 along with other values... In the ITEM field it has the same values for that line in the table so if you look at the table entry for that cust and date it has:
Item Date Price, Cust# SPLCHG
F 4/4/4 500 1539 F
Im not sure how to write the part of the selection formula that would IF the user selected NO then compare ITEM "F" to SPLCHG "F" and if that is true then the line with F in my example report above shouldn't appear on the report. If they choose Yes for ?Include Special Codes then that last line will be in the report Hopefully this clears things up

 

[synapsevampire]

More parentheticals I suppose, you say if they answer no, answer no to what? I'll assume the existing NO, the line is something like:

IF {?Include Special Charges}="NO"then
(
(
{LOT.ILT_ITEM} = "F"
and
{LOT.ILT_ITEM} <> {table.splchg}
)
and
(
{LOT.ILT_CUST#} = {?Customer}
)
and
(
{@Begin Date} = {?Date Begin}
)
)
or
(
(
{LOT.ILT_CUST#} = {?Customer}
)
and
(
{@End Date} = {?Date End}
)
)
else
IF {?Include Special Charges}= "YES" then
(
(
{LOT.ILT_CUST#} <> "F")
and
(
{LOT.ILT_CUST#}= {?Customer}
)
and
(
{@Begin Date} = {?Date Begin}
)
)
or
(
{LOT.ILT_CUST#} = {?Customer}
)
and
(
{@End Date} = {?Date End}
)

I had suggested that you provide example data and the resulting output. It clearly identifies requirements, unlike "If they choose Yes for ?Include Special Codes then that last line will be in the report " What last line? Where in the report?

Anyway, the example above should help.

-k

 

[kskid]

Try this

Create an alias of the same table, LOT_1 with a left outer join on LOT.ILT_CUST# to LOT_1.ILT_CUST#

Use the following in your record selection criteria.

{LOT.ILT_CUST#}= {?Customer} and
{LOT.ILT_ITEM} <> "F" and
(
{@Begin Date} = {?Date Begin} or
{@End Date} = {?Date End}
) and
(
isnull({LOT_1.ILT_ITEM}) or {LOT_1.ILT_ITEM} = "F"
)

From Report Design screen, create Detail Section B and insert the same fields from LOT_1 in Detail section B as you have from LOT in Detail A.
Suppress Detail B and click on x+2 and add the following formula.

//section will stay suppressed if both are true
{?Include Special Charges} = "NO" and LOT_1.ILT_ITEM <> "F"

 

[jspur2003]

I really dont know how else to explain what im trying to get at basically what i want to do is compare 2 fields ILT_ITEM and ILT_SPLCHG... In my ILT_ITEM field it would carry values in order in DB

1005
1021
1515
F
P
2525
.
.

The Field ILT_SPLCHG would contain values in order in DB

""
""
""
F
P
""
I will have to rewrite the selection formula but unsure exactly how but what needs to happen is IF the end user who runs the report enter NO on the paramater for ?INCLUDE SPECIAL CODE then what needs to happen is IF ILT_ITEM = F and ILT_SPLCHG = F then when the report prints to the window it will LOOK LIKE THIS:
Cust# 1539
ITEM QTYCurrentYR QTYPreviousYR DIFF
1005 50 30 20
1021 30 35 (5)
1515 15 10 5
2525 15 20 (5)
IF the user selects YES then it will LOOK LIKE THIS:
Cust# 1539
ITEM QTYCurrentYR QTYPreviousYR DIFF
1005 50 30 20
1021 30 35 (5)
1515 15 10 5
F 0 1 (1)
P 1 0 1
2525 15 20 (5)

Does this help any?

 

[kskid]

Based on your expected output, "P" needs to be included


//this first part will select records from LOT only if
//LOT.ILT_ITEM is not equal F or P
{LOT.ILT_CUST#}= {?Customer} and
not({LOT.ILT_ITEM} in ["F","P"] and
(
{@Begin Date} = {?Date Begin} or
{@End Date} = {?Date End}
) and
//This part will select F or P from LOT_1.ILT_ITEM if
// found with SPLCHG = "F"
(
isnull({LOT_1.ITEM}) or
(
{LOT_1.ILT_ITEM} in ["F","P"] and
{LOT_1.SPLCHG} = "F"
)
)

//The Detail Section B will print only if
//{$Include Special Charges is "YES" with any F or P item
//records.


if {?Include Special Charges} = "YES" and
{LOT_1.ILT_ITEM} IN ["F","P"]) and
LOT_1.SPLCHG = "F" then
false //Unsuppressed section, which will print data
else
true //Stay suppressed

 

[synapsevampire]

A bit of a moving target I think...

IF {?Include Special Charges}="NO"then
(
(
{LOT.ILT_ITEM} in ["F","P"]
)
and
(
{LOT.ILT_CUST#} = {?Customer}
)
and
(
{@Begin Date} = {?Date Begin}
)
)
or
(
(
{LOT.ILT_CUST#} = {?Customer}
)
and
(
{@End Date} = {?Date End}
)
)
else
IF {?Include Special Charges}= "YES" then
(
(
not({LOT.ILT_CUST#} in ["F","P"]
and
{LOT_1.SPLCHG} = {LOT.ILT_CUST#}
)
and
{LOT.ILT_CUST#}= {?Customer}
)
and
(
{@Begin Date} = {?Date Begin}
)
)
or
(
{LOT.ILT_CUST#} = {?Customer}
)
and
(
{@End Date} = {?Date End}
)

Not sure about your OR statements, you provide a few more details with each post.

Note that the example data clarifies everything, but your example data doesn't reference all of your other criteria.

-k

 

[jspur2003]

What im trying to avoid is this line right here

IF {?Include Special Charges}="NO"then
(
(
{LOT.ILT_ITEM} in ["F","P"]

Everytime i try to put
{LOT.ILT_ITEM} in [{LOT_1.SPLCHG}]
it either print all the records if Include Special Charges = Yes or if Include Special Charges = No the it wont print any records.
What need to happen is when it evaluates the record if Lot.ILT_Item record contained a character that was in lot.ILT_SPLCHG then either print the record depending on the users selection selection of YES or NO so when the selection formula looks at ITEM and SPLCHG if lets say G was in ITEM and G is in SPLCHG the dont display the record IF 1001 is in ITEM and Not in SPLCHG then display the record

 

[kskid]

Since it is left outer joined, change it to

(
isnull({LOT_1.SPLCHG}) or
{LOT.ILT_ITEM} in [{LOT_1.SPLCHG}]
)