Introduction
Iron Speed Designer makes it easy to filter data displayed on show and edit table pages by foreign key columns, but it does not allow filtering when the columns do not contain any data.
This article will demonstrate how to easily overcome this by adding a “No Sales Rep” checkbox to the filter area on the show orders table to allow order records without a sales representative to be easily found.
Implementation
This article has been built on the default Iron Speed Southwind database.
First, a checkbox and associated label need to be added to the filter area on the show orders table. The easiest way to accomplish this is to open the Toolbox and navigate to the ASPX controls in the ASPX & Other Controls section then drag and drop a CheckBox Label onto the page, as shown below.
Figure 1 – Adding the CheckBox and Label controls to the show orders table page.
Next, rename the CheckBox to IsNoSalesRepCheckBox and set the AutoPostBack property to True, as shown below.
Figure 2 – The filter area on the show orders table page.
To implement the filtering of orders without a Sales Rep, navigate to the OrdersTableControl class in the ShowOrdersTable.Controls.cs code file and enter the following code.
C#:
/// <summary>
/// DNA - DJP - Update the "Sales Rep" DDL based upon the
/// "No Sales Rep" checkbox.
/// </summary>
/// <param name="sender"></param>
/// <param name="args"></param>
protected override void IsNoSalesRepCheckBox_CheckedChanged(
object sender, EventArgs args)
{
base.IsNoSalesRepCheckBox_CheckedChanged(sender, args);
this.SalesRepIDFilter.Enabled = !this.IsNoSalesRepCheckBox.Checked;
SalesRepIDFilter.SelectedIndex = -1;
this.DataChanged = true;
}
/// <summary>
/// DNA - DJP - Update the "No Sales Rep" checkbox based upon the
/// selected "Sales Rep".
/// </summary>
/// <param name="selectedValue"></param>
/// <param name="maxItems"></param>
protected override void PopulateSalesRepIDFilter(
string selectedValue, int maxItems)
{
base.PopulateSalesRepIDFilter(selectedValue, maxItems);
if (selectedValue == null)
{
this.IsNoSalesRepCheckBox.Enabled = true;
}
else
{
this.IsNoSalesRepCheckBox.Enabled = false;
}
}
/// <summary>
/// DNA - DJP - Only return orders with no sales rep when
/// "No Sales Rep" is checked.
/// </summary>
/// <returns></returns>
public override WhereClause CreateWhereClause()
{
WhereClause wc = base.CreateWhereClause();
if (this.IsNoSalesRepCheckBox != null &&
this.IsNoSalesRepCheckBox.Checked)
wc.iAND(OrdersTable.SalesRepID,
BaseFilter.ComparisonOperator.EqualsTo, "");
return wc;
}
Visual Basic .NET:
''' <summary>
''' DNA - DJP - Update the "Sales Rep" DDL based upon the
''' "No Sales Rep" checkbox.
''' </summary>
''' <param name="sender"></param>
''' <param name="args"></param>
Protected Overrides Sub IsNoSalesRepCheckBox_CheckedChanged(
sender As Object, args As EventArgs)
MyBase.IsNoSalesRepCheckBox_CheckedChanged(sender, args)
Me.SalesRepIDFilter.Enabled = Not Me.IsNoSalesRepCheckBox.Checked
SalesRepIDFilter.SelectedIndex = -1
Me.DataChanged = True
End Sub
''' <summary>
''' DNA - DJP - Update the "No Sales Rep" checkbox based upon the
''' selected "Sales Rep".
''' </summary>
''' <param name="selectedValue"></param>
''' <param name="maxItems"></param>
Protected Overrides Sub PopulateSalesRepIDFilter(
selectedValue As String, maxItems As Integer)
MyBase.PopulateSalesRepIDFilter(selectedValue, maxItems)
If selectedValue Is Nothing Then
Me.IsNoSalesRepCheckBox.Enabled = True
Else
Me.IsNoSalesRepCheckBox.Enabled = False
End If
End Sub
''' <summary>
''' DNA - DJP - Only return orders with no sales rep when
''' "No Sales Rep" is checked.
''' </summary>
''' <returns></returns>
Public Overrides Function CreateWhereClause() As WhereClause
Dim wc As WhereClause = MyBase.CreateWhereClause()
If Me.IsNoSalesRepCheckBox IsNot Nothing AndAlso _
Me.IsNoSalesRepCheckBox.Checked Then
wc.iAND(OrdersTable.SalesRepID, _
BaseFilter.ComparisonOperator.EqualsTo, "")
End If
Return wc
End Function
When the “No Sales Rep” checkbox is checked, the “Sales Rep” drop-down list will be disabled and the table will refresh to only show the orders which do not currently have a sales representative. If a sales representative is selected, the “No Sales Rep” checkbox will be disabled.
Figure 3 – The show orders table page with no filtering applied.
Figure 4 – The show orders table page, filtered by orders without a sales representative.
Figure 5 – The show orders table page, filtered by orders with King as the sales representative.
Conclusion
This article demonstrates how to easily overcome not being able to filter data displayed on show and edit table pages by foreign key columns when the columns do not contain any data. This is achieved by adding a “No Sales Rep” checkbox to the filter area on the show orders table to allow order records without a sales representative to be easily found.
The method used in this article can be extended to allow filtering of data for other field types, such as text, numbers and dates.