How to use XMLPorts in Web Services (3)

9 Jun

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.

image

Filtering

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.

Sorting

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.

image

Parameters:

image

Local variables:

image

Source (if someone wants to copy it):

[codesyntax lang=”progress”]

//Apply sorting
IF SortFields <> '' THEN
  CASE SortFields OF
    'No':
      Item.SETCURRENTKEY("No.");
    'Search Description':
      Item.SETCURRENTKEY("Search Description");
  END;
Item.ASCENDING(Ascending);
//Apply filter
IF ItemNoFilter <> '' THEN
  Item.SETFILTER("No.", ItemNoFilter);
//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
REPEAT
  Item.MARK(TRUE);
  IF MaxRecords > 0 THEN
    RecordCounter += 1;
UNTIL (Item.NEXT = 0) OR (RecordCounter = MaxRecords);
Item.MARKEDONLY(TRUE);
Items.SETTABLEVIEW(Item);

[/codesyntax]

C# code in Visual Studio

The first example shows the first page of 25 Item records, sorted by Search Description.

[codesyntax lang=”csharp”]

using System; 
using System.Collections.Generic; 
using System.Linq; 
using System.Web; 
using System.Web.UI; 
using System.Web.UI.WebControls; 
using XMLPortWSDemo.ExportDataWebService;
namespace XMLPortWSDemo 
{ 
  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; 
      GridView1.DataBind();
      this.StartRecordLabel.Text = (StartRecord + 1).ToString(); 
      this.EndRecordLabel.Text = (StartRecord + navItems.NAVItem.Length).ToString(); 
      this.totalRecordsLabel.Text = TotalRecords.ToString(); 
    } 
  } 
}

[/codesyntax]

This code generates the next output:

image

 

To filter on Item No. 80200 until 80299 the line with ItemNoFilter must be changed to:

[codesyntax lang=”csharp”]

.
. 
string ItemNoFilter = "80200..80299"; 
. 
. 
.

[/codesyntax]

Now we get the next list of item records:

image

 

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!

13 thoughts on “How to use XMLPorts in Web Services (3)

  1. Thank’s for the inspiration and the ideas. This really helped out and made my day easier 🙂

  2. Pingback: How to use XMLPorts in Web Services (3) | Pardaan.com

  3. Thanks, this is the post I’ve been hoping for.
    I followed every step of yours and had no problems. Your explanations were really hopeful too.

  4. If I use the XMLport property Format/Evaluate = XML Format/Evaluate, I get the correct datatypes in my webservice for the fields set in the XMLPort:

    example:

    Items (table)
    No. (fields) In webservice published as String
    Description (fields) In webservice published as String
    Amount (fields) In webservice published as Decimal

    When I want to add an own variable to the XMLPort it always returns as a string!!:

    Items (table)
    No. (fields) In webservice published as String
    Description (fields) In webservice published as String
    Custom Amount (text) In webservice published as String

    Is there a sollution for this problem? Thanks!

  5. I noticed some strange behaviour when exporting a decimal value with an xml port. When running with webservices the decimal value has only 2 digits, and when running from the classic client, it uses all the digits. To get the right number of digits with the web service you have to fill in the decimal places property of the table field.

  6. Hi,

    When using your code as example i get an error when using the FINDSET function while ordering ASCENDING(FALSE). Is this a version mismatch?

    I’ve tried changing FINDSET to FIND, but then the function gives me no result on using the Ascending function. While running the service on ASCENDING(True), it does work.

    Therefore I tried to change the order of using de code lines. But i can’t seem to get a good result. Any known issues with this?

    Thanx, NoiK

    • Hi Kees,

      You are definitely right, the FINDSET function can only retrieve records in ascending order. I should have noticed that…

      I think that replacing FINDSET with FIND(‘-‘) will solve this problem. Not only FIND, which will try to find records based on the current key values. That will result in no records found.

      Please let me know if this solves the problem.

      Kind regards,
      Arend-Jan Kauffmann

      • Hi Arend-Jan Kauffmann,

        I changed the code from FINDSET to FIND(‘-‘), now the sort order just works(without work arounds). Better for my code.

        Thnx,
        NoiK

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.