It took a while before I was able to finish this series. Time is flying…
This is the third (and last) post in a series about using XMLPorts in Web Services. The first post showed XMLPorts can be used in Web Services without any line of C/AL code. The second post explained why C/AL code is not needed to export data with the Web Service and how the data is converted into a strong type by Visual Studio.
This post shows how filtering and paging can be implemented. Of course it is possible to let ASP.Net do the filtering and paging. But in that case you have to download the complete list of records. In case of a large recordset this could take too much time. If you filter the records before the Web Service exports it, then your application will become more responsive.
From Web Services that are based on Pages we know that the default methods like Read and ReadMultiple have parameters for filtering and paging. Sorting can only by set to ascending or descending. Ascending sorting is used when parameter setSize has a positive number, while a negative number will return descending sorting.
Since XMLPorts are published using Codeunits, we are able to define our own parameters. In this example we use the XMLPort ‘Export Items’ that was defined in the first post in this series. To keep it simple we just use one parameter to filter on Item No. And of course it must be possible to use C/AL filtering syntax.
Start record and size of result set
To specify the first record to start with, we use a parameter ‘StartRecord’. It is an integer that gives the record number where the export should start. E.g. a value of 10 means that the export should start at the 10th record.
The number of records to return can be specified with a parameter ‘MaxRecords’. This is an integer that specifies the maximum number of records to return.
Total number of records
In many cases it is very handy to know the total number of records that are available. For example to display in a grid on a website. This can easily be done by using a parameter ‘TotalRecords’. Of course this parameter must use the VAR property to return the value.
To sort the records we must use the keys that are defined in the table in NAV. The ‘SortFields’ parameter is a text and specifies the key that must be used. Since the SETCURRENTKEY function requires a real field, it is not possible to directly use the parameter with SETCURRENTKEY. Therefore we have to hardcode the different sorting keys. This is not the way I like it, but that’s life in NAV, isn’t it? If anyone knows a better method, you’re welcome!
Code in NAV
Let’s have a look at the Codeunit in NAV that implements these parameters.
Source (if someone wants to copy it):
IF SortFields <> '' THEN
CASE SortFields OF
IF ItemNoFilter <> '' THEN
//GET result SET
IF Item.FINDSET THEN;
TotalRecords := Item.COUNT;
//FIND start record (zero based)
IF StartRecord > 0 THEN
IF Item.NEXT(StartRecord) = 0 THEN;
//Max number OF records
IF MaxRecords > 0 THEN
RecordCounter += 1;
UNTIL (Item.NEXT = 0) OR (RecordCounter = MaxRecords);
C# code in Visual Studio
The first example shows the first page of 25 Item records, sorted by Search Description.
public partial class _Default : System.Web.UI.Page
protected void Page_Load(object sender, EventArgs e)
ExportData service = new ExportData();
service.UseDefaultCredentials = true;
service.Url = "http://localhost:7047/DynamicsNAV/WS/CRONUS%20International%20Ltd/Codeunit/ExportData";
NAVItems navItems = new NAVItems();
string SortFields = "Search Description";
bool Ascending = true;
string ItemNoFilter = string.Empty;
int StartRecord = 0;
int MaxRecords = 25;
int TotalRecords = 0;
service.ExportItems(ref navItems, SortFields, Ascending, ItemNoFilter, StartRecord, MaxRecords, ref TotalRecords);
GridView1.DataSource = navItems.NAVItem;
this.StartRecordLabel.Text = (StartRecord + 1).ToString();
this.EndRecordLabel.Text = (StartRecord + navItems.NAVItem.Length).ToString();
this.totalRecordsLabel.Text = TotalRecords.ToString();
This code generates the next output:
To filter on Item No. 80200 until 80299 the line with ItemNoFilter must be changed to:
string ItemNoFilter = "80200..80299";
Now we get the next list of item records:
To add pager buttons with next and previous page, is just a matter of some extra coding (and out of scope of this post).
And optimization in the C/AL code could be to use a RecordRef and FieldRefs. That would give more flexibility in specifying filters.
I hope you liked the series!