Foreign column filter on null value

Question

Hello,Im currently using syncfusion blazor 26.2.4.In the excel filter mode of the data grid, when filtering a foreign column we can only see object that aren't null. I would like to also see the null in the filter dialog. Is it possible ? ThanksAnthony

Prathap Senthil · Answer

Hi Anthony Benoit,Based
on your requirement, we would like to clarify that when using the Excel filter
with null values, we already support filtering for null values. You can use the
'Blank' option in the filter dialog to filter null values in the grid. Kindly
refer to the provided sample for your reference.

Sample:https://blazorplayground.syncfusion.com/embed/LtrTsXsCCnenWMeH?appbar=true&editor=true&result=true&errorlist=true&theme=bootstrap5

Regards,
Prathap Senthil

Anthony Benoit · Answer

HelloIn my filter exemple i dont see the blank option. My foreign id is a string and this is the code This is the filter excel and the result of it Like you see there is no blank checkboxUPDATE:I looked at the code you sent me and this is not what i was talking about. In my exemple, the class Order could accept an employee null so employeeId could be null. With this you aren't able to filter the null value of the employee Thanks Anthony

Prathap Senthil · Answer

Based
on your requirement, we would like to clarify that the GridForeignKey values
displayed in the grid and column filter options are based on the
ForeignKeyValue (e.g., FormattedName in your grid's foreign key column). If any
ForeignKeyValue contains null values, they will appear as blanks in the filter
dialog. This is the default behavior of the DataGrid. Thank you for your
understanding.

Anthony Benoit · Answer

HelloI updated the code you send me from the playground link. The behavior you are telling me is not working. you can't see the blank checkbox@page "/"@using Syncfusion.Blazor.Grids@using Syncfusion.Blazor.DropDowns() { "Add", "Edit", "Delete", "Cancel", "Update" })"> @code { public List Orders { get; set; } public List Employees { get; set; } protectedoverridevoid OnInitialized() { Orders = OrderData.GetAllRecords(); Employees = EmployeeData.GetAllRecords(); } publicclass EmployeeData { publicstatic List Employees = new List(); public EmployeeData() { } public EmployeeData(int? employeeID, string firstName) { EmployeeID = employeeID; FirstName = firstName; } publicstatic List GetAllRecords() { if (Employees.Count() == 0) { int code = 10; for (int i = 1; i < 2; i++) { Employees.Add(new EmployeeData( 1, "Nancy")); Employees.Add(new EmployeeData( 2, "Andrew")); Employees.Add(new EmployeeData( 3, "Janet")); Employees.Add(new EmployeeData( 4, "Nancy")); Employees.Add(new EmployeeData( 5, "")); Employees.Add(new EmployeeData( 6, null)); Employees.Add(new EmployeeData( 7, "Janet")); Employees.Add(new EmployeeData( 8, "Andrew")); Employees.Add(new EmployeeData(9, "Nancy")); code += 5; } } return Employees; } publicint? EmployeeID { get; set; } publicstring FirstName { get; set; } } publicclass OrderData { publicstatic List Orders = new List(); public OrderData() { } public OrderData(int? OrderID, int? EmployeeID, string ShipCity, double? Freight) { this.OrderID = OrderID; this.EmployeeID = EmployeeID; this.ShipCity = ShipCity; this.Freight = Freight; } publicstatic List GetAllRecords() { if (Orders.Count() == 0) { int code = 10; for (int i = 1; i < 2; i++) { Orders.Add(new OrderData(10248,1, "Reims", 32.18)); Orders.Add(new OrderData(10249,2, "Münster",33.33)); Orders.Add(new OrderData(10250,3, "Rio de Janeiro",12.35)); Orders.Add(new OrderData(10251,4, "", 22.65)); Orders.Add(new OrderData(10252,null, "Lyon", 63.43)); Orders.Add(new OrderData(10253,null, "Charleroi",56.98)); Orders.Add(new OrderData(10254,null, "Rio de Janeiro", 45.65)); Orders.Add(new OrderData(10255,null, "Münster", 11.13)); Orders.Add(new OrderData(10256,null, "Reims", 87.59)); code += 5; } } return Orders; } publicint? OrderID { get; set; } publicint? EmployeeID { get; set; } publicstring ShipCity { get; set; } publicdouble? Freight { get; set; } }}Test this and filter the employee foreign column and youll be only able to see 4 records and not theblank checkbox. This is my problem how could i filter to see only the null valueThanks Anthony

Prathap Senthil · Answer

Yes, we would like to clarify that the GridForeignKey
values displayed in the grid's filter dialog, as well as the filtering
functionality, are based on the ForeignKeyValue, not on the ForeignKeyColumn
Field or ForeignKeyField values. Since in your shared code, the
GridForeignKeyColumn Field value (<GridForeignColumn
Field=@nameof(OrderData.EmployeeID)) is null, blank values are not shown in the
filter dialog. This is the default behavior.

Andy Terbovich · Answer

Hello, I believe that I properly understood your explainer as to the null ForeignKeyValue, but can you please give an example of how to get a Blank option into the checkbox filter dialog for data rows that have a null GridForeignKeyColumn Field value?

I don't understand why it would be desirable for the default behavior to be based on the null value of the displayed ForeignKeyValue, when the data shown in the grid is based off of the Field value. Depending on the data structure, the ForeignKeyValue and ForeignKeyField may not even be nullable values so you can't even insert some dummy null lookup object for the filter, but it's entirely possibly that the Column Field is nullable. Unless I'm missing something, this seems to be backwards.

Are we obligated to use the Excel filter to get "Blanks" filtering? I would much prefer to use the simple Checkbox filter.

Prathap Senthil · Answer

Based
on your query, we would like to inform you that in a DataGrid, a foreign key
column must be correctly configured using three important properties:
`ForeignDataSource`, `ForeignKeyField`, and `ForeignKeyValue`. 1.
The `ForeignDataSource` specifies the collection that contains the lookup
values.2.The
`ForeignKeyField` maps the column in the grid to the matching field in the
foreign data source.3.The
`ForeignKeyValue` defines which related field's text value should be displayed
in the grid.The
mapping process works by taking the value from the grid's data, searching for a
match in the `ForeignKeyField` of the `ForeignDataSource`, and then displaying
the corresponding `ForeignKeyValue`. For example, if the grid has `EmployeeID =
1`, it will look for ID 1 in the foreign data source and display the employee’s
name. However, in your case, `EmployeeID = 1` is defined in the grid class data
source as having a null ID value for many rows, so the foreign key value is not
displayed. If the mapping is not done correctly, the grid cannot find and
display the correct value. Please
ensure that both the grid data source ID value and the foreign key data source
are present. Refer to the code snippet and sample for more information:Sample-(https://blazorplayground.syncfusion.com/embed/hXBoNJhLUTtJHlIz?appbar=true&editor=true&result=true&errorlist=true&theme=bootstrap5)Documentation-(https://blazor.syncfusion.com/documentation/datagrid/foreignkey-column)Additionally,
the grid Excel filter is designed based on the Excel sheet filter, so it
displays the built-in “Blanks” option when working with null or undefined
values, especially in `GridForeignKeyColumn`. When using the Checkbox filter,
if the foreign key field value is null, it will show the display option for
“Blank.”Thanks for your understanding.

Andy Terbovich · Answer

I reviewed my grid set up and it properly has all three items defined, and it is exactly equivalent to Anthony's set up.

I also reviewed your code sample in your last post, and although it is showing the "Blank" filter option, it is set up in a way that doesn't model expected usage and doesn't reflect what Anthony and I are asking.

Your example shows a blank because your Employees list contains EmployeeData entries with a null and/or blank display value for Employee Name, but they contain a value for the Employee.Id field. Your GetAllRecords() method returns orders with EmployeeIds pointing to those records (Ids 5 and 6).

Yes, this does give you a blank option filter, but in real-world implementations we wouldn't have a dummy record in the Employee list that just points to a blank Employee with valid Id but no display information. That would violate a lot of our data integrity concerns.

In typical set ups, you would have an order that contains a null value for the OrderData.EmployeeId, like this:

public static List<OrderData> GetAllRecords() //Abbreviated for length
{
  if (Orders.Count() == 0)

  {
int code = 10;
for (int i = 1; i < 2; i++)
    {
      Orders.Add(new OrderData(10252,null, "Lyon", 63.43));  //<-- NULL EmployeeID
      Orders.Add(new OrderData(10253,null, "Charleroi",56.98)); //<-- NULL EmployeeID
      code += 5;
    }
  }
return Orders;
}

Is there any way to have the filter automatically display Blank options in the Employee Name filter on the grid for when the EmployeeId is null and NOT the Employee Name in the associated data list?

I tried to edit your example, but I'm not sure if the changes are persisted.

https://blazorplayground.syncfusion.com/embed/LtrTsXsCCnenWMeH?appbar=true&editor=true&result=true&errorlist=true&theme=bootstrap5

Attachment: GridFilter_5fd790a4.PNG

Prathap Senthil · Answer

Thank you for your update.As mentioned in our previous communication,
the values shown in the grid's filter dialog and the filtering functionality
are based on the ForeignKeyValue, not the ForeignKeyField or the field
used in the GridForeignKeyColumn. This is the default and intended behavior of
the Grid component.So not feasible to apply filtering based on the value of the
foreign key field itself. The ForeignKeyField is used to map the grid column to
the corresponding field in the foreign data source, but filtering operations
rely on the display value (ForeignKeyValue).We
truly appreciate your understanding on this matte

Andy Terbovich · Answer

Understood. Although this may be the current intended design, I would present that it is not the preferred behavior.

How can I open a ticket or feedback item to request a change in this behavior so that nulls in the ForeignKeyField also trigger the Blank filter option?

Since the grid itself is already properly displaying blank cells where the calling ForeignKeyField is null, it is counterintuitive to not present a "blank" filter option using this set up.

Given that Foreign Keys are often required, non-nullable integer Ids from the displayed foreign class itself, adding in a dummy value to the displayed ForeignDataSource where that Id is null just to trigger a blank display is a non-trivial matter.

Naveen Palanivel · Answer

Hi Andy Terbovich,

We are currently validating the feasibility of achieving your requirement as a
high priority on our end and will update you with further details by May 19th,
2025. Until then, we appreciate your patience.
Regards,
Naveen

Prathap Senthil · Answer

Hi Andy Terbovich,We sincerely apologize for the delay and any
inconvenience caused.Based
on your requirement, we have evaluated the feasibility on our end.
Unfortunately, we regret to inform you that the requested feature is currently
not feasible. Thank you for your understanding and cooperation.Regards,
Prathap Senthil