Generating Primary Key ID with Editor

dojocasino

I have created an editable table for student exam results. While the results can be created and edited, and successfully stored on our SQL server, I have so far been unable to generate the primary key ID for new records as I'd like to.

The primary key IDs for our exam results database consist of a student ID, module ID and examboard ID concatenated together, e.g. if a student has an ID of 0012, the module ID is 0005 and the examboard ID is 0010 then the primary key ID for the exam board results table would be 001200050010.

After reading through the Event Sequence I have attached the following code to the preSubmit event to generate the ID:

editor.on('preSubmit', function (e, o, action) {
if (action == 'create') {
     editor.field('ExamBoardResults.ID').set((this.get('ExamBoardResults.StudentID')) + (this.get('ExamBoardResults.ModuleID')) + this.get('ExamBoardResults.ExamBoardID'));
}
});

Upon pressing the Create button in the New Entry window, the correct ID flashes up briefly in the ExamBoardResults.ID field briefly before the record saves, however it does not save anything in the ID field in the database. I suspect this may be due to the ID field not being set early enough after pressing the Create button, however from the Event Sequence it seems to me that the preSubmit event is the first one to occur after the button is pressed.

A screenshot of my entry creation window is here: http://i.imgur.com/qxVkw9t.png

dojocasino

Just wondering if anyone knows how I can manually set the primary key ID of newly created rows in the way I've mentioned above.

Mine are still showing up in the database as blank, which is causing some erratic behaviour when later editing them in DataTables. The database is not configured to automatically increment the PK ID for new records.

tangerine

Why do you need a primary key in that format? An auto-increment field would be simpler.

dojocasino

I agree with you, however the system I'm developing is to work alongside an existing one which uses that format for the PK ID.

allan

Hi,

I would suggest not having the client-side create your primary key, but rather do it on the server-side. Doing it client-side is a security risk I would suggest and leave the system open to a good hacking :-)

Are you using the Editor PHP libraries? The 1.4 libraries might be of some interest as they make it relatively easy to do this (it is possible in 1.3, but its a bit more difficult):

Field::inst( 'id' )
  ->setValue( /* the generated primary key goes here */
  ->set( Field::SET_CREATE )
  ->get( false )

So basically it gives you the option of setting the value on create only, saying what the value to set is (based on whatever calculations you want to perform) and also tells Editor not to read id, since it will do that automatically as the primary key anyway.

Allan

dojocasino

Hi Allan,

I was using Editor 1.3.3 but have just upgraded the libraries to 1.4. I've altered my PHP to reflect what you've supplied above:

$data = Editor::inst($db, 'ExamBoardResults')
->fields(
Field::inst('Student.Surname'),
 Field::inst('Student.Forename'),
 Field::inst('Student.StudentID1'),
 Field::inst('Modules.ModuleCode'),
 Field::inst('Modules.ModuleShortTitle'),
 Field::inst('CourseParts.PartDescription'),
 Field::inst('ExamBoards.ExamBoard'),
 Field::inst('ExamBoardResults.MCQScore'),
 Field::inst('ExamBoardResults.OverallMark'),
 Field::inst('ExamBoardResults.Notes'),
 Field::inst('ExamBoardResults.Decision'),
 Field::inst('ExamBoardResults.CWOutcomeID'),
 Field::inst('ExamBoardResults.StudentID'),
 Field::inst('ExamBoardResults.ModuleID'),
 Field::inst('ExamBoardResults.ExamBoardID'),
 Field::inst('ExamBoardResults.CWNotes'),
 Field::inst('CourseWorkOutcomes.Outcome'),

 Field::inst('ExamBoardResults.ID')
    ->setValue( 'test' )
    ->set( Field::SET_CREATE )
    ->get( false )
)

//        Table Joins
        ->leftJoin('ExamBoards', 'ExamBoards.ID', '=', 'ExamBoardResults.ExamBoardID')
        ->leftJoin('Modules', 'Modules.ModuleID', '=', 'ExamBoardResults.ModuleID')
        ->leftJoin('Student', 'Student.StudentID', '=', 'ExamBoardResults.StudentID')
        ->leftJoin('CourseWorkOutcomes', 'CourseWorkOutcomes.ID', '=', 'ExamBoardResults.CWOutcomeID')
        ->leftJoin('CourseParts', 'CourseParts.PartID', '=', 'Modules.CoursePartID')
        ->process($_POST)
        ->data();

if (!isset($_POST['action'])) {
    // Get a list of values for the select lists

    $data['ModuleCodes'] = $db
            ->selectDistinct('Modules', 'ModuleID as value, ModuleCode as label')
            ->fetchAll();

    $data['CWOutcomes'] = $db
            ->selectDistinct('CourseWorkOutcomes', 'ID as value, Outcome as label')
            ->fetchAll();

    $data['CourseParts'] = $db
            ->selectDistinct('CourseParts', 'PartID as value, PartName as label')
            ->fetchAll();

    $data['ExamBoards'] = $db
            ->selectDistinct('ExamBoards', 'ID as value, ExamBoard as label')
            ->fetchAll();

    $data['Students'] = $db
            ->selectDistinct('Student', 'StudentID as value, StudentID1 as label')
            ->fetchAll();
}

echo json_encode($data);

And it will duly create a new record with the ID of 'test', however when I initially load up the page I get a Requested unknown parameter 'ExamBoardResults.ID' for row 0 error.

I initially attempted to create the composite ID with:

->setValue('ExamBoardResults.StudentID' . 'ExamBoardResults.ModuleID' . 'ExamBoardResults.ExamBoardID')

but it seems to be taking the literal content of the single-quotes (ExamBoardResults.StudentIDExamBoardResults.ModuleIDExamBoardResults.ExamBoardID), rather than the content of the fields themselves.

dojocasino

Thanks for your help Allan, I have discussed this with one of the other developers here and we have changed the SQL server table to auto-increment instead, which has given us the desired result.

allan

->setValue('ExamBoardResults.StudentID' . 'ExamBoardResults.ModuleID' . 'ExamBoardResults.ExamBoardID')

That looks like you are trying to concatenate SQL fields which wouldn't work. The value that is given to setValue is the value that would be used. In this case just a big long string as you noted!

Good you hear you have a solution though.

Allan