Data grid - foreign key column validation and filtering

[Petr]

Dear friends,

I have two problems with the grid component, which otherwise works really nicely.

I'm using the most recent release of Syncfusion.

Could you perhaps tell me what am I doing wrong here?

1) Foreign key column validation does not work.

Other columns are correctly marked as required (see the image below), but the dropdown is not, even though I'm using the same settings.

I really cannot figure out why.

I use a little script bound to actionBegin as a workaround. Would it be possible to format the error message to look exactly the same as the default ones?

See the code, below:

@Html.EJS().Grid("DataGridA").DataSource(dataManager =>

{

dataManager.Json(ViewBag.dataSource).InsertUrl("/ConstructionApprovalModels/Insert").RemoveUrl("/ConstructionApprovalModels/Remove").UpdateUrl("/ConstructionApprovalModels/Update").Adaptor("RemoteSaveAdaptor");

}).AllowSorting().AllowFiltering().ActionBegin("actionBegin").Columns(col =>

{

col.Field("ConstructionID").IsPrimaryKey(true).IsIdentity(true).Visible(false).Add();

col.Field("ProjectTypeID").Visible(false).DefaultValue(@Model.ProjectTypeID).Add();

col.HeaderText("Číslo").Columns(

new List() {

new Syncfusion.EJ2.Grids.GridColumn { Field = "ListLevel", Width = "50", HeaderText = "", AllowFiltering = false, AllowSorting = false, ValidationRules = new {required = true } },

new Syncfusion.EJ2.Grids.GridColumn { Field = "ListSubLevel", Width = "50", HeaderText = "", AllowFiltering = false, AllowSorting = false }

}).Add();

col.Field("CategoryID").ForeignKeyField("CategoryID").ForeignKeyValue("Name").DataSource((IEnumerable<object>)ViewBag.categories).HeaderText("Kategorie").Width("150").ValidationRules(new { required = true }).Add();

col.Field("Description").HeaderText("Doklad").AllowFiltering(false).ValidationRules(new { required = true }).Add();

col.Field("CompanyRoleInProjectID").HeaderText("Zajistí").Width("120").ForeignKeyValue("Short").ForeignKeyField("CompanyRoleID").DataSource((IEnumerable<object>)ViewBag.participants).EditType("dropdownedit").AllowFiltering(false).Add();

}).EditSettings(edit => { edit.AllowDeleting(true).AllowAdding(true).AllowEditing(true).ShowDeleteConfirmDialog(true).Mode(Syncfusion.EJ2.Grids.EditMode.Dialog); }).AllowGrouping().GroupSettings(group => group.ShowDropArea(false).Columns(new string[] { "ConstructionApprovalCategoryID" })).Toolbar(toolbarItems).FilterSettings(filter => { filter.Type(Syncfusion.EJ2.Grids.FilterType.Excel); }).AllowSelection().SelectionSettings(select => select.Type(Syncfusion.EJ2.Grids.SelectionType.Single)).QueryCellInfo("customiseCell").Render()

2) OR between filter columns

In other view, I want to set up two-column default filter and have OR predicate between them.

However, following settings still uses AND. Is there some settings I can change or do I need to write a custom javascript function to accomplish this.

List<object> filterColumns = new List<object>();

filterColumns.Add(new { field = "Dokument.AuthorID", matchCase = false, @operator = "equal", predicate = "or", value = User.Identity.GetUserId()});

filterColumns.Add(new { field = "User.FullName", matchCase = false, @operator = "equal", predicate = "or", value = @ViewBag.CurrentUser});

Thank you for your help!

Best regards,

Petr

[Thiyagu Subramani]

Hi Petr, 

Thanks for contacting Syncfusion forum. 

Query: Foreign key column validation does not work. 

 

Based on your shared information we have prepared and checked a sample as per your requirement with latest package. But unfortunately your reported issue “Foreign key column validation does not work” doesn’t reproduced our side. Please refer to the below code and sample link. 

