Extend CU 6224: Read xml node values and attributes

Xml documents can be created/processed via XmlPorts. An other opportunity is the usage of Codeunit 6224. It’s used for complex xml handling. This CU only delivers some create and find functions, but no read/get functions. Following functions extend CU 6224 with read functions.

First a sample for the usage of the new functions. This sample shows, how to load a xml document given as string and read some of the node attributes.

// local variables
// XmlMgmt	Codeunit	XML DOM Management	
// xmlDoc	Automation	'Microsoft XML, v3.0'.DOMDocument60	
// rootNode	Automation	'Microsoft XML, v3.0'.IXMLDOMNode	
// childNode	Automation	'Microsoft XML, v3.0'.IXMLDOMNode	
// address	Text		30
// prio	Integer		
// createdAt	Date		
CREATE(xmlDoc);
xmlDoc.loadXML('<mail priority="1" createdAt="26/5/2014"><from address="from@test.com"/>' +
'<to name="to@test.com"/><state value="1"/></mail>');
rootNode := xmlDoc.selectSingleNode('mail');
prio := XmlMgmt.GetAttributeValueAsInt(rootNode,'priority');
createdAt := XmlMgmt.GetAttributeValueAsDate(rootNode,'createdAt');
childNode := rootNode.firstChild;
address := XmlMgmt.GetAttributeValueAsText(childNode,'address');
MESSAGE('mail: ' + FORMAT(prio) + ',' + FORMAT(createdAt) + ',' + address);
CLEAR(rootNode);
CLEAR(childNode);
CLEAR(xmlDoc);

The new functions:

// Read an attribute value from a given node, e.g. from <data att1="1"/> gets value 1
GetAttributeValueAsText(xmlNodePar : Automation "'Microsoft XML, v3.0'.IXMLDOMNode";attributeNamePar : Text[100]) : Text
// attributeNodeLoc | Automation | 'Microsoft XML, v3.0'.IXMLDOMNode
SetNormalCase;
IF ISCLEAR(xmlNodePar) THEN
  EXIT('Param. xmlNodePar must not be null');
IF attributeNamePar = '' THEN
  EXIT('Param. attributeNamePar must not be empty');
attributeNodeLoc := xmlNodePar.attributes.getNamedItem(attributeNamePar);
IF ISCLEAR(attributeNodeLoc) THEN
  EXIT('Attribute ' + attributeNamePar + ' not found');
IF STRLEN(attributeNodeLoc.text) > 0 THEN
  EXIT(attributeNodeLoc.text);
EXIT('');

GetAttributeValueAsInt(xmlNodePar : Automation "'Microsoft XML, v3.0'.IXMLDOMNode";attributeNamePar : Text[100]) : Integer
// attrValLoc | Text
// returnValueLoc | Integer
attrValLoc := GetAttributeValueAsText(xmlNodePar,attributeNamePar);
IF attrValLoc <> '' THEN
  IF NOT EVALUATE(returnValueLoc, attrValLoc) THEN
    EXIT(-1);
EXIT(returnValueLoc);

GetAttributeValueAsDec(xmlNodePar : Automation "'Microsoft XML, v3.0'.IXMLDOMNode";attributeNamePar : Text[100]) : Decimal
// attrValLoc | Text
// returnValueLoc | Decimal
attrValLoc := GetAttributeValueAsText(xmlNodePar,attributeNamePar);
IF attrValLoc <> '' THEN
  IF NOT EVALUATE(returnValueLoc, attrValLoc) THEN
    EXIT(-1);
EXIT(returnValueLoc);

GetAttributeValueAsDate(xmlNodePar : Automation "'Microsoft XML, v3.0'.IXMLDOMNode";attributeNamePar : Text[100]) : Date
// attrValLoc | Text
// returnValueLoc | Date
attrValLoc := GetAttributeValueAsText(xmlNodePar,attributeNamePar);
returnValueLoc := 0D;
IF attrValLoc <> '' THEN
  IF NOT EVALUATE(returnValueLoc, attrValLoc) THEN;
EXIT(returnValueLoc);

GetAttributeValueAsBool(xmlNodePar : Automation "'Microsoft XML, v3.0'.IXMLDOMNode";attributeNamePar : Text[100]) : Boolean
// attrValLoc | Text
// returnValueLoc | Boolean
attrValLoc := GetAttributeValueAsText(xmlNodePar,attributeNamePar);
returnValueLoc := FALSE;
IF attrValLoc <> '' THEN BEGIN
  IF NOT EVALUATE(returnValueLoc, attrValLoc) THEN
    ERROR('No bool value given');
  EXIT(FALSE);
