Creating Complex Searches By Using Search Groups

Business Logic Framework - Tutorial 15:

This tutorial shows you how to use the Business Objects's capability to build and run complex search criteria. This tutorial shows a class called SearchGroup which extends BaseField and can be used to build search groups " (FirstName LIKE 'j%' AND LastName LIKE '%h') OR (FirstName LIKE '%i%' AND LastName LIKE '%e%') ". The process is simple, and you simply need to create a new instance of the SearchGroup. Once a SearchGroup instances is added to the Fields collection of the Business Object the rest of the fields are ignored, and only the SearchGroups are used. The related objects's Fields collections are ignored as well. To include a related object's field in the search criteria the field can be simply added to the SearchGroup's Fields collection.

C#
VB.NET

1
// Purpose of this sample/tutorial is to demonstrate the ability to run searches with search criteria where fields are grouped together to handle complex scenarios.  

2
// The target here is to search for Customers whose first name starts with 'j' and last name ends with 'h' or their first name contains 'i' and last name contains 'e'  

3
// SELECT CustomerID, FirstName, LastName FROM Customers  

4
// WHERE (FirstName LIKE 'j%' AND LastName LIKE '%h') OR (FirstName LIKE '%i%' AND LastName LIKE '%e%')  

5
using (Customers customers = new Customers())  

6
{  

7
    customers.ObjectMode = Akal.QuickObjects.ObjectBase.ObjectModes.Search;  

8
 

9
    // If PartialTextMatch property is set to true the business object uses all string field's values for partial text matches. However, this can be overridden  

10
    // at each field individually and also the PartialTextMode property can be used to specify weather to look for values that start/end with or contain the specified value.  

11
    customers.PartialTextMatch = true;  

12
 

13
    customers.FirstName.Value = "j";  

14
    // Setting the PartialTextMode to "StartsWith" will cause the customers object to search for FirstName value that starts with "j"  

15
    customers.FirstName.PartialTextMode = PartialTextModes.StartsWith;  

16
 

17
 

18
    customers.LastName.Value = "h";  

19
    // Setting the PartialTextMode to "EndsWith" will cause the customers object to search for LastName value that ends with "h"  

20
    customers.LastName.PartialTextMode = PartialTextModes.EndsWith;  

21
 

22
    // SearchGroup is a Powerful class that can be used to create complex search criteria where multiple fields can be grouped together and each group seperated by AND/OR (SearchCondition property).  

23
    // Once one or more SearchGroup instances are constructed as shown in the following line, the search criteria will exclude all fields except the ones that are added to the "Fields" collection of the SearchGroup  

24
    // Each SearchGroup must be given a unique name.  

25
    SearchGroup sg1 = new SearchGroup(customers, "sg1");  

26
 

27
    // Set the SearchCondition to " OR ". Since this is the first SearchGroup being added the SearchCondition will actually be ignored but it is shown here just to demonstrate the capability.  

28
    sg1.SearchCondition = " OR ";  

29
 

30
    // Add the FirstName and LastName fields to the SearchGroup. The field thats added first its SearchCondition is ignored since the generated where clause will look like this  

31
    // WHERE ( FirstName LIKE :FirstNameParam AND LastName LIKE LastNameParam )  

32
    sg1.Fields.Add(customers.FirstName);  

33
    sg1.Fields.Add(customers.LastName);  

34
 

35
    // Lets create another SearchGroup and this time it displays an alternate means of specified a "GroupName".  

36
    // GroupName must be unique for each group and can be any string of your choice.  

37
    SearchGroup sg2 = new SearchGroup(customers);  

38
    // You can set the GroupName property or you can alternatively set the sg2.Name.Value = "sg2" and they both have the same affect.  

39
    sg2.GroupName = "sg2";  

40
 

41
    // Set the SearchCondition to " OR ".   

42
    sg2.SearchCondition = " OR ";  

43
 

44
    // Since we have already used the FirstName field lets create a new StringField that will hold the search criteria for "FirstName" to be included in the second search group.  

45
    // customers object instance already has a field for "FirstName" so when add a second field for "FirstName" we must give it an alias (i.e. set the ResultSetName property).  

46
    // The StringField constructor used below takes three arguments 1. BaseBusinessObject instance 2. A String that is the field's name 3. A String that will be used as the field's alias (ResultSetName)   

47
    StringField firstName = new StringField(customers, "FirstName", "FN_ALIAS");  

48
 

49
    // By setting this instance's Visible property to false we make sure that a duplicate "FirstName" column will not appear in the result set.  

50
    firstName.Visible = false;  

51
    // By setting the PartialTextMatch to "true" this field's search criteria will automatically   

52
    firstName.PartialTextMatch = true;  

53
    // By Setting the PartialTextMode to "PartialTextModes.Contains" the search criteria that will look for FirstName that contains the Value (i.e. 'i')  

54
    firstName.PartialTextMode = PartialTextModes.Contains;  

55
    firstName.Value = "i";  

56
 

57
    // Since we have already used the LastName field lets create a new StringField that will hold the search criteria for "LastName" to be included in the second search group.  

58
    // customers object instance already has a field for "LastName" so when add a second field for "LastName" we must give it an alias (i.e. set the ResultSetName property).  

59
    // The following lines show an alternate way to achieve the same that we did in one line for firstName field instance.  

60
    StringField lastName = new StringField(customers);  

61
    // Name property must be given a field name that belong to the underlying table.  

62
    lastName.Name.Value = "LastName";  

63
    // An alias must be created since LastName field already exists in the customers object  

64
    lastName.ResultSetName.Value = "LN_ALIAS";  

65
 

66
    // the following lines follow the same reasoning as the firstName field instance.  

67
    lastName.Visible = false;  

68
    lastName.PartialTextMatch = true;  

69
    lastName.PartialTextMode = PartialTextModes.Contains;  

70
    lastName.Value = "e";  

71
 

72
    // Lets add the firstName and lastName instances into the sg2 SearchGroup  

73
    sg2.Fields.Add(firstName);  

74
    sg2.Fields.Add(lastName);  

75
 

76
    // Calling the Find method will create and run the desired sql statement.  

77
    customers.Find();  

78
    // AffectedRecords property can be used to determine how many records were returned by the business object.  

79
    if (customers.AffectedRecords > 0)  

80
    {  

81
        this.GridView1.DataSource = customers.ResultSet;  

82
        this.GridView1.DataMember = customers.GetResultSetName();  

83
    }  

84
 

85
}  

