Auto-Serial in Based on IF test?

[Calantha]

Hi there,

In my database I have a field "Student Number" that auto-generates a student number using the Auto-Enter Serial Number function of that field.

However, not all entries into the database are enrolled students, some are merely inquiries, applicants, etc., who do not need a student number.

I also have a drop-down Value List for the Inquirers Current Status (i.e. Enrolled, Accepted, Under Review).

Is it possible to have it so that:

If Current Status = "Enrolled" then auto-enter the serial number?

Thanks for you help!

I have asked many questions on this forum since I have been given the task of designing this database with no previous knowledge of databases and have had nothing but great responses from people more than willing to help, and I am very grateful!

[comment]

For good practice, every record in your db should have an auto-generated serial number ID. Perhaps you should rename it to PersonID, or something like that.

The question is: how important is it for Student Numbers to be consecutive and contiguous? If it were me, I'd simply use the same numbers. So an enquirer/applicant would be issued a PersonID today, that would become his Student Number when he/she enrolled.

If that is not acceptable, you will need a separate numbering mechanism for Student Numbers.

[Calantha]

Hello!

I understand the logic behind having an ID number for every entry and I might just add that in but these need to be seperate from that.

I considered leaving it and having everyone use that as an ID number but it is relatively important that they numbers remain consecutive and within a certain range (that we specify).

What I would like to do essentially is have the Serial Number auto-enter only when specific criteria are met (the year of enrollment and the fact that they are enrolled) by hopefully using some sort of Logic statement.

Is this possible?

If not is there another way around it, some sort of Loop calculation?

Thank you for your response!

[comment]

Generating conditional serial numbers is problematic. It's difficult to prevent assigning the same number to more than one record (e.g. by two users creating a new record at the same time), and at the same time to make sure there are no gaps.

Consider a situation where an existing record is changed from applicant to student. A new student number, say 1000, is assigned to this record. Meanwhile, another student record is added to the db and given the number 1001. Now it turns out that the applicant will not be enrolling after all. What now?

That is an example of the problems with methods that use a relationship and Count() or Last() or Max() functions to assign the next number.

If I had to do this, I would do it with a script, not a calculation. I would create another file that would serve as a numerator. This file needs only two fields: PersonID and StudentNumber (auto-enter serial number). The relationship to the Numerator file is:

People::PersonID = Numerator::PersonID

and is set to allow creation of related records in Numerator.

The script, run from the People file, is simply:

Set Field [ Numerator::PersonID , PersonID ]

This will create a new record in Numerator and assign it a new student number (which you can access thru the relationship). If the current record has already been assigned a student number in the past, the script will essentially do nothing.

[Calantha]

Perhaps I do not understand because my knowledge of databases is rather bleak, or you are misunderstanding me.

For this database only one person at a time will be creating new records. Our Inquiries as well as Enrollments are one record, the "Current Status" Value List just changes according to whether they a) are inquirers, applicants, c)enrolled students. I merely change the drop down value according to their status.

The student number is included on the same record as people who have merely inquired. Because it is auto-generate a student number is given to every entry independent of whether they are enrolled or not. I do not want anyone who does not have a status of "Enrolled" to have a student number.

For example:

If Current Status = "Enrolled" then Auto-Enter the Serial Number.

If Current Status = anything but Enrolled then Student Number = Empty.

I tried running a script that read:

If ["Current Status="Enrolled"]

Set Next Serial Value ["Student Number"]

End If

But it wasn't working so I think I'm missing something... And if the script does work some way how could I set it to run every single time the Current Status switches to "Enrolled"...

...Am I making sense?

If it turns out to be too complicated then we might just resort to manually entering the student numbers. It would just be really nice to have them automated. It worked find when our Inquiries database was separate from the Enrollment database, but now they are merged it has become an issue.

I thank you for any advice you can provide me.

[comment]

I am not sure I understand this. Suppose I have these people in my db:

PersonID   Name     Status     Student#

001        Adam     Enrolled   001

002        Betty    Applicant  

003        Cecil    Enrolled   003

Now Betty enrolls, so it becomes:

PersonID   Name     Status     Student#

001        Adam     Enrolled   001

002        Betty    Enrolled   002  

003        Cecil    Enrolled   003

Is this a correct interpretation?

[Calantha]

Thank you for your effort with your replies!

Your reply was almost right on except that anyone who is not enrolled does not have a personal id, they are merely a name until they become enrolled. At enrollment they receive a personal id.

Does that make sense?

[comment]

Not really - it seems we are back at the beginning.

The thing is, numbers have to come from somewhere. When you set a number to auto-enter on record creation, FM knows the next number. That number is not going to change if you delete a record, for example.

When you change a status of an existing record, where is the number supposed to come from? If it is a calculation, then it has to depend on how many records have been enrolled before. That, as I have tried to show before, cannot be made 100% foolproof (even if only one person enters the data).

[Calantha]

Ok, I think I'm finally understanding what it is that you were trying to explain to me before! I apologize for the time it took for me to actually understand what you were saying.

We're currently trying to come up with some sort of calculation that would generate the student numbers based on their current status without having a repeating number!

Thank you for your time!

[comment]

Well, if you do come up with such a calc, let us all know. So far, someone has always managed to come up with a scheme-breaking scenario.

The script I described above seems to me to be the safest way. Unless someone can think of a way to break that too...

[comment]

Let me ammend this a bit. If it was up to me, I'd assign a "preliminary Student Number" to EVERYONE in the db. Then, for those who enroll, I'd turn the preliminary into a real Student Number.

So the real students woudn't have consecutive (from the POV of their enrollments) or contiguous numbers. I wouldn't lose any sleep over that. This is 100% fool-proof.

[Mushi]

If you need to have the student ID numbers be consecutive, you could try using a global nubmer field. Set the global number to 1, then have a button on your layout that says something along the lines of "Generate Student ID" or "Enroll Student" This would trigger a script that

1. Sets the StudentID field to the global field

2. Adds 1 to the global field

This isn't as nice as auto entry, but you can make sure that only students whose status is "Enrolled" get student IDs

[comment]

This will not work if the file is shared. See also here.

[Calantha]

I don't think that will be me. The idea seemed much easier than the actual implementation and we have resorted to entering in the student numbers manually.

B)