6 Replies Latest reply on May 15, 2012 12:26 PM by otto_m

I need a sequential number field

Title

I need a sequential number field

Post

Hi,

I have a table "STUDENT" with student data related to a table "PROBLEMS" with a couple of dozen exam problems via STUD-ID key.

For the oral exam, I assign each student six problems by entering his/her name in the STUD-ID field.

What I need for the test paper layout (sorted by STUD-ID) is a field that contains a sequential number (1-6) in the order I assign the problems to each student so I can print:

PROBLEM <<1>>:

Find …

PROBLEM <<2>>

Do …etc.

Another use is in post-exam comments, such as "Problem <<4>> was solved creatively…"

Otto

• 1. Re: I need a sequential number field

You have this relationship:

Student::Stud-ID = Problems::Stud-ID

How do you determine the order to be used for the selected problems? Should the problems be listed in the order that you select them?

Or is there a sort order that is predictable and that produces the order that you want here?

• 2. Re: I need a sequential number field

The order should be determined by the order of entries of STUD-ID, i.e. the first problem I assign to student A should be #1, the second I assign to student A should be #2 etc. And the first problem I assign to student B again should be #1, the second I assign to student B should be #2 etc.

And it should be possible to enter students A, B, C etc. randomly. This means not all six problems are assigned to student A first, but rather like:

problem 1 assigned to student C, problem 2 assigned to student F, problem 3 assigned to student A etc.

• 3. Re: I need a sequential number field

I read that as the order is determined by the order in which you select them for a given student.

Are the same problems assigned to more than one student? Your last post seems to describe that, but your relationship does not appear to support that as that requires a many to many relationship. (But perhaps you are building a return separated list of values in the Problems::Stud_ID field.)

• 4. Re: I need a sequential number field

Yes, the order is determined by the order in which I select them for a given student.

The same problem is never assigned to more than one student.

Otto

• 5. Re: I need a sequential number field

No problem, it's just that making sure the underlying design will support what you want to do is step one.

Define a number field in students as MaxProbNumber. Define a sequence number in Problems as ProblemNumber.

When selecting a student in the Problems::Stud_ID field, use the OnObjectSave trigger to perform this script:

Commit Record
Set Field [Students::MaxProbNumber ; Students::MaxProbNumber + 1]
Set Field [Problems::ProblemNumber ; Students::MaxProbNumber ]

This assumes that only one user will be assigning problems to a given user at any given time--a safe assumption I would think.

Please note that if you assign a problem to a student and change your mind, you'll need to manually fix the ProblemNumber field and you may need to update the MaxProbNumber field in some circumstances. There are also methods that can take all your assigned Problem records, sort them by student, sort them by problemNumber and then "clean up" the problem numbers to eliminate gaps and/or duplications created by such changes during or after selecting the problems.

Also, given that names are not unique and given that even children sometimes change their names, you may want to change your relationship used here to be one based on an ID number defined in your students table.

• 6. Re: I need a sequential number field

Thank you for solving my problem:-)

I'll be careful not to mess up the order.

BTW, the relationship field Stud_ID is actually a unique value based on enrollment.

Thanks again for your kind help,

Otto