Tag Archives: dynamics NAV

NAV 2013 R2 RunPageLink bug

Bug

If you have standard Page Action that uses RunPageLink on field that is an Option field, and the OptionCaption of that field has entry that uses & sign (“and” sign) the RunPageLink will not filter the underlying table correctly in Filter Group0.

Explanation

The standard example would be Comment Line Table. If you define Page Action to run Comment Line Table, from some page, where RunPageLink uses filter on the field, that is of Option type and the OptionCaption (OptionCaptionML) of the field has & sign (“and” sign):

2014-09-01_1522

then the standard filter will not work:

2014-09-01_1507

After you change the OptionCaption (OptionCaptionML) of the field that is in RunPageLink filter to (note that OptionString still has & sign):2014-09-01_1510

the standard filter works as it is supposed to:

2014-09-01_1513Of course, when you run Page Action any other value of Option field, that doesn’t have & sign in OptionCaption, RunPageLink works properly.

 

Add multiple Excel worksheets to single Excel workbook – Dynamics NAV 2013

In Microsoft Dynamics NAV 2013 Excell Buffer table (370) changed from using Excel Automation variable to Open XML 2.0 DotNet interoperability classes. More information about that can be found on NAV Team blog in article Excel Buffer Using Open XML Instead of Excel Automation.

The one function that is needed, in real life, when exporting data to Excel workbook is to export data to the same Excel workbook but to different worksheets.

Let say you want to export Item and Item Ledger Entry table to single Excel workbook. I haven’t found any function in ExcelBuffer that does that – correct me if I am wrong.

So I decided to write function that enables just that, open single Excel workbook and just adds new worksheet one after another. In Excel Buffer table (370) I have created AddNewSheet function that receives only one parameter (SheetName).

AddNewSheet(SheetName : Text[250])
IF SheetName = '' THEN
  ERROR(Text002);

IF ISNULL(XlWrkBkWriter) THEN BEGIN
  CreateBook(SheetName);
  ActiveSheetName := SheetName; //first sheet activated
END ELSE BEGIN
  WHILE XlWrkBkWriter.HasWorksheet(SheetName) DO BEGIN
    IF NOT FirstIncrement THEN BEGIN
      SheetName := SheetName + '-01';
      FirstIncrement := TRUE;
    END ELSE BEGIN
      SheetName := INCSTR(SheetName);
    END;
  END;
  XlWrkShtWriter := XlWrkBkWriter.AddWorksheet(SheetName);
  FirstIncrement := FALSE;
  //ActiveSheetName := SheetName; //last sheet activated
END;
WriteSheet('',COMPANYNAME,USERID);
DELETEALL;
ClearNewRow;

Additional variable that I have used is FirstIncrement (Boolean) – all other are global variables in Excel Buffer table.

Let me explain few key points in this code. First in line 5 we check if the WorkSheetWriter is initialized. If it is not initialized we call standard CreateBook function and set the ActiveSheetName to that “first” excel sheet.

In line 9 we check if there is sheet in workbook that has the same name as the one we have passed as parameter – which can happen in subsequent calls. If this is true then we append ‘-01’ so we can use INCSTR(SheetName) until we find unused SheetName – so we don’t overwrite data on existing sheet.

Then the main task of this function is executed  in line 17 when we append new worksheet to currently open workbook.

In line 19 I have commented out line of code that sets the last sheet that we call to be initially shown when we open Excel workbook. I really want the first worksheet to be shown but you might want it the other way.

Finally, we WriteSheet and delete all data currently in Excel Buffer.

When the code, that populates data in Excel Buffer for single sheet, is finished we have to call this function to write sheet and flush all the data so it is free for another sheet.

Also, there is a difference that we cannot use standard function CreateBookAndOpenExcel – because it is used for single worksheet case scenario – but we must “close” Excel manually by calling standard functions CloseBook, OpenExcel, GiveUserControl on Excel Buffer.

Test codeunit that simulates usage of this function follows.

