1. Home
  2. Forum
  3. jQuery
  4. Sorting on Foreign Key value locally

Sorting on Foreign Key value locally

I am trying to figure out how to make my columns sort on the foreign key value (e.g., Greek letter names should be sorted) rather than the foreign key id.  Data sources for both the grid and foreign key column are locally stored on client.  However, the sorting is done on the ID instead.

I tried using a ForeignKeyAdaptor, but can't get it to work.

var OrganizationObjects = [{ID: 1, Name: "Gamma", ShortName: "ShortName1", Description: "Description"} ,
{ID: 2, Name: "Zeta", ShortName: "ShortName2", Description: "Description"},
{ID: 3, Name: "Alpha", ShortName: "ShortName3", Description: "Description"},
{ID: 4, Name: "Delta", ShortName: "ShortName4", Description: "Description"}];

var OrganizationOrganizationObjects = [{ID: 1, ParentID: 2, ChildID: 1},
{ID: 2, ParentID: 3, ChildID: 2},{ID: 3, ParentID: 4, ChildID: 3}];

function refreshOrgOrgs() {
            $("#orgOrgsTable").ejGrid({
                allowReordering: true,
                allowMultiSorting: true,
                allowPaging: true,
                allowSorting: true,
                allowFiltering: true,
                allowTextWrap: true,
                allowResizing: true,
                allowHiding: true,
                editSettings: {
                    allowEditing: true,
                    allowAdding: true,
                    allowDeleting: true,
                    editMode: "inlineform"
                },
                selectionSettings: { allowSelection: true, selectionType: "single" },
                unHideColsOnDblClick: true,
                toolbarSettings: {
                    showToolbar: true,
                    toolbarItems: ["add", "edit", "delete", ej.Grid.ToolBarItems.Search],
                    backgroundColor: "lightblue"
                },
                toolbarClick: "onToolbarClick",
                pageSettings: { pageSize: 5, pageSizeList: [5, 10, 20, 30, 40, 50] },
                filterSettings: { filterType: "Menu" },
                columns: [
                    { field: "ID", headerText: "ID", textAlign: ej.TextAlign.Left, visible: false, isPrimaryKey: true },
                    {
                        field: "ParentID", headerText: "Parent Organization", foreignKeyField: "ID", foreignKeyValue: "Name",
                        headerTemplateID: "#requiredUniqueParentOrganizationTemplate", dataSource: OrganizationObjects,
                        textAlign: ej.TextAlign.Left, visible: true
                    },
                    {
                        field: "ChildID", headerText: "Child Organization", foreignKeyField: "ID", foreignKeyValue: "Name",
                        headerTemplateID: "#requiredUniqueChildOrganizationTemplate", dataSource: OrganizationObjects,
                        textAlign: ej.TextAlign.Left, visible: true
                    }
                ],
                sortSettings: { sortedColumns: [{ field: "ParentID", direction: "ascending" }] },
                actionBegin: "begin"
            });

            var orgOrgsGridObj = $("#orgOrgsTable").ejGrid("instance");
            orgOrgsGridObj.dataSource(OrganizationOrganizationObjects);
}

8 Replies
GL Gowri Loganathan Syncfusion Team April 9, 2020 01:16 PM UTC

  
Hi Megan, 
 
Thanks for contacting Syncfusion Forum. 

Query : Sorting on Foreign Key value locally

When you use foreignKeyField name which is different from the Grid fieldname, we have to define the foreignKeyField along with field in the ForeignKeyAdaptor and initialize the ForeignKeyAdaptor to ejGrid in the load event.

Grid column field name must be a combination of Grid field name and foreignKeyField name separated by “_” 

Code snippet