86
 

1
'Purpose of this sample/tutorial is to demonstrate the ability to run searches with search criteria where fields are grouped together to handle complex scenarios.  

2
'The target here is to search for Customers whose first name starts with 'j' and last name ends with 'h' or their first name contains 'i' and last name contains 'e'  

3
'SELECT CustomerID, FirstName, LastName FROM Customers  

4
'WHERE (FirstName LIKE 'j%' AND LastName LIKE '%h') OR (FirstName LIKE '%i%' AND LastName LIKE '%e%')  

5
Using customers As Customers = New Customers()  

6
    customers.ObjectMode = Akal.QuickObjects.ObjectBase.ObjectModes.Search  

7
    'If PartialTextMatch property is set to true the business object uses all string field's values for partial text matches. However, this can be overridden  

8
    'at each field individually and also the PartialTextMode property can be used to specify weather to look for values that start/end with or contain the specified value.  

9
    customers.PartialTextMatch = True 

10
 

11
    customers.FirstName.Value = "j" 

12
    'Setting the PartialTextMode to "StartsWith" will cause the customers object to search for FirstName value that starts with "j"  

13
    customers.FirstName.PartialTextMode = PartialTextModes.StartsWith  

14
 

15
    customers.LastName.Value = "h" 

16
    'Setting the PartialTextMode to "EndsWith" will cause the customers object to search for LastName value that ends with "h"  

17
    customers.LastName.PartialTextMode = PartialTextModes.EndsWith  

18
 

19
    'SearchGroup is a Powerful class that can be used to create complex search criteria where multiple fields can be grouped together and each group seperated by AND/OR (SearchCondition property).  

20
    'Once one or more SearchGroup instances are constructed as shown in the following line, the search criteria will exclude all fields except the ones that are added to the "Fields" collection of the SearchGroup  

21
    'Each SearchGroup must be given a unique name.  

22
    Dim sg1 = New SearchGroup(customers, "sg1")  

23
 

24
    'Set the SearchCondition to " OR ". Since this is the first SearchGroup being added the SearchCondition will actually be ignored but it is shown here just to demonstrate the capability.  

25
    sg1.SearchCondition = " OR " 

26
 

27
    'Add the FirstName and LastName fields to the SearchGroup. The field thats added first its SearchCondition is ignored since the generated where clause will look like this  

28
    'WHERE ( FirstName LIKE :FirstNameParam AND LastName LIKE LastNameParam )  

29
    sg1.Fields.Add(customers.FirstName)  

30
    sg1.Fields.Add(customers.LastName)  

31
 

32
    'Lets create another SearchGroup and this time it displays an alternate means of specified a "GroupName".  

33
    'GroupName must be unique for each group and can be any string of your choice.  