OBJECT Codeunit 50000 Test AddNewSheet
{
  OBJECT-PROPERTIES
  {
    Date=27.08.14;
    Time=10:58:00;
    Modified=Yes;
    Version List=;
  }
  PROPERTIES
  {
    OnRun=BEGIN
            CreateItemSheet;
            CreateItemLedgerEntrySheet;
            CreateItemSheet;
            CreateItemSheet;
            CreateItemLedgerEntrySheet;
            CreateItemLedgerEntrySheet;
            CreateItemSheet;
            CreateItemSheet;
            CreateItemLedgerEntrySheet;
            CreateItemLedgerEntrySheet;

            ExcelBuff.CloseBook;
            ExcelBuff.OpenExcel;
            ExcelBuff.GiveUserControl;
          END;

  }
  CODE
  {
    VAR
      Item@1002 : Record 27;
      ItemLedgerEntry@1001 : Record 32;
      ExcelBuff@1000 : TEMPORARY Record 370;

    PROCEDURE CreateItemSheet@1();
    BEGIN
      ExcelBuff.AddColumn(FORMAT(Item.FIELDCAPTION("No.")),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
      ExcelBuff.AddColumn(FORMAT(Item.FIELDCAPTION(Description)),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
      ExcelBuff.AddColumn(FORMAT(Item.FIELDCAPTION("Base Unit of Measure")),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
      ExcelBuff.AddColumn(FORMAT(Item.FIELDCAPTION(Inventory)),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
      ExcelBuff.NewRow;

      Item.RESET;
      IF Item.FINDSET THEN REPEAT
        Item.CALCFIELDS(Inventory);
        ExcelBuff.AddColumn(FORMAT(Item."No."),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
        ExcelBuff.AddColumn(FORMAT(Item.Description),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
        ExcelBuff.AddColumn(FORMAT(Item."Base Unit of Measure"),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
        ExcelBuff.AddColumn(FORMAT(Item.Inventory),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
        ExcelBuff.NewRow;
      UNTIL Item.NEXT = 0;

      ExcelBuff.AddNewSheet(Item.TABLECAPTION);
    END;

    PROCEDURE CreateItemLedgerEntrySheet@2();
    BEGIN
      ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry.FIELDCAPTION("Item No.")),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
      ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry.FIELDCAPTION("Posting Date")),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
      ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry.FIELDCAPTION("Document No.")),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
      ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry.FIELDCAPTION(Quantity)),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
      ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry.FIELDCAPTION("Remaining Quantity")),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
      ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry.FIELDCAPTION("Entry No.")),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
      ExcelBuff.NewRow;

      ItemLedgerEntry.RESET;
      ItemLedgerEntry.SETCURRENTKEY("Item No.","Posting Date");
      IF ItemLedgerEntry.FINDSET THEN REPEAT
        ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry."Item No."),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
        ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry."Posting Date"),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Date);
        ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry."Document No."),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Text);
        ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry.Quantity),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Number);
        ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry."Remaining Quantity"),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Number);
        ExcelBuff.AddColumn(FORMAT(ItemLedgerEntry."Entry No."),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuff."Cell Type"::Number);
        ExcelBuff.NewRow;
      UNTIL ItemLedgerEntry.NEXT = 0;

      ExcelBuff.AddNewSheet(ItemLedgerEntry.TABLECAPTION);
    END;

    BEGIN
    {
      ZORAN - Excel buffer AddNewSheet Test
    }
    END.
  }
}

I hope this helps someone and please feel free to comment.

 

Explicit error handling when working with XMLPort

During my development of Dynamics NAV Web Services for client’s integration with with their web shop I have had unforseen behaviour of XMLPorts.

When you use SETTABLEVIEW function of XMLPort to narrow data to export you use some kind of input parameter (or parameters) you can do something like this:

GetShipToAddress(CustID : Code[20];VAR XMLShipToAddress : XMLport "Ship To Address")
IF CustID <> '' THEN BEGIN
 ShipToAddressLoc.RESET;
 ShipToAddressLoc.SETRANGE("Customer No.",CustID);
 XMLShipToAddress.SETTABLEVIEW(ShipToAddressLoc);
