Record selection on a formula that has to be evaluated later

[sschaller]

I have created a Crystal report for Peachtree that tracks serial numbers as items are purchased and then sold. My client wants to be able to filter the report by the status of the item. This is complicated because a separate transaction is created in the JrnlSNum table each time the status changes. In the table the status is represented by a number but I created a formula called {@Status} to show text for each status:

If {JrnlSNum.TransactionClass}=0
Then "appears on work ticket"
Else If {JrnlSNum.TransactionClass}=1
Then "Available"
Else If {JrnlSNum.TransactionClass}=2
Then "Returned by Customer"
Else If {JrnlSNum.TransactionClass}=3
Then "Sold"
Else If {JrnlSNum.TransactionClass}=4
Then "Returned to Vendor"
Else If {JrnlSNum.TransactionClass}=5
Then "Adjusted"



Then I created a parameter called {?Status} and populated the choices with Available, Sold, Adjusted, etc. At first in the Select Expert I said {@Status} = {?Status}. If someone chose Available for the status it would show each transaction where the status was available, but if the item was subsequently sold they don't want it to be included.



So I created a second formula called {@StatusDisplay} that looks like this:

If {JrnlSNum.SerialRecordNumber}=Maximum ({JrnlSNum.SerialRecordNumber},{JrnlSNum.SerialNumber})
Then {@Status}



Basically it will only show the status of the most recent transaction for each serial number. Now I want to set the selection so that {@StatusDisplay}={?Status} but it says "This formula cannot be used because it must be evaluated later." I can see why that would be the case because it doesn't know if {JrnlSNum.SerialRecordNumber}=Maximum ({JrnlSNum.SerialRecordNumber},{JrnlSNum.SerialNumber}) until the data is compiled. Is there any way around this though? Is there some way to have the report run in two passes so first it complies the complete set of data and then filters it so that {@StatusDisplay}={?Status}? Or any other way to accomplish what I'm trying to do? Any thoughts on the subject would be greatly appreciated.



Steve Schaller
TriStar Data Systems
Blue Bell, PA

 

[CR85user]

Group by {JrnlSNum.SerialNumber} then by {JrnlSNum.SerialRecordNumber} place your fields in the footer so as to display the most recent entry.

If you need these entries at detail level then advise and there are then several ways to complete this including use of a command or subreport.

To simplify your current selection.

You don't need to refer the parameter to the formula and compare based on description.

Just make the parameter a number type and add 1,2,3,4,etc and give each the description as shown above and display description only.

Then in your receord selection just use:

{JrnlSNum.TransactionClass} = {?Status}

Or the 'in' operator if multiple values allowed.

Can I also clarify one point.

If your users are selecting for example 'Adjusted' what do they expect to see?

1) Any transactions that are of the type adjusted regardless of most recent status or not?
2) Only transactions for which the most recent status is adjusted?
3) Only the adjusted transactions and not any other transactions relating to the account?
4) All transactions for any accounts where the most recent status is 'Adjusted'?

Let us know so we can better help you.

'J

CR8.5 / CRXI - Discovering the impossible

 

[sschaller]

Thanks for your response but that still doesn't quite get me to where I need to be. To answer your question, if they choose Adjusted, they would only want to see transactions where the most recent status is adjusted. Putting the fields in the footer works to only display the most recent status but if {JrnlSNum.TransactionClass} = {?Status} and they select only Available all the items show, because at some point they were available, even though now some of them have been sold. Because of the selection I have eliminated all of the other transactions for that item and it thinks the first transaction, where the item was available is the latest. Basically they want to see one line for each serial number with it's current status so they can keep track of which items are available and which have have been sold. Any other ideas?

 

[lbass]

Create a SQL expression {%maxstatus} like this:

(
Select Max(`SerialRecordNumber`)
from JrnlSNum A
where A.`SerialNumber` = JrnlSNum.`SerialNumber`
)

Add a record selection formula like this:

{JrnlSNum.SerialRecordNumber} = {%maxstatus} and
{@Status} = {?Status}

An alternative method would be to allow all statuses into the report, and then go to report->selection formula->GROUP and enter:

{JrnlSNum.SerialRecordNumber}=Maximum ({JrnlSNum.SerialRecordNumber},{JrnlSNum.SerialNumber}) and
{@Status} = {?Status}

You would need to use running totals with the second method if you want to calculate across groups, since non-group selected records would still contribute to the more usual summaries.

-LB