How to set dynamically the Key and Sort Order for the pages using NAV 2009 Web Services feature.
The default methods exposed when a page is published are the so-called CRUD (Create, Read, Multiple and Delete) methods. All of those does not allow to set up previously a key for sorting order nor if the records have to be ascending or descending.
In order to retrieve data in a particular sorting order it should be necessary to develop N pages for N related sorting keys. This can be a solution that deserve the analysis of the cost of the pages plus the maintenance of the source objects.
Another way to set the Key and Sort order for pages object is described here. It only has the cost of purchasing one table that collect the User ID, the page called (Page ID) , the Key used and the Sort Order, plus one page to publish the table and only one page per master table. This will avoid the cost of N pages for master table per N Sorting order keys.
The following example is based on Customer table and has the purpose of develop a WinForm that dynamically retrieves data from the server based on a selected sorting order like in the picture below (just 3 keys have been used here):
PREREQUISITES
1. Install NAV 2009 + Web Services components
2. This example has been developed using the following scenario:
- Windows Server 2008 x64
- SQL Server 2005 SP2 x64
- NAV 2009 IT RTM
A. CREATE THE OrderBy TABLE
1. Create a new table (e.g. 50000) and give it the name OrderBy with those fields:
- User ID Text 65 (used to store the USERID)
- Page No. integer (Number of the page published as WS)
- OrderByInt integer (store the number of the key used)
- SortOrder Boolean (TRUE means ASC, FALSE means DESC)
2. Save and compile the table (e.g. 50000 OrderBy)
B. CREATE THE OrderByWS PAGE
1. Create a new page (e.g. 50001) based on the OrderBy table previously created (e.g. Table 50000) and give it the name OrderBy. This page will be published as Web Service and will expose all its own CRUD methods.
- PageType property: Card
- SourceTable property: OrderBy
2. Select all the fields of the table using the classic Field Menu and put all of them into a group:
- "User ID"
- "Page No."
- OrderByInt
-SortOrder
3. Save and Compile (e.g. 50001 OrderBy)
C. CREATE THE CustomerWS PAGE
1. Create a new page (e.g. 50002) based on the Customer table (Table 21) and give it the name CustomerWS. This page will be published as Web Service and will expose all its own CRUD methods.
- PageType property: Card
- SourceTable property: Customer
2. Select the fields reported below by using the classic Field Menu and put all of them into a group
- "No."
- Name
- Address
- "Country/Region Code"
- City
3. Click on View > C/AL Globals > Functions tab
4. Create a new function called SetPageKey
5. Click on Locals button and create a new Local variable for the SetPageKey function:
OrderByRec Record OrderBy
5. Close the C/AL Globals form
6. Edit the C/AL Editor of the page by pressing F9 and start adding C/AL code to your page
7. In the OnInit trigger write this line:
SetPageKey; //Call the SetPageKey function
8. In the SetPageKey function write this sequence of C/AL code:
IF OrderByRec.GET(USERID,50002) THEN BEGIN
CASE OrderByRec.OrderByInt OF
1 : SETCURRENTKEY(Name);
2 : SETCURRENTKEY("Country/Region Code");
ELSE
SETCURRENTKEY("No.");
END;
ASCENDING(OrderByRec.SortOrder);
END;
9. Save and compile the page (e.g. 50002 CustomerWS)
D. PUBLISH YOUR PAGE AS WEB SERVICES
1. Run Table 2000000076 Web Service
2. Insert the following lines
Object Type Service Name Object ID Published
Page Cust WS 50002 Yes
Page OrderBy WS 50001 Yes
3. Once you have ticked the Published control, your page should be published as Web Service in your environment. To verify this you can easily check it out at:
https://<SeverName>:<WebServicePort>/<ServiceName>/ws/<CompanyName>/Services
that in this scenario has to be:
https://dtacconit7400:7047/DynamicsNAV/ws/CRONUS_Italia_S_p_A/Services
for more information on working with Web Services, please refers to MSDN at:
https://msdn.microsoft.com/en-us/library/dd355036.aspx
E. DESIGN YOUR CUSTOMER WINFORM
1. Open Visual Studio 2008
2. Create a new WinForm VC# project
3. Add Controls to the form:
a. Button control
(name) - btnLoadData
Text - Load Data
b. 3 RadioButton controls + GroupBox control (Text - Key)
(name) - rbNo
Text - Customer No.
(name) - rbName
Text - Name
(name) - rbCountry
Text - Country
c. 2 RadioButton controls + GroupBox control (Text - Order)
(name) -rbAsc
Text - Ascending
(name) - rbDesc
Text - Descending
d. DataGridView
4. Add Reference to your Web Services.
a. In the Solution Explorer, right click on References > Add Web Reference
b. Click on Add Web Reference
c. Paste the link to the URL related to the Services and click Go:
https://dtacconit7400:7047/DynamicsNAV/ws/CRONUS_Italia_S_p_A/Services
d. Click on View Service in the Cust_WS service row and change the Web Reference name to:
CustWRN (customer web reference name)
e. Click on View Service in the OrderBy_WS service row and change the Web reference name to:
OrderByWRN (OrderBy web reference name)
5. Link the DataGridView to the CustWS service
a. Click on the right arrow in the upper right position of the DataGridView
b. In the DataGridView Tasks click on Choose Binding Source
c. Explode the tree and select CustWRN, a brand new CustWSBindingSource will be created
d. Return to DataGridView Tasks and untick all the Enable check boxes
e. Click on Edit Columns and add all the fields present (tip: change some of the column properties in order to let the DataGridView display the data properly), click OK.
F. WRITE C# CODE TO LET THE WINFORM ANIMATE…
Add the C# code into your form to let it work as expected.
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Security.Principal; //Namespace useful to retrieve the login
namespace OrderBy
{
//Use 2 Web References based on Order By Page, Customer Page
using OrderByWRN;
using CustWRN;
public partial class Form1 : Form
{
//Declare 2 WS Variables
private Cust_WS_Service CustWSService;
private OrderBy_WS_Service OrderByService;
//Create the integer variables related to the RadioButtons
// for the Order By and the Sorting
private int OptChoice;
private bool SortChoice;
public Form1()
{
InitializeComponent();
//Default Key used = Customer No. (You can always change this code to retrieve
// it from the last access to the WinForm)
OptChoice = 0;
SortChoice = false;
rbNo.Checked = true;
rbAsc.Checked = true;
//Instantiate Customer Page WS and set the correct URL
CustWSService = new Cust_WS_Service();
CustWSService.UseDefaultCredentials = true;
CustWSService.Url = "https://dtacconit7400:7047/DynamicsNAV/ws/CRONUS_Italia_S_p_A/Page/Cust_WS";
//Instantiate OrderBy page WS and set the correct URL
OrderByService = new OrderBy_WS_Service();
OrderByService.UseDefaultCredentials = true;
OrderByService.Url = "https://dtacconit7400:7047/DynamicsNAV/ws/CRONUS_Italia_S_p_A/Page/OrderBy_WS";
}
private void rbNo_CheckedChanged(object sender, EventArgs e)
{
OptChoice = 0;
}
private void rbName_CheckedChanged(object sender, EventArgs e)
{
OptChoice = 1;
}
private void rbCountry_CheckedChanged(object sender, EventArgs e)
{
OptChoice = 2;
}
private void rbAsc_CheckedChanged(object sender, EventArgs e)
{
SortChoice = true;
}
private void rbDesc_CheckedChanged(object sender, EventArgs e)
{
SortChoice = false;
}
private void RetrieveRecords()
{
//Create a new Customer recordset
Cust_WS CustomerRset = new Cust_WS();
//Create a new set of filters (empty)
List<Cust_WS_Filter> CustFilters = new List<Cust_WS_Filter>();
//Feed the data grid with the recordset retrieved by the ReadMultiple
// CRUD method on Customer Page and retrieve the first 100 result records
dataGridView1.DataSource = CustWSService.ReadMultiple(CustFilters.ToArray(), null, 100);
}
private void btnLoadData_Click(object sender, EventArgs e)
{
//If the button is clicked perform the following actions:
//Retrieve the login to determine the USERID
AppDomain.CurrentDomain.SetPrincipalPolicy(PrincipalPolicy.WindowsPrincipal);
WindowsPrincipal user = (WindowsPrincipal)System.Threading.Thread.CurrentPrincipal;
//Create a new OrderBy recordset
OrderBy_WS OrderByRset = new OrderBy_WS();
//Fill in the new OrderBy recordset with the data read with this key
OrderByRset = OrderByService.Read(
user.Identity.Name.Substring(user.Identity.Name.LastIndexOf('\\') + 1), 50002);
//If there is no record in the OrderBy table then create new one
if (OrderByRset == null)
{
OrderBy_WS OrderByRsetCreate = new OrderBy_WS();
//Set the values for a brand new OrderBy record
OrderByRsetCreate.User_ID = user.Identity.Name.Substring(
user.Identity.Name.LastIndexOf('\\') + 1);
OrderByRsetCreate.Page_NoSpecified = true;
OrderByRsetCreate.Page_No = 50002;
OrderByRsetCreate.OrderByInt = OptChoice;
OrderByRsetCreate.SortOrder = SortChoice;
//Create the NAV OrderBy table record with the key and sort order selected
OrderByService.Create(ref OrderByRsetCreate);
//Flush the OrderBy record after doing the create
OrderByRsetCreate = null;
}
else
{
//Change the value of the fields OrderByInt and SortOrder in the new OrderBy recordset
OrderByRset.OrderByInt = OptChoice;
OrderByRset.SortOrder = SortChoice;
//Update the NAV OrderBy table record with the new key and sort order selected
OrderByService.Update(ref OrderByRset);
}
//Flush the OrderBy record
OrderByRset = null;
//Retrieve the record in the right sort order by using the SETCURRENTKEY statement
// that you find in the Customer Page into the SetPageKey function
RetrieveRecords();
}
}
}
7. Run the WinForm by pressing F5 and play with Key selection and Sort order dynamically with your brand new Customer WinForm!
These postings are provided "AS IS" with no warranties and confer no rights. You assume all risk for your use.
Best Regards,
Duilio Tacconi
Microsoft Dynamics Italy
Microsoft Customer Service and Support (CSS) EMEA