Updating sort order of records (1 Viewer)

HealthyB1

Registered User.
Local time
Today, 12:55
Joined
Jul 21, 2013
Messages
82
G'day,
I have a table with entries describing a sequence of Tasks (tblTask)

tskID TaskDescription
10 Clean Room
12 Wash Dishes
etc

I have second table called Steps (tblSteps) that is related to tblTask via the field "StepNumber" which is a concatenation of [tskID] & "." & [StepOrder]
So if we look at washing dishes as a task then the steps are as follows:

StepID, StepDescription, StepOrder, StepNumber
202 Put plug in the sink , 1, 12.1
512 Turn on tap, 2, 12.2
205 Put in detergent, 3, 12.3
210 Place dishes in sink, 4, 12.4
435 Turn off tap, 5, 12.5
etc

If I decide delete the third step I get the following:-
StepID, StepDescription, StepOrder, StepNumber
202 Put plug in the sink, 1, 12.1
512 Turn on tap, 2, 12.2
210 Place dishes in sink, 4, 12.4
435 Turn off tap, 5, 12.5

Is there a simple way renumbering the StepOrder entries when one step is totally removed to give me the following?

StepID, StepDescription, StepOrder, StepNumber
202 Put plug in the sink, 1, 12.1
512 Turn on tap, 2, 12.2
210 Place dishes in sink, 3, 12.3
435 Turn off tap, 4, 12.4

Sorry about the formatting but hopefully the commas will help delineate the fields in the records.
 
Last edited:

madefemere

Registered User.
Local time
Today, 06:25
Joined
Aug 3, 2013
Messages
80
Hello,

"simple way", No.
The value depends of the "StepOrder" field, so to give an appropriate solution, how do you define the value of "StepOrder" ?
 

spikepl

Eledittingent Beliped
Local time
Today, 05:25
Joined
Nov 3, 2010
Messages
6,144
Your relation is wrong, The steps should be related to the task via tskID. Your steps should have a sequence number (signifying each step's relative place in the sequence) , which would not need to chnage when a task is removed.

And then you can at any time recalculate your composite ID for the steps belonging to a task.
 

smig

Registered User.
Local time
Today, 06:25
Joined
Nov 25, 2009
Messages
2,210
StepNumber is not needed.
You should put the TaskID, as spikepl said.
The "StepNumber" can be calculated on the fly when required.

If you remove a step it's not necessary to reorder steps. but if you want to put a step in the middle or reorder steps you'll have to.
 

HealthyB1

Registered User.
Local time
Today, 12:55
Joined
Jul 21, 2013
Messages
82
The attached may better explain what I am trying to achieve.
Attachment 1 shows the records prior to deleting any records.

Attachment 2 shows what happens when I delete the 3rd record in the sequence. As you can see when I delete a record in the SubProcSteps form I want to renumber the substep 2.4 to 2.3 and substep 2.5 to 2.4
The substep as mentioned is the concatenation of "[StepNumber]" (from the frmMainProcSteps),".",[Order] (from frmSubProcSteps).
Basically how do I change the "Order" field?
Is it as simple as making a temporary file with an Autonumber field every time I delete a record in the SubProcSteps File?

I can do it manually by changing the value in the "order" field from 4 back to 3 and the Sub_Step number becomes 2.3. Likewise if I manually change step 5 back to step 4 then the 4th item in the list becomes Sub_Step 2.4
However if I had 20 or more records to re-sequence, this would become very laborious and error prone. So I would like to automate same.
 

Attachments

  • capture 1.JPG
    capture 1.JPG
    82.2 KB · Views: 47
  • Capture 2.JPG
    Capture 2.JPG
    82.4 KB · Views: 49
  • Capture 3.JPG
    Capture 3.JPG
    81 KB · Views: 39

HealthyB1

Registered User.
Local time
Today, 12:55
Joined
Jul 21, 2013
Messages
82
Hello,

"simple way", No.
The value depends of the "StepOrder" field, so to give an appropriate solution, how do you define the value of "StepOrder" ?

Hi thanks for your help.
It is the sequence number of the tasks. Refer to attachments in my reply to SMIG
Thanks in advance for any help you can offer
 

Users who are viewing this thread

Top Bottom