34
    Dim sg2 = New SearchGroup(customers)  

35
    'You can set the GroupName property or you can alternatively set the sg2.Name.Value = "sg2" and they both have the same affect.  

36
    sg2.GroupName = "sg2" 

37
 

38
    'Set the SearchCondition to " OR ".   

39
    sg2.SearchCondition = " OR " 

40
 

41
    'Since we have already used the FirstName field lets create a new StringField that will hold the search criteria for "FirstName" to be included in the second search group.  

42
    'customers object instance already has a field for "FirstName" so when add a second field for "FirstName" we must give it an alias (i.e. set the ResultSetName property).  

43
    'The StringField constructor used below takes three arguments 1. BaseBusinessObject instance 2. A String that is the field's name 3. A String that will be used as the field's alias (ResultSetName)   

44
    Dim firstName As StringField = New StringField(customers, "FirstName", "FN_ALIAS")  

45
 

46
    'By setting this instance's Visible property to false we make sure that a duplicate "FirstName" column will not appear in the result set.  

47
    firstName.Visible = False 

48
    'By setting the PartialTextMatch to "true" this field's search criteria will automatically   

49
    firstName.PartialTextMatch = True 

50
    'By Setting the PartialTextMode to "PartialTextModes.Contains" the search criteria that will look for FirstName that contains the Value (i.e. 'i')  

51
    firstName.PartialTextMode = PartialTextModes.Contains  

52
    firstName.Value = "i" 

53
 

54
    'Since we have already used the LastName field lets create a new StringField that will hold the search criteria for "LastName" to be included in the second search group.  

55
    'customers object instance already has a field for "LastName" so when add a second field for "LastName" we must give it an alias (i.e. set the ResultSetName property).  

56
    'The following lines show an alternate way to acheive the same that we did in one line for firstName field instance.  

57
    Dim lastName As StringField = New StringField(customers)  

58
    'Name property must be given a field name that belong to the underlying table.  

59
    lastName.Name.Value = "LastName" 

60
    'An alias must be created since LastName field already exists in the customers object  

61
    lastName.ResultSetName.Value = "LN_ALIAS" 

62
 

63
    'the following lines follow the same reasoning as the firstName field instance.  

64
    lastName.Visible = False 

65
    lastName.PartialTextMatch = True 

66
    lastName.PartialTextMode = PartialTextModes.Contains  

67
    lastName.Value = "e" 

68
 

69
    'Lets add the firstName and lastName instances into the sg2 SearchGroup  

70
    sg2.Fields.Add(firstName)  

71
    sg2.Fields.Add(lastName)  

72
 

73
    'Calling the Find method will create and run the desired sql statement.  

74
    customers.Find()  

75
    'AffectedRecords property can be used to determine how many records were returned by the business object.  

76
    If customers.AffectedRecords > 0 Then 

77
        Me.GridView1.DataSource = customers.ResultSet  

78
        Me.GridView1.DataMember = customers.GetResultSetName()  

79
    End If 

80
End Using  

81
 

Send comments on this topic.

Quick Objects Documentation
Creating Complex Searches By Using Search Groups

