Ordering Related Records w/ Order Field

[HiredGun79]

Hello,

I've moved this post from the Scriptmaker forum after 50 views and 0 responses. . perhaps you all can help me out! Thanks in advance. . .

I have an order database and an itinerary database - In the itinerary database, I have a field called stop_order (as in the order of stops to make) and a foreign key field called order_number.

What I have is a portal on the order layout that lists the related itinerary stops. I already have the create new stop button working using "if else" and "count" to count the number of stops and set the "stop_order" field to be the number of related records plus 1.

On the order layout, I have the itinerary portal sorted by stop_order. .

What I need a little help with is creating the delete, move up and move down buttons that will go on each row of the portal.

The part I'm stumbling on is if, say, there is 4 stops, and the user deletes the middle stop, stop 2. . what is the best way to determine what related records are after that and go about setting the stop order field to the new value on the multiple records?

The move up and move down buttons have the same problem, though it's smaller since it only effects two related records. . .

By taking a guess, I imagine it'll use global fields in some way. . but I'm not sure. . I think I've been staring at the scriptmaker screen a little too long.

Thanks again!

[mdpres]

I just create a "Clear Button" or a "Delete Button" in the protal row. Then you can just delete portal row through your relationship.

[HiredGun79]

I don't think I phrased my question that well. . .

I can do that, but in addition to that, I need to change the stop_order value. For instance, say there are 4 itinerary stops, with stop_order values from 1-4. They are ordered by the stop order value. . .

When one stop is deleted, in addition to deleting the related itinerary stop, I need to change the stop_order value for the other related records.

So if the user erases the #2 stop, I need a good way to find the number 3 and number 4 stop and subtract 1 from them. . .

I'm confused because I do not know the best way to find those related records that need to be changed. . .

Thanks for the response!

[comment]

You can find 3 demos that do that at:

http://www.databasepros.com/resources.html

Do a search for "portal row move".

[HiredGun79]

I'll check it out - Thanks!