END;

But with this approach you risk the unintentional exposure of all data. Because of using XMLPort as VAR there is no filter in XMLPort – unless defined otherwise on Node element in XMLPort used.

When XMLPort is used in function exposed as web service of codeunit it is “automagically” populated with data AFTER the function has finished. Therefore, if no filter is set on XMLPort it is populated with ALL data from that/those tables used in it.

The secure way of doing this is with proper error handling. Something like this:

GetShipToAddress(CustID : Code[20];VAR XMLShipToAddress : XMLport "Ship To Address")
IF CustID <> '' THEN BEGIN
 ShipToAddressLoc.RESET;
 ShipToAddressLoc.SETRANGE("Customer No.",CustID);
 XMLShipToAddress.SETTABLEVIEW(ShipToAddressLoc);
END ELSE BEGIN
 ERROR(Text001);
END;

When inserting data though web services using XMLPort you should just use XMLPort.IMPORT function in you exposed web service function. All other required data checking should be done in OnBeforeInsertRecord trigger of Table element in XMLPort.

ShipToAddressTable - Import::OnBeforeInsertRecord()
IF ("Ship-to Address"."Customer No." = '') THEN
 ERROR(Text001);
IF ("Ship-to Address".Code = '') THEN
 ERROR(Text002);

When calling web service functions from client side, you should be writing your calls within try/catch block of code so you get proper exception/error code.

These are some thing you should have in mind when using XMLPorts in exposed web services to get enhanced security of data.

 

 

Metadata for object of type CodeUnit with id ***** is in a failed state.

While developing Windows Embedded 6.5.3 application for WMS (Warehouse Management System) which relies on Dynamics NAV web services I have encountered rather “familiar” error:

Metadata for object of type CodeUnit with id ****** is in a failed state…. error CS1001: Identifier expected.

OK, I said, I will recompile the object and restart Microsoft Dynamics NAV Business Web Services.

Recompile, restart… same error. What now? Hmm… restart Web Services Windows Service again. Nope!

Went into code and view the last function I have edited. Nothing suspicious. Compile – everything OK. Puf?!

Let’s go to error again (reading it whole this time). Whoa… Error details info:

…Error details: c:\ProgramData\Microsoft\Microsoft Dynamics NAV\60\Server\MicrosoftDynamicsNavServer\source\Codeunit\Codeunit50004.cs(498,163) : error CS1001: Identifier expected

Ok. Let’s open the error details file. It opens in Microsoft Visual Studio Tools for Applications. Went to line 498 and position 163 and I can see the following line of my function (with source in C#) which ends like:

CS1001_identifier_expectedThere is Int32 param in my function without identifier (which the error clearly states)!

Went back to my codeunit in NAV and opened C/AL locals of the function I have edited and I have something to see. There is a parameter WITHOUT NAME of Integer Type.

dynamicsNAV_identifierexpectedSomehow, while editing parameter list I have, inadvertently, created new parameter row, never assigning a name to it. NAV thinks this is OK and allows the codeunit to be compiled, but the NAV Business Web Services doesn’t like this.

Resolution

Resolution was simple. Removed the “unintentional” parameter, recompiled codeunit, restarted NAV Web Service and everything was fine, again. 🙂

 

 

No execute permission on ” CodeUnit with ID 1.

When our client tried to connect with RTC to the application server he got error:

No execute permission on ” CodeUnit with ID 1.

The problem was raised when the client’s administrator was instructed to tighten NAV security assigning each user “Role ID” and “Company Name” in Logins->Roles.

After that the users could not login to the application server giving them previous error.

Solution

I found the solution on another blog (http://thinkaboutit.be/2012/07/no-execute-permission-on-codeunit-with-id-1/)

Basically the “User personalization” was not set for users that had “Company Name” assigned to their login Role. “User personalization” is found under Administration->Application Setup->RoleTailored Client->User Personalization.

User Personalization
User Personalization.

Creating new record with user who has a “Company Name” assigned should solve the RTC login error.