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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Build an E-Commerce Site with Angular 5

Learn how to build an E-Commerce site with Angular 5, a JavaScript framework used by developers to build web, desktop, and mobile applications.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.