END;
EXIT(returnValueLoc);

// Following is needed to read a node value, e.g. from <data>1</data> gets value 1
GetNodeValueAsText(xmlNodePar : Automation "'Microsoft XML, v3.0'.IXMLDOMNode") : Text[1024]
IF ISCLEAR(xmlNodePar) THEN
  EXIT('Param. xmlNode must not be null');
IF STRLEN(xmlNodePar.text) > 0 THEN
  EXIT(xmlNodePar.text);
EXIT('');

// Return a node value as Integer value
GetNodeValueAsInt(xmlNodePar : Automation "'Microsoft XML, v3.0'.IXMLDOMNode") : Integer
// attrValLoc | Text
// returnValueLoc | Integer
xmlNodeValueLoc := GetNodeValueAsText(xmlNodePar);
returnValueLoc := 0;
IF xmlNodeValueLoc <> '' THEN
  IF NOT EVALUATE(returnValueLoc, xmlNodeValueLoc) THEN
    ERROR('No int value given');
EXIT(returnValueLoc);

Instead of Xml vs. 3 you can also use Xml vs. 6, e.g. xmlNode : ‘Microsoft XML, v6.0’.IXMLDOMNode.

Advertisements

Convert date string containing a month text value to a date value

Assuming you want to enter date values in the RTC client like 15-MAR-14. that won’t work. date values are only accepted with syntax like 15.03, 15.03.14, etc. But it can be that users prefer the above syntax. so then you can use a text field in your page and convert internally the value into a date value. Following function converts that kind of date string to a date value.

// local variables
// dateString | Text
// dateValue  | Date
dateString := '15-MAR-14';
dateValue := ConvertDateString(dateString);
MESSAGE(FORMAT(dateValue,0,'<day,2>.<month,2>.<year4>'));

ConvertDateString(dateString : Text[9]) : Date
// local variables
// dateString | Text
// dayValue   | Integer
// months     | Text
// monthValue | Integer
// yearValue  | Integer
// dateValue  | Date
// mPos       | Integer
// monthText  | Text

if strpos(dateString,'-') > 0 then begin
  dateString := CONVERTSTR(dateString,'-',',');
  EVALUATE(dayValue,SELECTSTR(1,dateString));
  months := 'jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec';
  monthText := lowercase(SELECTSTR(2,dateString));
  if strlen(monthText) <> 3 then
    error('Invalid month expression: ' + monthText);
  mPos := STRPOS(months,monthText);
  if mPos >0 then begin
    monthValue := (mPos + 3) / 4;
    EVALUATE(yearValue,SELECTSTR(3,dateString));
    IF yearValue > 14 THEN
      yearValue := yearValue + 1900
    ELSE
      yearValue := yearValue + 2000;
    dateValue := DMY2DATE(dayValue,monthValue,yearValue);
    exit(dateValue);
  end else
    error('No valid month given: ' + monthText);
end;
exit(0D);

to use the standard functionality of date validation and conversion it’s needed to change function MakeDateText in codeunit 1:

MakeDateText(VAR DateText : Text[250]) : Integer
// additional local variables
// mPos | Integer
// monthText | Text | 10
// monthValue | Integer
// Text Constant: Text022 | jan,feb,mar,apr,may,jun,jul,aug,sep,oct,nov,dec

Position := 1;
Length := STRLEN(DateText);
ReadCharacter(' ',DateText,Position,Length);
 
// begin changes
IF STRPOS(DateText,'-') > 0 THEN BEGIN
  DateText := CONVERTSTR(DateText,'-',',');
  monthText := lowercase(SELECTSTR(2,DateText));
  if strlen(monthText) <> 3 then
    error('Invalid month expression: ' + monthText);
  mPos := STRPOS(Text022,monthText);
  IF (mPos > 0) THEN BEGIN
    monthValue := (mPos + 3) / 4;
    // optional: add a leading 0, if needed; simple use format(monthValue) should also work
    monthText := PADSTR('',2 - STRLEN(FORMAT(monthValue)),'0') + FORMAT(monthValue);
    DateText := SELECTSTR(1,DateText) + '-' + monthText + '-' + SELECTSTR(3,DateText);
  END ELSE
    error('No valid month given: ' + monthText);