. . . . . . .  
<script type="text/javascript"> 
    var OrganizationObjects = [. . .] 
 
    var Objects = [. . . ] 
 
    var foreignObj = [ 
        { 
            dataSource: OrganizationObjects, 
            field: "ParentID", 
            foreignKeyField: "ID", //Property in the Grid's main as well as foreignKey dataSource 
            foreignKeyValue: "Name" //Property in foreignkey dataSource 
        }, 
{ 
            dataSource: OrganizationObjects, 
            field: "ChildID", 
            foreignKeyField: "ID", //Property in the Grid's main as well as foreignKey dataSource 
            foreignKeyValue: "Name" //Property in foreignkey dataSource 
        }]; 
 
    $(function () { 
        $("#Grid").ejGrid({ 
            dataSource: Objects, 
            
            allowSorting: true, 
            sortSettings: { sortedColumns: [{ field: "ParentID_Name", direction: "ascending" }] }, 
             
            load: function (args) { 
                this.model.dataSource.adaptor = new ej.ForeignKeyAdaptor(foreignObj, "JsonAdaptor");  // bind ForeignKeyAdaptor here 
            }, 
            columns: [ 
                { field: "ID", headerText: "ID", textAlign: ej.TextAlign.Left, visible: false, isPrimaryKey: true }, 
                { 
                    field: "ParentID_Name", headerText: "Parent Organization", 
                    headerTemplateID: "#requiredUniqueParentOrganizationTemplate", dataSource: OrganizationObjects, 
                    textAlign: ej.TextAlign.Left, visible: true 
                }, 
                { 
                    field: "ChildID_Name", headerText: "Child Organization", foreignKeyField: "ID", foreignKeyValue: "Name", 
                    headerTemplateID: "#requiredUniqueChildOrganizationTemplate", dataSource: OrganizationObjects, 
                    textAlign: ej.TextAlign.Left, visible: true 
                } 
            ], 
 
        }); 
    }); 

</script>

Sample Link : https://jsplayground.syncfusion.com/r1lhblq2

Also refer to the screenshot 
 

Please refer the below help documentation link,

https://help.syncfusion.com/js/grid/columns#foreignkeyfield-name-differs-with-grid-field-name

 
Please get back to us if you need more assistance on this. 
 
Regards, 
Gowri V L 
 
 


MM Megan Moore April 9, 2020 02:48 PM UTC

That worked perfectly!  Thank you.  I was almost there when I tried the adaptor, but did not name the field as the combination field_foreignKeyValue, nor did I put that new name in the sortSettings.

MM Megan Moore April 9, 2020 05:48 PM UTC

Well, now my add/edit functions do not work anymore.  The foreign key fields, which used to display the drop down list of the field I chose to display are just text fields.  Any suggestions?

GL Gowri Loganathan Syncfusion Team April 14, 2020 02:22 PM UTC

Hi Megan, 
 
Sorry for the delayed response. 
 
Query : Edit/Add operation not works  
When we use foreignKeyField name which is different from the Grid fieldname, while editing the dropdown list is not render which is the default behavior due to the field mismatch. But we can achieve your requirement using editTemplate feature of ejGrid. And also, we suggest you to use the below code example to update the foreignkey adaptor. 
 
Code snippet 
 
 
$("#Grid").ejGrid({ 
 
    actionComplete: function (args) { 
        if (args.requestType == "refresh" && this.initialRender && this._dataSource() == null) { 
            this.dataSource(ej.DataManager({ 
                json: ej.parseJSON(Objects), 
                adaptor: new ej.ForeignKeyAdaptor(foreignObj, "JsonAdaptor") 
            })); 
        } 
    }, 
columns: [ 
{ 
    field: "ParentID_Name", headerText: "Parent Organization", 
       headerTemplateID: "#requiredUniqueParentOrganizationTemplate", dataSource: OrganizationObjects, 
            textAlign: ej.TextAlign.Left, visible: true, 
          editTemplate: { 
            create: "create", 
            read: "read", 
             write: "write" 
    } 
}, 
{ 
    field: "ChildID_Name", headerText: "Child Organization", 
       headerTemplateID: "#requiredUniqueParentOrganizationTemplate", dataSource: OrganizationObjects, 
            textAlign: ej.TextAlign.Left, visible: true, 
        editTemplate: { 
            create: "create1", 
            read: "read1", 
           write: "write1" 
    } 
}    ], 
                
    }); 
 }); 
      
       
    <script> 
        function create() {  
              return $("<input>"); 
} 
          function write(args) {  
              var gridObj = $(".e-grid").ejGrid("instance"); // Get grid object 
          var val = ej.isNullOrUndefined(args.rowdata["ParentID_Name"]) ? "" : args.rowdata["ParentID_Name"]; 
              args.element.ejDropDownList({ 
 
                dataSource: args.column[1].dataSource, 
                value: args.rowdata.ParentID_Name, 
                fields: {text: "Name", value: "ParentID_Name" }, 
    
      }) 
  } 
          function read(args) {  
              return args.ejDropDownList("getSelectedValue");   //return the selected value in dropdown list 
        
      } 
           function create1() {  
              return $("<input>"); 
      } 
          function write1(args) {  
              var gridObj = $(".e-grid").ejGrid("instance"); // Get grid object 
                  var val = ej.isNullOrUndefined(args.rowdata["ChildID_Name"]) ? "" : args.rowdata["ChildID_Name"]; 
              args.element.ejDropDownList({ 
 
                    dataSource: args.column[1].dataSource, 
            value: args.rowdata.ChildID_Name, 
                fields: {text: "Name", value: "ChildID_Name" }, 
 
              }) 
          } 
          function read1(args) {  
              return args.ejDropDownList("getSelectedValue");   //return the selected value in dropdown list  
                  
              }  
      </script> 
 
 
 
