Find field where record is equal to criteria

Having a bit of trouble and thought I would come to the experts.  
I have a crystal report where I am pulling data from an xml dataset.  I have it grouped by time which yeilds 2 records.  The only way I can differentiate is by using the transaction number.  I have identified that the lowest transaction number is the original value and the next is the changed.

So I am trying to use a formula to select the field called CalcInvVal where the transaction number is the lowest.  

Just having trouble with the correct syntax.  Here is what I have so far

Select {@CalcInvVal} where {BAQReportResult.PartTran.TranNum} = {@Calc_TranMin}


Here is the code I am using for the Calc_TranMin above

Minimum ({BAQReportResult.PartTran.TranNum}, {BAQReportResult.CalcTime})
LVL 3
Trygve ThayerIT DirectorAsked:
Who is Participating?
 
mlmccCommented:
Are there always 2 records for a time?

Do you ever have more than 2 records?
If not then try this
In the TIME group footer add a formula
{ValueField} - Previous({ValueField})

mlmcc
0
 
mlmccCommented:
Where are you using the formula?

Have you tried sorting by datetime then by transaction number and using a TOP 1 selection on the group?

Do you just want to show the 1 record?
If so put the information in the group header and suppress the details.

mlmcc
0
 
Trygve ThayerIT DirectorAuthor Commented:
I think I am trying to use the formula on the time grouping.  The report is grouped by Plant. by transaction number by time.  Within the time there are only 2 records and they do appear to be in order of lowest to highest.   To add to what I am working on I am trying to identify the first record value as it is the original.  Then I want to identify the value of the second (or last) and subtract the first from the last to get a positive or negative value.    The way I was doing it previously was getting the max value and the min value and then showing the difference but our accountants want to see if the value is negative or positive.  
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Trygve ThayerIT DirectorAuthor Commented:
There is never more than 2 records.  I will try that.
0
 
Trygve ThayerIT DirectorAuthor Commented:
Amazing.  That works great!  Many Thanks
0
 
Trygve ThayerIT DirectorAuthor Commented:
Thanks again to the experts.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.