END;
// end changes
 
IF NOT FindText(PartOfText,DateText,Position,Length) THEN
...

cheers

You cannot create an Automation object ‘name of autom.’ on Microsoft Dynamics NAV Server. You must create it on a client computer.

This is an error message, when trying to create an instance of an automation on a nav server vs. >= 2013. It’s only possible to create the automation instance as client instance. Creating automation instances on nav server is allowed till nav 2009.

solution:
use create(<name_of_autom.>,false,true) instead of create(<name_of_autom.>).

remarks:
you need a installed version of the automation on each client computer.

links:
create fct.: http://msdn.microsoft.com/en-us/library/dd355255(v=nav.70).aspx
Usage of automations in nav 2013: http://msdn.microsoft.com/en-us/library/ee909565(v=nav.70).aspx
Usage of automations in nav 2009: http://msdn.microsoft.com/en-us/library/ee909565(v=nav.60).aspx

Numbers to Words

This code converts given numbers to words.

For usage run (sample):

InitTextVariables;
numText := NumberToWords(1256,'');  // numText | Text | 100
Message(numText);
NumberToWords(number : Integer;appendScale : Text) : Text
numString := '';
IF number < 100 THEN
  IF number < 20 THEN
    numString := OnesText[number]
  ELSE BEGIN
    numString := TensText[number DIV 10];
    IF (number MOD 10) > 0 THEN
      numString := numString + ' ' + OnesText[number MOD 10];
  END
ELSE BEGIN
  pow := 0;
  powStr := '';
  IF number < 1000 THEN BEGIN // number is between 100 and 1000
    pow := 100;
    powStr := ThousText[1];
  END ELSE BEGIN // find the scale of the number
    log := number DIV 1000;
    pow := POWER(1000, log);
    powStr := ThousText[log + 1];
  END;

  numString := NumberToWords(number DIV pow, powStr) + ' ' + NumberToWords(number MOD pow,'');
END;

EXIT(numString + ' ' + appendScale);

// local variables
// numString	Text		
// pow	Integer		
// powStr	Text		
// log	Integer		

InitTextVariables()
OnesText[1] := 'one';
OnesText[2] := 'two';
OnesText[3] := 'three';
OnesText[4] := 'four';
OnesText[5] := 'five';
OnesText[6] := 'six';
OnesText[7] := 'seven';
OnesText[8] := 'eight';
OnesText[9] := 'nine';
OnesText[10] := 'ten';
OnesText[11] := 'eleven';
OnesText[12] := 'twelve';
OnesText[13] := 'thirteen';
OnesText[14] := 'fourteen';
OnesText[15] := 'fifteen';
OnesText[16] := 'sixteen';
OnesText[17] := 'seventeen';
OnesText[18] := 'eighteen';
OnesText[19] := 'nineteen';

TensText[1] := '';
TensText[2] := 'twenty';
TensText[3] := 'thirty';
TensText[4] := 'forty';
TensText[5] := 'fivty';
TensText[6] := 'sixty';
TensText[7] := 'seventy';
TensText[8] := 'eighty';
TensText[9] := 'ninty';

ThousText[1] := 'hundred';
ThousText[2] := 'thousand';
ThousText[3] := 'million';
ThousText[4] := 'billion';
ThousText[5] := 'trillion';

// global variables
// OnesText	Text	30 | Dim:20
// TensText	Text	30 | Dim:10
// ThousText	Text	30 | Dim:5

Date Functions

Date functions are quite often used and needed. Here are some samples:

  1. Calculate the 1st of current month:
    EndDate := CALCDATE('-CM');
    
  2. Calculate last day of current month:
    EndDate := CALCDATE('CM');
    
  3. Calculate the end date of a defined period (e.g.3 months) starting with the 1st of the current month; get the last day of the end month:
    EndDate := CALCDATE('-CM+3M-1D');
    EndDate := CALCDATE('CM+2M');
    
  4. Get start and end date of a period with given day values and variable month value:
    startDay, endDay, month, prevMonth, currYear | integer values
    month := 10  // sample value
    if month > 1 then prevMonth := month - 1 else prevMonth := 12;
    currYear := DATE2DMY(TODAY,3);  // get current year
    startDate := DMY2DATE(startDay, month - 1, currYear);
    endDate := DMY2DATE(endDay, month, currYear);
    

Be aware that the calc formulars are language specific. For german language e.g. use LM instead of CM, 1T instead of 1D.