GridFilterBar and Foreign Keys in a GridGroupingControl

[Alice Graham]

I have the same problem as described in
http://www.syncfusion.com/support/Forums/message.aspx?MessageID=69177
but the difference is I am using a GridGroupingControl.

The problem with the given solution is that WireGrid is not available for GGC.

Can you please advise on how I can achieve the same as above but for GGC.

[haneefm]

Hi Alice,

Here is a minimal sample on using foreignkeys with datatables.If you add a filterbar to it, you will see the filter dropdown contains the DisplayMember and not the ValueMember. Below is the code snippet for adding filterbar in a GroupingGrid.

gridGroupingControl1.TopLevelGroupOptions.ShowFilterBar = true;
foreach( GridColumnDescriptor gd in this.gridGroupingControl1.TableDescriptor.Columns)
{
gd.AllowFilter = true;
}

Here is a sample for your reference.
GGC_ForeignKeyReference.zip

Best regards,
Haneef

[Alice Graham]

Thanks Haneef,

Using that approach does not seem as flexible as the solution provided in the link above.

Is there anything more similar to that approach for the GGC?

Alice

[haneefm]

Hi Alice,

Another way you can do this by adding new unbound filed to grid which maps to foreign display member and hide this column from the grid. And then you need to set the celltype of combobox column filterbarcell to "ComboBox" and also set DataSource,DisplayMember and, ValueMember property of the filterBar. You can handle the TableControlCurrentCellCloseDropDown event of the grid to manually filter the records in a GridTable.

Here is KB article that shows you "How to add a UnBoundField ina grid?".
http://www.syncfusion.com/support/kb/grid/Default.aspx?ToDo=view&questId=293

Best regards,
Haneef

[Alice Graham]

Hi,
Many thanks for your reply. We would ideally avoid using unbound columns if possible as this would require quite a lot of rework to our application.

We have investigated a new approach for this by sub-classing the GridGroupingControl and overriding the method below. The good news is this does display the display names in the filter dropdown. The bad news is the filter fails once a value is selected because the gird is trying to use the string passed from the filter to query on the column value (which is a System.Guid)!

Can you think of a way around this?

public override void OnQueryFilterBarChoices(GridQueryFilterBarChoicesEventArgs e)
{
// Force the grid to use the filter list we specify via the e.UniqueFilterBarValues
e.ShouldCreateSummaryDescriptor = false;

// Make sure the user has clicked on a filter (this event is also rasied when the grid is created)
if (e.Element == null) return;

// Construct array of unique cell values for this column
ArrayList vals = new ArrayList();
foreach (Record rec in this.GetTable("TargetCompanies").FilteredRecords)
{
object cellValue = rec.GetValue(e.Column.Name);
if (!vals.Contains(cellValue))
{
vals.Add(cellValue);
}
}

// Convert cell values to display values via ReferenceData and store in an array
object[] displayVals = new object[vals.Count];
for(int x = 0; x < vals.Count; x++)
{
object cellValue = vals[x];
if (cellValue == null || cellValue.ToString() == Guid.Empty.ToString() || cellValue.ToString().Length == 0)
{
cellValue = Convert.DBNull;
}
displayVals[x] = ReferenceData.GetStaticLending(cellValue);
}

// Return our display values to be displayed in filter dropdown
e.UniqueFilterBarValues = displayVals;
}

[haneefm]

Hi Alice,

There is a QueryRecordMeetsFilterCriteria event that you can use along with an display member filterbar column to get this behavior. Here is a code snippet that shows the behavior of the QueryRecordMeetsFilterCriteria event.

private void gridGroupingControl1_QueryRecordMeetsFilterCriteria(object sender, Syncfusion.Grouping.QueryRecordMeetsFilterCriteriaEventArgs e)
{
e.Handled = true; //cancel the built in filtering support.

object o = e.Record.GetValue("ComboBoxColumnName");

if(o != null && o.ToString() == "1")
e.Result = true; // shows the records in a grid.

else
e.Result = false; //filter the records from the grid.
}

Best regards,
Haneef

[Alice Graham]

Hi Haneef,

I'm a little confused how we use this method to achieve our filtering. Please consider the scenario where we have a column called "Fruit" which uses comboboxes to map the integer value of the column to the display name. The mappings are as follows:

Display Name Value
Apples 1
Oranges 2
Bananas 3

We want to add a filter to this column. So far I have successfully overridden the OnQueryFilterBarChoices event so that when the users clicks on the filter bar the following list is displayed:

All
Custom
Apples
Oranges
Bananas

However if they pick Apples the filter will not work correctly as the grid will be trying to apply the filter term "Apples" on values in the fruit column (which are integers). From your suggestion below, I can't see how we detect which item of fruit the user has currently selected from the filter list. It may also be possible that they have selected "All" or "Custom". The logic I think we need is:

1. Extract the selected item from the filter.
2. If it is an item of fruit then convert the display name to the value.
3. Compare the selected value with e.Record.GetValue"FruitColumn"). If it matches then return true.

I hope this is clear and we look forward to your reply,
Alice

[Alice Graham]

Display Name | Value
Apples | 1
Oranges | 2
Bananas | 3

[haneefm]

Hi Alice,

You can use the ListBoxPart.SelecteItem property of the GridFilterBarCellRenderer to get the selected value(control text) in the GridFilterBarCell in a grid. Below is a code snippet.

private void gridTableControlCurrentCellCloseDropDown(object sender, GridTableControlPopupClosedEventArgs e)
{
GridTableFilterBarCellRenderer cr = this.grid.TableControl.CellRenderers["FilterBarCell"] as GridTableFilterBarCellRenderer;
if( cr != null)
Console.WriteLine( " SelectedItem : " + cr.ListBoxPart.SelectedItem );
}

//// Or

Another way you can get this behavior by implementing a custom filterbar cell in a grid. Custom Filter is deriving the cellmodel / cellrenderer from GridTableFilterBarCellModel / GridTableFilterBarCellRenderer.

Please refer the attached sample for more details.
SampleFilter.zip

Best regards,
Haneef

[Tung Onci]

ok

[Sindhu Nagarajan]

Hi Tung, 

Thanks for the update. 

Please let us know if you need  any further assistance on this. 

Regards, 

Sindhu