Avoid Duplicate Records

[Agentshevy]

I have a simple solution for entering students test scores.  The enrollment table has 4 fields namely : subjects, score, level and semester. 

The subjects values are gotten via a value list,  same with the Level and Semester fields. 

Records are entered from the students layout via a portal 

My challenge is how to avoid students from entering  same subject more than once, because this would affect their calculated result..  Thanks 

Edited October 22, 2015 by shevyshevy

[siroos12]

 

Create a script and assign it to the "Subject" field for "OnObjectSave" to get the student name as a variable, and the "subject" as variable. Then perform a find base on these variables and if the found count is more than "0", it means that this student already choose this subject. Show a custom message box saying that "This subject has been choose already!", then clear the subject field.

 

it should be something like this:

 

Set Variable [ $Student ; Value:test::Student ]
Set Variable [ $Subject; Value:test::Subject ]
Perform Find [ Specified Find Requests: Find Records ; Criteria: test::Student : “$Student” AND test::Subject : “$Subject” ]
[ Restore ]
If [ Get( FoundCount) > 0 ]
Show Custom Dialog [ Message: "This subject has been choose already!"; Default Button: “OK”, Commit: “Yes” ]
Clear [ test::Subject ]
[ Select ]
Else
Exit Script [ ]
End If
 

 

 

 

 

Edited October 22, 2015 by siroos12

[Kris M]

This seems like a data layer problem.

If you define your schema (data model) right then you will be able to set a "unique" validation constraint on the enrollments table..

If you want to retain the current structure of enrollments then make a calc field in enrollments that concatenates student id (this entity should be in enrollments!) , subject, level, and semester and set the calc field to be validated as unique, validate always, and do not allow user to override.

Edited October 22, 2015 by Kris M

[comment]

My challenge is how to avoid students from entering  same subject more than once

There are actually two questions here. The first question is how to make sure no duplicates are created, ever. This is a matter of data integrity and needs to be solved through field validation:

make a calc field in enrollments that concatenates student id (this entity should be in enrollments!) , subject, level, and semester and set the calc field to be validated as unique, validate always, and do not allow user to override.

This is the correct answer - except that calculation fields cannot be validated. The field in question needs to be a Text field, with auto-entered calculated value (replacing existing value).

The other question is how to protect users from having to deal with validation errors. This is a matter of user interface and can be solved in a variety of ways - for example, by a "dwindling" value list.

 

If you define your schema (data model) right then you will be able to set a "unique" validation constraint on the enrollments table..

IMHO, the presented structure is the correct one for the described situation (with the addition of StudentID, as you noted). How else would you handle it?

 

[Kris M]

IMHO, the presented structure is the correct one for the described situation (with the addition of StudentID, as you noted). How else would you handle it?

 

That was my way of suggesting that inserting the student ID into enrollments was needed

[Agentshevy]

The field in question needs to be a Text field, with auto-entered calculated value (replacing existing values) 

The other question is how to protect users from having to deal with validation errors. This is a matter of user interface and can be solved in a variety of ways - for example, by a "dwindling" value list.

Please can you elaborate more on how to achieve this? 

 

If you want to retain the current structure of enrollments then make a calc field in enrollments that concatenates student id (this entity should be in enrollments!) , subject, level, and semester and set the calc field to be validated as unique, validate always, and do not allow user to override.

The enrollment are being entered from the students layout via a portal 

[comment]

On 10/22/2015 at 6:26 PM, shevyshevy said:

Please can you elaborate more on how to achieve this? 

 

On 10/22/2015 at 6:26 PM, shevyshevy said:

The enrollment are being entered from the students layout via a portal 

Makes no difference.

Edited March 21, 2019 by Ocean West
fixed link

[LaRetta]

Here is one of yours, Michael.