Quick Objects Documentation Akal Tech Logo
Joining, Sorting, Searching and Customizing the result set.

Glossary Item Box

Business Logic Framework - Tutorial 8:

Our eighth tutorial shows you how to join multiple tables, apply sorting and search criteria in addition to customizing the resulting column names. One of the very powerful features of the Quick Objects Business Logic Framework Each object can join with other objects it is related to, and in the joined objects can then further create joins with other objects they are related to. This allows the developer great flexibility and can get to any data in any table via any relationship.

Since each business object provides the same set of properties and methods and follows the same naming conventions (of your choice), it becomes very easy to use this framework. Once you get used to any one of the business objects the rest of the business objects are very familiar and easy to us.

Here is the code sample that shows how to join multiple tables, apply sorting, customization and search criteria to any of the fields from any of the selected objects.

1 using (OrderItems orderItems = new OrderItems())  
2 {  
3     orderItems.ObjectMode = Akal.QuickObjects.ObjectBase.ObjectModes.Search;  
4  
5     // customize what fields are shown  
6     orderItems.UseAllFieldsForDisplay(false);  
7     orderItems.Quantity.Visible = true;  
8     orderItems.ItemAmount.Visible = true;  
9     // the following line changes the resulting columns display name to "Sub Total" instead of the default ItemAmount  
10     orderItems.ItemAmount.ResultSetName = "Sub Total";  
11  
12     // create join between the OrderItems and Products (a parent object)  
13     orderItems.Join_ProductID_Products_Parent();  
14     // customize what fields are shown from the Products object i.e. Only the ProductName is shown.  
15     orderItems.ProductID_Products_Parent.UseAllFieldsForDisplay(false);  
16     orderItems.ProductID_Products_Parent.ProductName.Visible = true;  
17  
18     // the following line changes the resulting columns display name to "Product" instead of the default ProductName  
19     orderItems.ProductID_Products_Parent.ProductName.ResultSetName = "Product";  
20  
21     // create join between OrderItems and Orders  
22     orderItems.Join_OrderID_Orders_Parent();  
23  
24     // customize what fields are shown from the Orders object.  
25     orderItems.OrderID_Orders_Parent.UseAllFieldsForDisplay(false);  
26     orderItems.OrderID_Orders_Parent.OrderDate.Visible = true;  
27  
28     // OrderDate from the Orders object will be used for searching  
29     orderItems.OrderID_Orders_Parent.OrderDate.Value = Akal.QuickObjects.TimeZones.DateCalculations.GetStartOfLastYear(DateTime.Now);  
30     orderItems.OrderID_Orders_Parent.OrderDate.Operator = " >= ";  
31  
32     // the following line changes the resulting columns display name to "Date Ordered" instead of the default OrderDate  
33     orderItems.OrderID_Orders_Parent.OrderDate.ResultSetName = "Date Ordered";  
34       
35     // OrderDate will also be used for sorting the result set and all the returned records will be sorted  
36     // in the descending order of the OrderDate values.  
37     orderItems.OrderID_Orders_Parent.OrderDate.Sort = Akal.QuickObjects.ObjectBase.SortTypes.Descending;  
38     orderItems.OrderID_Orders_Parent.OrderDate.SortPosition = 1;  
39  
40     // Create join between the Orders and Customers objects.  
41     orderItems.OrderID_Orders_Parent.Join_CustomerID_Customers_Parent();  
42     orderItems.OrderID_Orders_Parent.CustomerID_Customers_Parent.UseAllFieldsForDisplay(false);  
43     orderItems.OrderID_Orders_Parent.CustomerID_Customers_Parent.FirstName.Visible = true;  
44     orderItems.OrderID_Orders_Parent.CustomerID_Customers_Parent.LastName.Visible = true;  
45  
46     // the following line changes the resulting columns display name to "First Name" instead of the default FirstName  
47     orderItems.OrderID_Orders_Parent.CustomerID_Customers_Parent.FirstName.ResultSetName = "First Name";  
48  
49     // the following line changes the resulting columns display name to "Last Name" instead of the default LastName  
50     orderItems.OrderID_Orders_Parent.CustomerID_Customers_Parent.LastName.ResultSetName = "Last Name";  
51  
52     // LastName will be used in sorting the result set after the OrderDate.  
53     // Resulting records will be sorted in the ascending order of LastName after OrderDate in the descending order.  
54     orderItems.OrderID_Orders_Parent.CustomerID_Customers_Parent.LastName.Sort = Akal.QuickObjects.ObjectBase.SortTypes.Ascending;  
55     orderItems.OrderID_Orders_Parent.CustomerID_Customers_Parent.LastName.SortPosition = 2;  
56  
57     orderItems.Find();  
58     if (orderItems.AffectedRecords > 0)  
59     {  
60         this.GridView.DataSource = orderItems.ResultSet;  
61         this.GridView.DataMember = orderItems.GetResultSetName();  
62     }  
63 }  
64  
1 Using orderItems As OrderItems = New OrderItems()  
2     orderItems.ObjectMode = Akal.QuickObjects.ObjectBase.ObjectModes.Search  
3  
4     ' customize what fields are shown  
5     orderItems.UseAllFieldsForDisplay(False)  
6     orderItems.Quantity.Visible = True 
7     orderItems.ItemAmount.Visible = True 
8     ' the following line changes the resulting columns display name to "Sub Total" instead of the default ItemAmount  
9     orderItems.ItemAmount.ResultSetName = "Sub Total" 
10  
11     ' create join between the OrderItems and Products (a parent object)  
12     orderItems.Join_ProductID_Products_Parent()  
13     ' customize what fields are shown from the Products object i.e. Only the ProductName is shown.  
14     orderItems.ProductID_Products_Parent.UseAllFieldsForDisplay(False)  
15     orderItems.ProductID_Products_Parent.ProductName.Visible = True 
16  
17     ' the following line changes the resulting columns display name to "Product" instead of the default ProductName  
18     orderItems.ProductID_Products_Parent.ProductName.ResultSetName = "Product" 
19  
20     ' create join between OrderItems and Orders  
21     orderItems.Join_OrderID_Orders_Parent()  
22  
23     ' customize what fields are shown from the Orders object.  
24     orderItems.OrderID_Orders_Parent.UseAllFieldsForDisplay(False)  
25     orderItems.OrderID_Orders_Parent.OrderDate.Visible = True 
26  
27     ' OrderDate from the Orders object will be used for searching  
28     orderItems.OrderID_Orders_Parent.OrderDate.Value = Akal.QuickObjects.TimeZones.DateCalculations.GetStartOfLastYear(DateTime.Now)  
29     orderItems.OrderID_Orders_Parent.OrderDate.Operator = " >= " 
30  
31     ' the following line changes the resulting columns display name to "Date Ordered" instead of the default OrderDate  
32     orderItems.OrderID_Orders_Parent.OrderDate.ResultSetName = "Date Ordered" 
33  
34     ' OrderDate will also be used for sorting the result set and all the returned records will be sorted  
35     ' in the descending order of the OrderDate values.  
36     orderItems.OrderID_Orders_Parent.OrderDate.Sort = Akal.QuickObjects.ObjectBase.SortTypes.Descending  
37     orderItems.OrderID_Orders_Parent.OrderDate.SortPosition = 1  
38  
39     ' Create join between the Orders and Customers objects.  
40     orderItems.OrderID_Orders_Parent.Join_CustomerID_Customers_Parent()  
41     orderItems.OrderID_Orders_Parent.CustomerID_Customers_Parent.UseAllFieldsForDisplay(False)  
42     orderItems.OrderID_Orders_Parent.CustomerID_Customers_Parent.FirstName.Visible = True 
43     orderItems.OrderID_Orders_Parent.CustomerID_Customers_Parent.LastName.Visible = True 
44  
45     ' the following line changes the resulting columns display name to "First Name" instead of the default FirstName  
46     orderItems.OrderID_Orders_Parent.CustomerID_Customers_Parent.FirstName.ResultSetName = "First Name" 
47  
48     ' the following line changes the resulting columns display name to "Last Name" instead of the default LastName  
49     orderItems.OrderID_Orders_Parent.CustomerID_Customers_Parent.LastName.ResultSetName = "Last Name" 
50  
51     ' LastName will be used in sorting the result set after the OrderDate.  
52     ' Resulting records will be sorted in the ascending order of LastName after OrderDate in the descending order.  
53     orderItems.OrderID_Orders_Parent.CustomerID_Customers_Parent.LastName.Sort = Akal.QuickObjects.ObjectBase.SortTypes.Ascending  
54     orderItems.OrderID_Orders_Parent.CustomerID_Customers_Parent.LastName.SortPosition = 2  
55  
56     orderItems.Find()  
57     If orderItems.AffectedRecords > 0 Then 
58         Me.GridView.DataSource = orderItems.ResultSet  
59         Me.GridView.DataMember = orderItems.GetResultSetName()  
60     End If 
61 End Using  
62