Insert next record's field

[Robbydobbs]

I know it's possible to insert a field from a previous record. Is it possible to insert a field from the next record?

[Lee Smith]

Is the "next" record already in existence?

[Robbydobbs]

yes.

[Ugo DI LUCA]

Lookup rather than inserted....

Record_Id (autoserial num)

c_Next Record_Id = Record_Id +1

Selfjoin with c_Next Record_Id at left side and Record_Id at right side.

Your value

Your next value (lookup usingthe Selfjoin)

Could it suit your needs.

[Robbydobbs]

Look's like it might work. I'll have to try it. Thanks for the advise.

[danjacoby]

One caveat: The problem occurs when you've re-sorted records, 'cause then they're in a different order.

[Ugo DI LUCA]

Dead right Dan,

I don't know how I solved this one in one of my files where I use this next record lookup for a serialByCategory stuff....?

I'll give it a look and tells back.

[Robbydobbs]

Ugo, let me know what you found out on how you solved it in your records.

Thanks

[Vaughan]

It seems to me that whether a lookup or other technique is used it's going to need a script to trigger it. Might as well use a simple loop in that case:

Sort [restore, no dialog]

Go to Record/request [first]

Loop

Set Field [gValue, Value]

Go to Record/request [next, exit after last]

Set Field [Value, gValue]

End Loop

[Ugo DI LUCA]

Hi,

The way I lock the lookup is changing the calc from

c_Next Record_Id = Record_Id +1

to

c_Next Record_Id = Case(flagkey = 0, Record_Id +1, (Record_Id +1)&" 0")).

The flagkey is a num field entered once by script when I first "leave" the record.

But Vaughan is right. Yep.

In fact, the way I use it is slightly different. As Lee pointed it, I use it for creating records, and this makes a big difference (I think)

[CobaltSky]

Hi,

The 'RecordID + 1' approach may appear to work over small numbers of records, but it will break if you use it more widely, because Status(CurrentRecordID) numbering is only contiguous within defined ranges (as explained in FMI kbase article #104663 at http://www.filemaker.com/ti/104663.html ).

The method you've described will also break even when it falls within one of the ranges of contiguous IDs (as outlined by FMI) whenever/wherever a record has been deleted.

Next record lookups can be made to work reliably (in all cases where there *is* a next record), by referencing the current recordID within the index of the recordID field (retrieved using the ValueListItems( ) function) and then parsing the index to retrieve the next sequential value.

The calc which performs the parsing must then be linked to a data field so that it will be triggered by a user action, and defined as the key field in a relationship to the recordID field. Lookups which are based on the relationship will then copy values from the next record when the user performs one of the actions that has been defined as a trigger for the calc.

No scripts required...

[Ugo DI LUCA]

Hi Ray,

Thanks for pointing this out to me !

May this make a difference if I tell that the Record_Id is in fact a serial number, that I use with a lookup for a max 50 records per category.

I use this method to identify that line 0005g4g5dsqq556 of my line item is in fact the second line linked to the Invoice 125.

That means that the final calc with the record_id lookup stuff will be INV125-1, INV125-2, INV125-3,.... and that a new INV126 will automatically set the calc to INV126-1 as the lookup is set to enter 1 if no match value has been found...

If not (and anyway cause I'm sure your method is more reliable), could you be more specific for the second part of your post.

Are you saying that the left and right part should be :

c_key = ValuelistItems(Status(CurrentFile...), "Record_IDVList")

and the field to retrieve should be another calc

c_fieldForLookup = LAST(ValuelistItems(Status(CurrentFile...))+ 1

or

Max(Selfjoin::ValuelistItems(Status(CurrentFile...))+ 1

or

Middle(Position,...."PP",...) to extract the last occurence )+1)

Thanks for clearing me/us on this very good point.

[CobaltSky]

Hello Ugo,

Using a serial number field rather than a recordID will address the first part of the problem but not the second - the 'solution' will still break if a record is deleted (or if for any other reaons, your serial numbers happen to be non-contiguous).

The alternative method I suggested can be used in an *unstored* calc which will reliably reference the next record (if there is one), and can be used to trigger a lookup, or simply to display fields from the adjacent record on the current record. The calc would be along the lines of this:

Middle(

ValueListItems(Status(CurrentFileName), "IDs") & "

[Ugo DI LUCA]

Ray,

Sign ?

You know what ?

I'm happier today than yesterday, but surely less than tomorrow.

[Ugo DI LUCA]

Nope

I was happier yesterday. Cannot make this to work.

I've made a test with 20 records with a Record_Id autoenter serial num.

Created a Value list IDs of all Record_Id

and dropped your Calc N

[CobaltSky]

See attached demo for a working model.

The NumToText is not essential, providing your recordID and key field calc result types match.

The method is not suitable for use with totally random IDs, since it depends on the index order (sorted as per the characteristics for the field type of the recordID field) to match the creation order of the records.

AdjacentRecord.zip

[Ugo DI LUCA]

Ok Ray, I got it. Thanks a million.

I had exactly the same....but....as I use a FRench version, I didn't copy/paste your calcs.

Sometimes, it allows me to understand what I'm doing, but in that case I added a " ) " at the end of " Sign(Length(NumToText(recordID))) " from the second line of your 1st calc.

Strange FM didn't warned me. I suppose the calc was just good but for other purpose.

Thanks again. As you said. Very robust.