1	// Purpose of this sample/tutorial is to demonstrate the ability to run searches with search criteria where fields are grouped together to handle complex scenarios.
2	// The target here is to search for Customers whose first name starts with 'j' and last name ends with 'h' or their first name contains 'i' and last name contains 'e'
3	// SELECT CustomerID, FirstName, LastName FROM Customers
4	// WHERE (FirstName LIKE 'j%' AND LastName LIKE '%h') OR (FirstName LIKE '%i%' AND LastName LIKE '%e%')
5	using (Customers customers = new Customers())
6	{
7	customers.ObjectMode = Akal.QuickObjects.ObjectBase.ObjectModes.Search;
8
9	// If PartialTextMatch property is set to true the business object uses all string field's values for partial text matches. However, this can be overridden
10	// at each field individually and also the PartialTextMode property can be used to specify weather to look for values that start/end with or contain the specified value.
11	customers.PartialTextMatch = true;
12
13	customers.FirstName.Value = "j";
14	// Setting the PartialTextMode to "StartsWith" will cause the customers object to search for FirstName value that starts with "j"
15	customers.FirstName.PartialTextMode = PartialTextModes.StartsWith;
16
17
18	customers.LastName.Value = "h";
19	// Setting the PartialTextMode to "EndsWith" will cause the customers object to search for LastName value that ends with "h"
20	customers.LastName.PartialTextMode = PartialTextModes.EndsWith;
21
22	// SearchGroup is a Powerful class that can be used to create complex search criteria where multiple fields can be grouped together and each group seperated by AND/OR (SearchCondition property).
23	// Once one or more SearchGroup instances are constructed as shown in the following line, the search criteria will exclude all fields except the ones that are added to the "Fields" collection of the SearchGroup
24	// Each SearchGroup must be given a unique name.
25	SearchGroup sg1 = new SearchGroup(customers, "sg1");
26
27	// Set the SearchCondition to " OR ". Since this is the first SearchGroup being added the SearchCondition will actually be ignored but it is shown here just to demonstrate the capability.
28	sg1.SearchCondition = " OR ";
29
30	// Add the FirstName and LastName fields to the SearchGroup. The field thats added first its SearchCondition is ignored since the generated where clause will look like this
31	// WHERE ( FirstName LIKE :FirstNameParam AND LastName LIKE LastNameParam )
32	sg1.Fields.Add(customers.FirstName);
33	sg1.Fields.Add(customers.LastName);
34
35	// Lets create another SearchGroup and this time it displays an alternate means of specified a "GroupName".
36	// GroupName must be unique for each group and can be any string of your choice.
37	SearchGroup sg2 = new SearchGroup(customers);
38	// You can set the GroupName property or you can alternatively set the sg2.Name.Value = "sg2" and they both have the same affect.
39	sg2.GroupName = "sg2";
40
41	// Set the SearchCondition to " OR ".
42	sg2.SearchCondition = " OR ";
43
44	// Since we have already used the FirstName field lets create a new StringField that will hold the search criteria for "FirstName" to be included in the second search group.
45	// customers object instance already has a field for "FirstName" so when add a second field for "FirstName" we must give it an alias (i.e. set the ResultSetName property).
46	// The StringField constructor used below takes three arguments 1. BaseBusinessObject instance 2. A String that is the field's name 3. A String that will be used as the field's alias (ResultSetName)
47	StringField firstName = new StringField(customers, "FirstName", "FN_ALIAS");
48
49	// By setting this instance's Visible property to false we make sure that a duplicate "FirstName" column will not appear in the result set.
50	firstName.Visible = false;
51	// By setting the PartialTextMatch to "true" this field's search criteria will automatically
52	firstName.PartialTextMatch = true;
53	// By Setting the PartialTextMode to "PartialTextModes.Contains" the search criteria that will look for FirstName that contains the Value (i.e. 'i')
54	firstName.PartialTextMode = PartialTextModes.Contains;
55	firstName.Value = "i";
56
57	// Since we have already used the LastName field lets create a new StringField that will hold the search criteria for "LastName" to be included in the second search group.
58	// customers object instance already has a field for "LastName" so when add a second field for "LastName" we must give it an alias (i.e. set the ResultSetName property).
59	// The following lines show an alternate way to achieve the same that we did in one line for firstName field instance.
60	StringField lastName = new StringField(customers);
61	// Name property must be given a field name that belong to the underlying table.
62	lastName.Name.Value = "LastName";
63	// An alias must be created since LastName field already exists in the customers object
64	lastName.ResultSetName.Value = "LN_ALIAS";
65
66	// the following lines follow the same reasoning as the firstName field instance.
67	lastName.Visible = false;
68	lastName.PartialTextMatch = true;
69	lastName.PartialTextMode = PartialTextModes.Contains;
70	lastName.Value = "e";
71
72	// Lets add the firstName and lastName instances into the sg2 SearchGroup
73	sg2.Fields.Add(firstName);
74	sg2.Fields.Add(lastName);
75
76	// Calling the Find method will create and run the desired sql statement.
77	customers.Find();
78	// AffectedRecords property can be used to determine how many records were returned by the business object.
79	if (customers.AffectedRecords > 0)
80	{
81	this.GridView1.DataSource = customers.ResultSet;
82	this.GridView1.DataMember = customers.GetResultSetName();
83	}
84
85	}
86

