Producing next and previous values using a sequence number

[woodyinoz]

Hi all,

This is a tricky one to explain so bare with me.....

I've got a table which holds 5 fields:

BASE_ID
MACHINE
SEQUENCE_NO
PREVIOUS_MACHINE (EMPTY)
NEXT_MACHINE (EMPTY)

I have 5 machines (MACHINE1-MACHINE5)
The BASE_ID is basically a job number and the SEQUENCE_NO is the order in which the machines are used upon a particular job. For example:

BASE_ID: 12
MACHINE: 1
SEQUENCE_NO: 10

BASE_ID: 12
MACHINE : 3
SEQUENCE_NO: 13

BASE_ID : 12
MACHINE : 5
SEQUENCE_NO : 2

etc.. etc..

What I need to do is search through the table matching MACHINES to specific BASE_ID's and then fill in PREVIOUS_MACHINE and NEXT_MACHINE according to the SEQUENCE_NO. For the above examples:

BASE_ID: 12
MACHINE: 1
SEQUENCE_NO: 10
PREVIOUS_MACHINE: 5
NEXT_MACHINE: 3


BASE_ID: 12
MACHINE : 3
SEQUENCE_NO: 13
PREVIOUS_MACHINE: 1
NEXT_MACHINE: <BLANK>

BASE_ID : 12
MACHINE : 5
SEQUENCE_NO : 2
PREVIOUS_MACHINE: <BLANK>
NEXT_MACHINE: 1

Ideally I would like the code to run each time the form which uses the table is opened.

Thanks in advance, any questions please don't hesitate to post.

Woody.

 

[jartman2]

Woody:

How about creating two secondary indices on the table, sorted first on BASE_ID and then on SEQUENCE_NO (ascending for one, descending for the other). Put the code in Pagepen to apply the first secondary index to a tCursor, and then walk through the table with the tCursor and set the PREVIOUS_MACHINE field as you go.

Then do the whole thing again, this time with the descending SEQUENCE_NO index, and set the NEXT_MACHINE field.

- John