Set Next Related Record

[datalink]

In the system we are working on there is a jobs table and a phase table, with each job having a series of related phases. In the job table there are key fields to house the phaseId of the current phase (_zkfCurrentPhaseID), and also the PhaseID of the next phase (_zkfNextPhaseID) in the production cycle. I need a way in a script to evaluate the current phase of production and set _zkfNextPhaseID to the recordID of the next related record. Because of how phases are added to the production cycle we can't just take the current ID and add 1 to it. It has to be the next related record ID.

Any suggestions would be greatly appreciated. Thanks so much.

[comment]

Couldn't you calculate the next phase from the current phase and List ( Phases::PhaseID )?

[datalink]

Couldn't you calculate the next phase from the current phase and List ( Phases::PhaseID )?

I haven't worked much with the list function. The calculation that you suggest gets me a list of phases for the current job, which is cool, but I'm not sure how to use that to get the next list value to use in a Set Field step in my script.

[comment]

Try =

Let ( [

v = List ( Phases::PhaseID ) ;

i = ValueCount ( Left ( v ; Position ( ¶ & v & ¶ ; ¶ & CurrentPhaseID & ¶ ; 1 ; 1 ) ) )

] ;

GetValue ( v ; i + 1 )

)

Note that this assumes that phases are created in order, or that the relationship is sorted in the correct order.

to use in a Set Field step in my script.

I don't see why you need a script here. Select the current phase, and the next phase ID is automatically calculated.

Another option is to define a second relationship as:

Jobs::CurrentPhaseID < Phases 2::PhaseID

The next phase is the first related record through this relationship (again, assuming creation order is the correct order).

[datalink]

This is going to work perfectly for what we are trying to do. Thank you so much for you help.