1	'Purpose of this sample/tutorial is to demonstrate the ability to run searches with search criteria where fields are grouped together to handle complex scenarios.
2	'The target here is to search for Customers whose first name starts with 'j' and last name ends with 'h' or their first name contains 'i' and last name contains 'e'
3	'SELECT CustomerID, FirstName, LastName FROM Customers
4	'WHERE (FirstName LIKE 'j%' AND LastName LIKE '%h') OR (FirstName LIKE '%i%' AND LastName LIKE '%e%')
5	Using customers As Customers = New Customers()
6	customers.ObjectMode = Akal.QuickObjects.ObjectBase.ObjectModes.Search
7	'If PartialTextMatch property is set to true the business object uses all string field's values for partial text matches. However, this can be overridden
8	'at each field individually and also the PartialTextMode property can be used to specify weather to look for values that start/end with or contain the specified value.
9	customers.PartialTextMatch = True
10
11	customers.FirstName.Value = "j"
12	'Setting the PartialTextMode to "StartsWith" will cause the customers object to search for FirstName value that starts with "j"
13	customers.FirstName.PartialTextMode = PartialTextModes.StartsWith
14
15	customers.LastName.Value = "h"
16	'Setting the PartialTextMode to "EndsWith" will cause the customers object to search for LastName value that ends with "h"
17	customers.LastName.PartialTextMode = PartialTextModes.EndsWith
18
19	'SearchGroup is a Powerful class that can be used to create complex search criteria where multiple fields can be grouped together and each group seperated by AND/OR (SearchCondition property).
20	'Once one or more SearchGroup instances are constructed as shown in the following line, the search criteria will exclude all fields except the ones that are added to the "Fields" collection of the SearchGroup
21	'Each SearchGroup must be given a unique name.
22	Dim sg1 = New SearchGroup(customers, "sg1")
23
24	'Set the SearchCondition to " OR ". Since this is the first SearchGroup being added the SearchCondition will actually be ignored but it is shown here just to demonstrate the capability.
25	sg1.SearchCondition = " OR "
26
27	'Add the FirstName and LastName fields to the SearchGroup. The field thats added first its SearchCondition is ignored since the generated where clause will look like this
28	'WHERE ( FirstName LIKE :FirstNameParam AND LastName LIKE LastNameParam )
29	sg1.Fields.Add(customers.FirstName)
30	sg1.Fields.Add(customers.LastName)
31
32	'Lets create another SearchGroup and this time it displays an alternate means of specified a "GroupName".
33	'GroupName must be unique for each group and can be any string of your choice.
34	Dim sg2 = New SearchGroup(customers)
35	'You can set the GroupName property or you can alternatively set the sg2.Name.Value = "sg2" and they both have the same affect.
36	sg2.GroupName = "sg2"
37
38	'Set the SearchCondition to " OR ".
39	sg2.SearchCondition = " OR "
40
41	'Since we have already used the FirstName field lets create a new StringField that will hold the search criteria for "FirstName" to be included in the second search group.
42	'customers object instance already has a field for "FirstName" so when add a second field for "FirstName" we must give it an alias (i.e. set the ResultSetName property).
43	'The StringField constructor used below takes three arguments 1. BaseBusinessObject instance 2. A String that is the field's name 3. A String that will be used as the field's alias (ResultSetName)
44	Dim firstName As StringField = New StringField(customers, "FirstName", "FN_ALIAS")
45
46	'By setting this instance's Visible property to false we make sure that a duplicate "FirstName" column will not appear in the result set.
47	firstName.Visible = False
48	'By setting the PartialTextMatch to "true" this field's search criteria will automatically
49	firstName.PartialTextMatch = True
50	'By Setting the PartialTextMode to "PartialTextModes.Contains" the search criteria that will look for FirstName that contains the Value (i.e. 'i')
51	firstName.PartialTextMode = PartialTextModes.Contains
52	firstName.Value = "i"
53
54	'Since we have already used the LastName field lets create a new StringField that will hold the search criteria for "LastName" to be included in the second search group.
55	'customers object instance already has a field for "LastName" so when add a second field for "LastName" we must give it an alias (i.e. set the ResultSetName property).
56	'The following lines show an alternate way to acheive the same that we did in one line for firstName field instance.
57	Dim lastName As StringField = New StringField(customers)
58	'Name property must be given a field name that belong to the underlying table.
59	lastName.Name.Value = "LastName"
60	'An alias must be created since LastName field already exists in the customers object
61	lastName.ResultSetName.Value = "LN_ALIAS"
62
63	'the following lines follow the same reasoning as the firstName field instance.
64	lastName.Visible = False
65	lastName.PartialTextMatch = True
66	lastName.PartialTextMode = PartialTextModes.Contains
67	lastName.Value = "e"
68
69	'Lets add the firstName and lastName instances into the sg2 SearchGroup
70	sg2.Fields.Add(firstName)
71	sg2.Fields.Add(lastName)
72
73	'Calling the Find method will create and run the desired sql statement.
74	customers.Find()
75	'AffectedRecords property can be used to determine how many records were returned by the business object.
76	If customers.AffectedRecords > 0 Then
77	Me.GridView1.DataSource = customers.ResultSet
78	Me.GridView1.DataMember = customers.GetResultSetName()
79	End If
80	End Using
81