@Html.EJS().Grid("DefaultPaging").DataSource(ds => ds.Url("/Home/UrlDatasource").Adaptor("UrlAdaptor").InsertUrl("/Home/Insert").RemoveUrl("/Home/Remove").UpdateUrl("/Home/Update")).Columns(col => 

{ 

 

    col.Field("OrderID").HeaderText("Order ID").Width("120").IsPrimaryKey(true).ValidationRules(new { required = true }).TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add(); 

    col.Field("CustomerID").HeaderText("Customer Name").Width("170").Add(); 

 

    col.Field("Id").HeaderText("Customer ID").DataSource((IEnumerable<object>)ViewBag.Species).ForeignKeyField("Id").ForeignKeyValue("name").ValidationRules(new { required = true }).Width("170").Add(); 

 

    col.Field("OrderDate").HeaderText("Order Date").Width("130").EditType("datepickeredit").Format("yMd").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add(); 

    col.Field("Freight").HeaderText("Freight").Width("120").Format("C2").TextAlign(Syncfusion.EJ2.Grids.TextAlign.Right).Add(); 

    col.Field("ShipCountry").EditType("dropdownedit").HeaderText("Ship Country").ValidationRules(new { required = true }).Width("150").Add(); 

 

}).Height("400").AllowPaging().Toolbar(new List<string> 

        () { "Add", "Edit", "Delete", "Update", "Cancel" }).AllowFiltering().FilterSettings(filter => filter.Columns(filterColumns).Type(Syncfusion.EJ2.Grids.FilterType.Excel)).EditSettings(edit => { edit.AllowEditing(true).AllowAdding(true).AllowDeleting(true).Mode(Syncfusion.EJ2.Grids.EditMode.Dialog); }).Render() 

Screenshot:  

 

 

If still facing the issue, please share below details then only we provide the appropriate solution as soon as possible. 

1.  Share Syncfusion and NuGet package version 

2. If possible, Share the issue reproducing sample or reproduce the issue in below sample and revert to us. 

Query -2 : OR between filter columns 

By default, If you want to filter two columns in grid , first column will be filtered from grid data and then next column will be filtered from previously filtered data ( like AND actions). Its default behavior of current grid architecture. So based on your suggestion we have achieved OR filter between 2 columns by overriding our filterQuery method in sample (workaround way) level. Please refer to the below code screenshot. 

 

@{ 

    List<object> filterColumns = new List<object>(); 

    filterColumns.Add(new { field = "CustomerID", matchCase = false, @operator = "equal", predicate = "or", value = "ANATR" }); 

    filterColumns.Add(new { field = "ShipCountry", matchCase = false, @operator = "equal", predicate = "or", value = "Austria" }); 

} 

 

<div class="control-section"> 

    @Html.EJS().Grid("DefaultPaging").Load("load"). . . . .  .  

AllowFiltering().FilterSettings(filter => filter.Columns(filterColumns).Type(Syncfusion.EJ2.Grids.FilterType.Excel)).EditSettings(edit => { edit.AllowEditing(true).AllowAdding(true).AllowDeleting(true).Mode(Syncfusion.EJ2.Grids.EditMode.Dialog); }).Render() 

</div> 

 

 

 

<script> 

    function load() { 

         this.getDataModule().filterQuery = filterFunction; 

    } 

    function filterFunction(query, column, skipFoerign) { 

                 . . . . . . . 

            if (predicateList.length) { 

                query.where(ej.data.Predicate.or(predicateList)); 

            } 

            else { 

                this.parent.notify(events.showEmptyGrid, {}); 

            } 

        } 

        return query; 

    } 

</script> 

 

Screenshot:  

 

 

 

Sample link: https://www.syncfusion.com/downloads/support/forum/160166/ze/293307-3341673741867142075 

 

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

Regards,  

Thiyagu S