join not working

mmontoya

I am having an issue with join. The select list is blank for new records. If a record was there already, I see the correct value, but clicking the select box it will be blank.

Also, if I attempt to add a record, leaving the select box blank, I also get a blank record in the table that the left join is pulling from (Departments)

Editor::inst( $db, 'DepartmentGoals', 'GoalID' )
    ->fields(
        Field::inst( 'DepartmentGoals.DepartmentID' )->validator( 'Validate::notEmpty' )
                ->options( 'Departments', 'DepartmentID', 'Department' ),
        Field::inst( 'Departments.Department'),
        Field::inst( 'DepartmentGoals.Goal' )->validator( 'Validate::notEmpty' ),
        Field::inst( 'DepartmentGoals.EffectiveDate' )
                ->validator( 'Validate::dateFormat', array(
                                "format"  => Format::DATE_ISO_8601,
                                "message" => "Please enter a date in the format yyyy-mm-dd"
                            ) )
                            ->getFormatter( 'Format::date_sql_to_format', Format::DATE_ISO_8601 )
                            ->setFormatter( 'Format::date_format_to_sql', Format::DATE_ISO_8601 )       
    )
    ->leftJoin('Departments','Departments.DepartmentID','=','DepartmentGoals.DepartmentID')
    ->process( $_POST )
    ->json();

the results I get in looking at the console are:

data: [,…]
0: {DT_RowId: "row_2", DepartmentGoals: {DepartmentID: "1", Goal: "40", EffectiveDate: "2015-02-01"},…}
options: {,…}
DepartmentGoals.DepartmentID: [{value: "1", label: "InBound"}, {value: "3", label: "Quality Control"},…]
0: {value: "1", label: "InBound"}
1: {value: "3", label: "Quality Control"}
2: {value: "2", label: "Returns"}

allan

Are you able to line to the page please, so I can take a look and see what is going wrong. Also can you confirm that you are using Editor 1.4.0?

Thanks,
Allan

mmontoya

http://www.uniquedatasolutions.biz/511/companyProfile.php

username: removed
password: removed

allan

The "Department" field in Editor is defined as:

name: "Departments.Department",

However the Ajax is returning a list of options for:

DepartmentGoals.DepartmentID

There is a discrepancy there. I would suggest changing the field name (assuming that Departments.Department is incorrect there).

Allan

mmontoya

What line in the code above are you referring to?

What the select should have is departmentID for value and Department for label, coming from the table Departments.

mmontoya

to clarify:

Table: Departments
Fields: DepartmentID, Department

Table: DepartmentGoals
Fields: GoalID, DepartmentID (foreign key), Goal, EffectiveDate

It seems to be displaying the data correctly for the one record that I added manually.

mmontoya

Allan,

I was able to get it mostly working. The 'Add' form is working correctly, the select list displays correctly there.

But now the select list is not displaying in the DataTable correctly. How do I add the options there?

mmontoya

Ok Allan. I was able to get it working.
One problem is I didn't see the 'editField' option in the columns definition:
{data: "Departments.Department", editField: "DepartmentGoals.DepartmentID" }

mmontoya

Thank you very much for your time.