get previous record field values into following record

[Poruchan]

Hi, the goal is to monitor and compare results when a horse changes racecourses. I want to grab a few of the field values from the previous record and insert them in the following record when there is a track change.

I’ve seen similar requests for help on getting info from previous record, but was unable to adapt the solutions offered up.

In the image I’ve added text fields (last Race_ID, last track, last class, last fin) for demo purposes. The ‘change’ field should be temporary, but I suppose I could live with it.  The ‘mark’ field was part of my effort to have fmp do it. 

There are 3 tables working here in the image ‘Results’ ‘Races’  and ‘Horses’. But, that was to get the English name - really two tables at work ‘Results’ and ‘Races’.   

[comment]

There are several ways to interpret your question, because both "previous record" and "insert values" are ambiguous terms. If "previous record" means previous in the context of the current found set and sort order, then you can use the GetNthRecord() function to get values from it. For example, an unstored calculation field  =

HorseID = GetNthRecord ( HorseID ; Get (RecordNumber) - 1 )
and
RaceID ≠ GetNthRecord ( RaceID ; Get (RecordNumber) - 1 )

would return true when the previous record for the same horse is from a different race.

[Poruchan]

I’m being too ambitious. Can we just do one small thing, first, so I can understand GetNthRecord? 

From the image, I want the record with the [_RaceID]  of  15may18kana7  to show  4mar18kok1  in the [last Race_ID] field.  

Thought I would get that with:

last Race_ID = GetNthRecord ( _RaceID ; Get (RecordNumber) - 1 )

But I didn’t …  [_RaceID]  and   [last Race_ID]  are the same.

[comment]

I don't see your file, so I don't know what exactly you did wrong. Does the attached demo work for you?

PreviousValue.fmp12

[Poruchan]

Yes, I have just like your demo - It appears I have something wrong with my thinking on the found set. I see now that it is grabbing the previous Race_ID (not unique)  - as if it were in the original sort.

I thought "GetNthRecord of the current table returns the Nth record of the found set according to how the current table is sorted" 

 

current table  - results

found set - Inside Out

sorted by - rDate

[comment]

45 minutes ago, Poruchan said:

I thought "GetNthRecord of the current table returns the Nth record of the found set according to how the current table is sorted" 

Is your calculation field set to 'Do not store calculation results -- recalculate when needed' in Storage Options…?

[Poruchan]

That was it

I think I'm happy with this for now - as I review the races I can just use the check box when there is a change in venue. Let me think on it for a while - as always, I very much appreciate your help.

[comment]

12 hours ago, Poruchan said:

as I review the races I can just use the check box when there is a change in venue.

What is your purpose here? If all you want is a visual separator between groups of records, why not add a sub-summary part to the layout and sort the records? Conditional formatting could also be used for this. Neither of these require adding calculation fields to your schema.

[Poruchan]

A Class A race in New York would probably be the same as a Class A race in California. Not so here. Each of the 15 local tracks have their own bizarre class systems. If I could isolate just the races where there was a venue change and see the results from both races (the new race, and the previous race) on one line, I think it would be a real eye opener. I have almost 300,000 records, so separating these out is important.

As far as isolating the races, I thought it would work with something like checking for duplicates where a record with a different track than the previous record gets marked. The output looks so simple (attached image):

From the image it is very helpful for me that I can see the horse raced at Kana, class C1 and finished 3rd, THEN in the next race finished 2nd at Nago class CA.

A sub-summary solution sounds interesting - but, alas, can't see how to do it.

[comment]

I am afraid you have lost me at this point. I think that if you want to isolate records that meet some criteria you should perform a find. Or perhaps use a relationship to show the "isolated" (i.e. related) records in a portal. That would be especially useful if you want to to do side-by-side comparisons: use 2 side-by-side portals.

 

2 hours ago, Poruchan said:

A sub-summary solution sounds interesting - but, alas, can't see how to do it.

Like in the attached demo file?

Subsummaries.fmp12

[Poruchan]

Let me take another stab at it -  I grabbed a random horse Capriccioso from the layout based on the Results table (results are imported form excel). The Track, Class and Fin are from the Races table (also excel import). I hi-lighted the races I need to focus on. As you can see on 24 Feb Capriccioso raced at Oi, C2 class and finished 12th. He then travelled to Kana for a B1 class race and finished 6th.

I need to find these pairs in the thousands of records. My first thought was that I needed to somehow mark a race record where the horse travelled - otherwise, my thinking goes, there is nothing to find.

[comment]

Well, if you sort by horse, then by race, and add a sub-summary part by race, you will get a visual separator whenever a horse changed race. That is assuming all you want is a visual indicator. If you want some sort of permanent marker, then none of the above is relevant, since it all depends on the current found set and sort order.