GridFilterBar and foreign keys

[Stephane Dapsanse]

I have a main table that contains columns with foreign keys to other tables.
I display the main table in a GridDataBoundGrid and add a GridFilterBar to it.
I can also display meaningful values (Bonds as as opposed to 3, let's say) in the grid
(instead of the foreign keys) doing:

GridBoundColumnsCollection gbcc = this.thePositionsGrid.Binder.InternalColumns;
gbcc["ProductTypeID"].StyleInfo.CellType = "ComboBox";
gbcc["ProductTypeID"].StyleInfo.DataSource = thePositionsDataController.GetProductTypeTable();
gbcc["ProductTypeID"].StyleInfo.DisplayMember = "ProductTypeName";
gbcc["ProductTypeID"].StyleInfo.ValueMember = "ProductTypeID";
gbcc["ProductTypeID"].StyleInfo.ShowButtons = GridShowButtons.Show;
gbcc["ProductTypeID"].HeaderText = "Type";

At this stage, my GridFilterBar still displays the foreign keys as options for filtering.
The filter still works properly though.
I can display the meaningful values instead by doing:

int col = this.thePositionsGrid.Binder.NameToColIndex("ProductTypeID");
DataTable filterTable = (DataTable) this.thePositionsGrid[1,col].DataSource;
DataTable inputTable = thePositionsDataController.GetProductTypeTable(); // table with related primary keys
int nbrOfRows = filterTable.Rows.Count;
for (int i = 2; i < nbrOfRows;++i)
filterTable.Rows[i][0] = inputTable.Rows[i-2]["ProductTypeName"];

But then the filter is not working properly anymore.

Can you tell the most effective way to display a table with foreign keys in a GridDataBoundGrid,
replace the foreign keys by meaningful values to the user and be able to filter based on these meaningful values?

Thanks

[haneefm]

Hi Stephane,

You would have to derive the GridFilterbar and override the GetFilterFromRow method. Below is a minimal sample that shows this task.
FilterBarDisplayMember.zip

Thanks for choosing and using Syncfusion Products.

Best Regards,
Haneef

[Stephane Dapsanse]

Thank you Haneef. It works great.
One remark though:
I get a "Cannot perform '=' operation on System.Int32 and System.String" message when I keep filtering although there is nothing to filter anymore (empty display). It actually happens only with Col0, Col2 and Col3 (with no foreign key). Do you know what exception I should catch, to handle it?
I have another question about foreign keys but not related to GridFilterBar, so I guess I'll post a new message.


>Hi Stephane,

You would have to derive the GridFilterbar and override the GetFilterFromRow method. Below is a minimal sample that shows this task.
FilterBarDisplayMember.zip

Thanks for choosing and using Syncfusion Products.

Best Regards,
Haneef

[haneefm]

Hi Stephane,

Thank you for your update.

I am not sure of what be might be causing this strange behavior without a working sample. I have tested this issue in the attached sample with Essentail studio V.4.x/5.x. But i was not able to reproduce the issue. Is it possible for you to upload us a minimal sample or modify the browser sample to reproduce the issue here? This will help us to analyse the issue further.

Sample : ModifiedFilterBarDisplayMember.zip

Thanks for using Syncfusion product.

Best regards,
Haneef

[Stephane Dapsanse]

Hi,

If I use your 1st example (ForumFilterBarDisplayMember.zip),
and pick:
"row0 col0" then "value1" then "row0 col2",
I get the "Cannot perform '=' operation on System.Int32 and System.String" message.

If I use your 2nd example (ModifiedFilterBarDisplayMember.zip), and pick:
"row0 col0", then "0" then "row0 col2",
nothing unexpected happens.

The only difference I see is that the 2nd column contains a string in your 1st example and a number is your 2nd example. Based on this, do you know the cause of the message?

Thanks



>Hi Stephane,

Thank you for your update.

I am not sure of what be might be causing this strange behavior without a working sample. I have tested this issue in the attached sample with Essentail studio V.4.x/5.x. But i was not able to reproduce the issue. Is it possible for you to upload us a minimal sample or modify the browser sample to reproduce the issue here? This will help us to analyse the issue further.

Sample : ModifiedFilterBarDisplayMember.zip

Thanks for using Syncfusion product.

Best regards,
Haneef

[haneefm]

Hi Stephane,

Please try using the below GetFilterFromRow override method in your GridFilterbar class. Below are the codes :

private Hashtable DisplayFilter = new Hashtable();
private Hashtable ValueFilter = new Hashtable();
protected override string GetFilterFromRow(GridDataBoundGrid grid)
{
string filter = base.GetFilterFromRow(grid);
//swap display/value strings in the default filter

GridCurrentCell cc = grid.CurrentCell;
GridBoundColumnsCollection gbcc;
int row = this.GetFilterRow();

gbcc = (grid.GridBoundColumns.Count == 0) ? grid.Binder.InternalColumns : grid.GridBoundColumns;
//for(int col = 1; col <= grid.Model.ColCount; ++col)
int col = cc.ColIndex;
{

int field = grid.Binder.ColIndexToField(col);
GridStyleInfo style = gbcc[field].StyleInfo;

if(style.CellType == "ComboBox" && style.DataSource != null &&
style.DisplayMember != style.ValueMember)
{
string s = "";
string s1 = "";
if(cc.ColIndex == col )
{
s = "'" + cc.Renderer.ControlText + "'";
s1 = "'" + cc.Renderer.ControlValue.ToString() + "'";
}
else
{
s = "'" + grid[row, col].GetFormattedText(grid[row, col].CellValue);
s1 = "'" + grid[row, col].CellValue.ToString() + "'";
}
DisplayFilter[gbcc[field].MappingName] = s;
ValueFilter[gbcc[field].MappingName] = s1;
filter = filter.Replace(s, s1);
}
}
if(DisplayFilter.Contains("combo"))
{
filter = filter.Replace( DisplayFilter["combo"].ToString(),ValueFilter["combo"].ToString());
}
return filter;
}

Please try the suggestion and let me know if this helps.

Best regards,
Haneef