Kindly refer the below help documentation links, 
 
Please get back to us if you need more assistance. 
 
Regards, 
Gowri V L. 
 


MM Megan Moore April 15, 2020 06:58 PM UTC

Thank you.  This code does seem to work, but I do have a few more questions.  

1.  Since this grid example is only 1 of about 25 different grids I'm using, each with 1 or more foreign key columns, I wondered about re-using the same create and read functions in multiple editTemplates since those are not dependent upon the actual column name?

2.  If reusing the functions is acceptable, then is there also a way to pass in the column's foreignKeyName, foreignKeyValue, and column index to the write function in order to reuse that function as well?

3.  Is there a way to define a default value from one of these foreign key columns?

4.  Are the GridObj and val variables used for anything in the write function?



FS Farveen Sulthana Thameeztheen Basha Syncfusion Team April 16, 2020 03:44 PM UTC

Hi Megan, 

As per your requirement, we have used the unique values for the write function to be reused for multiple foreignKey columns. Refer to the code example:- 

             <script type="text/javascript"> 
 
    $(function () { 
            $("#Grid").ejGrid({ 
                allowReordering: true, 
                allowMultiSorting: true, 
                 
 
              columns: [ 
                    { field: "ID", headerText: "ID", textAlign: ej.TextAlign.Left, visible: false, isPrimaryKey: true }, 
                    { 
                        field: "ParentID_Name", headerText: "Parent Organization", 
                        headerTemplateID: "#requiredUniqueParentOrganizationTemplate", dataSource: OrganizationObjects, 
                        textAlign: ej.TextAlign.Left, visible: true, 
                        editTemplate: { 
                            create: "create", 
                            read: "read", 
                            write: "write" 
                        } 
                    }, 
                    { 
                        field: "ChildID_Name", headerText: "Child Organization", 
                        headerTemplateID: "#requiredUniqueChildOrganizationTemplate", dataSource: OrganizationObjects, 
                       textAlign: ej.TextAlign.Left, visible: true, 
                      editTemplate: { 
                            create: "create", 
                            read: "read", 
                            write: "write" 
                        } 
                    } 
                ], 
 
            }); 
        }); 
 
            </script> 
            <script> 
                         function create() { 
                              return $("<input>"); 
                          } 
                          function write(args) { 
                              var field = $(args.element)[0].name; 
                              var text = field.split('_')[1]; 
                              var gridObj = $(".e-grid").ejGrid("instance"); // Get grid object 
                              var val = ej.isNullOrUndefined(args.rowdata[field]) ? "" : args.rowdata[field]; 
                              args.element.ejDropDownList({ 
 
                                dataSource: args.column[1].dataSource, 
                                value: val, 
                                fields: {text: text, value: field }, 
 
                          }) 
                     } 
                          function read(args) { 
                              return args.ejDropDownList("getSelectedValue");   //return the selected value in dropdown list 
 
                        } 
            </script> 

Sample Link:- 

Please get back to us if you need any further assistance. 

Regards, 
Farveen sulthana T 


MM Megan Moore April 16, 2020 07:19 PM UTC

Thanks for the tips...this almost gets me there.  

The foreign key ID column values are not kept up-to-date in the grid datasource.  The new column in the datasource that contains the "_" has the correct value, but the associated foreign key ID does not.  What do I have to do to keep that updated?  

For example, using the code we started with, once ParentID becomes ParentID_Name and ChildID becomes ChildID_Name, the ParentID and ChildID values are no longer maintained and thus are out of sync with the values of ParentID_Name and ChildID_Name once they are updated.

GL Gowri Loganathan Syncfusion Team April 17, 2020 11:11 AM UTC

Hi Megan, 

Thanks for the update. 

Query: ParentID and ChildID values are no longer maintained and thus are out of sync with the values of ParentID_Name and ChildID_Name once they are updated. 

From the query we understood that the parentID and ChildID values doesn’t get updated while updating the parentID_Name and childID_Name, but we are unable to reproduce the reported issue at our end.  


For your convenience, we have attached here the video demo to show you the updated values in parentID and childID. Please refer to the below link, 

Still facing an issue, please get back to us with the below details. 
  1. Kindly reproduce the reported issue in the shared sample Or share the issue reproducible sample.
  2. Please share us the video demonstration of the reported issue.
 

Regards, 
Gowri V L 
 


Loader.
Up arrow icon