Conventions, Or Should I Say Conversions
Often you have to convert objects from one type to another e.g. from Integer to Text, from Boolean to Text, etc. Up to version 4.0 there was a codeunit, Codeunit 6201 – Conventions, that had a bunch of functions to convert objects from one type to another. This codeunit disappeared from version 5.0 but many of the functions are still useful, if not to use them than at least to give ideas for other functions or, as they did for me, to start learnig some C/AL code.
So have a look at the code or download the attached codeunit Conventions of which I never understood where the name came from.
CodeToText(VarCode : Code[250];MaxLength : Integer) : Text[250]
IF Setup.RECORDLEVELLOCKING THEN
EXIT(VarCode);
EXIT(LeftPadCode(VarCode,MaxLength,’ ‘));
OptionToText(VarOption : Option) : Text[250]
EXIT(FORMAT(VarOption,0,2));
IntegerToText(VarInteger : Integer) : Text[250]
EXIT(FORMAT(VarInteger,0,2));
SQLDecimalToText(VarDecimal : Decimal) ReturnValue : Text[260]
DecimalSepSymbol := ‘.’;
ReturnValue :=
FORMAT(VarDecimal,0,’<Sign><Integer><Decimals><Comma,’ + DecimalSepSymbol + ‘>’);
CSDecimalToText(VarDecimal : Decimal) ReturnValue : Text[260]
WITH CommercePortalSetup DO BEGIN
GET;
CASE “Decimal Symbol” OF
“Decimal Symbol”::”0″:
DecimalSepSymbol := ‘.’;
“Decimal Symbol”::”1″:
DecimalSepSymbol := ‘,’;
END;
END;
ReturnValue :=
FORMAT(VarDecimal,0,’<Sign><Integer><Decimals><Comma,’ + DecimalSepSymbol + ‘>’);
BooleanToText(VarBoolean : Boolean) : Text[1]
EXIT(FORMAT(VarBoolean,1,2));
DateToText(VarDate : Date) : Text[8]
IF VarDate = 0D THEN
VarText := ’17530101′
ELSE
VarText := FORMAT(VarDate,0,’<Year4><Month,2><Day,2>’);
EXIT(VarText);
TimeToText(VarTime : Time) ReturnTime : Text[16]
ReturnTime := FORMAT(VarTime,0,’<Hours24,2>:<Minutes,2>:<Seconds,2>’);
IF ReturnTime = ” THEN
ReturnTime := ’00:00:00′;
TextToInteger(VarText : Text[250]) : Integer
EVALUATE(VarInteger,VarText);
EXIT(VarInteger);
TextToDecimal(VarText : Text[250]) : Decimal
BaseVarDecimal := 1.2;
DecimalSymbol := COPYSTR(FORMAT(BaseVarDecimal),2,1);
EVALUATE(VarDecimal,CONVERTSTR(VarText,’.',DecimalSymbol));
EXIT(VarDecimal);
TextToBoolean(VarText : Text[1]) : Boolean
IF NOT EVALUATE(VarBoolean,VarText) THEN
VarBoolean := FALSE;
EXIT(VarBoolean);
TextToDate(VarText : Text[8]) : Date
Day := TextToInteger(COPYSTR(VarText,7,2));
Month := TextToInteger(COPYSTR(VarText,5,2));
Year := TextToInteger(COPYSTR(VarText,1,4));
EXIT(DMY2DATE(Day,Month,Year));
TextToTime(VarText : Text[8]) : Time
BaseVarTime := 111111T;
TimeSeparator := COPYSTR(FORMAT(BaseVarTime),3,1);
EVALUATE(VarTime,CONVERTSTR(VarText,’:',TimeSeparator));
EXIT(VarTime);
SQLEncodeText(NonEncodedText : Text[250]) : Text[250]
EncodedText := ”;
FOR i := 1 TO STRLEN(NonEncodedText) DO BEGIN
EncodedText[STRLEN(EncodedText) + 1] := NonEncodedText[i];
IF NonEncodedText[i] = ”” THEN
EncodedText := EncodedText + ””;
END;
EXIT(”” + EncodedText + ””)
CSEncodeText(NonEncodedText : Text[250]) : Text[250]
EXIT(NonEncodedText);
SQLEncodeCode(VarCode : Code[250];Length : Integer) : Text[250]
EXIT(SQLEncodeText(CodeToText(VarCode,Length)));
CSEncodeCode(VarCode : Code[250]) : Text[250]
EXIT(VarCode);
SQLEncodeOption(VarOption : Option) : Text[250]
EXIT(SQLEncodeText(OptionToText(VarOption)));
CSEncodeOption(VarOption : Option) : Text[250]
EXIT(CSEncodeText(OptionToText(VarOption)));
SQLEncodeInteger(VarInteger : Integer) : Text[250]
EXIT(SQLEncodeText(IntegerToText(VarInteger)));
CSEncodeInteger(VarInteger : Integer) : Text[250]
EXIT(CSEncodeText(IntegerToText(VarInteger)));
SQLEncodeDecimal(VarDecimal : Decimal) : Text[250]
EXIT(SQLEncodeText(SQLDecimalToText(VarDecimal)));
CSEncodeDecimal(VarDecimal : Decimal) : Text[250]
EXIT(CSDecimalToText(VarDecimal));
SQLEncodeBoolean(VarBoolean : Boolean) : Text[5]
EXIT(SQLEncodeText(BooleanToText(VarBoolean)));
CSEncodeBoolean(VarBoolean : Boolean) : Text[5]
EXIT(CSEncodeText(BooleanToText(VarBoolean)));
SQLEncodeDate(VarDate : Date) : Text[12]
EXIT(SQLEncodeText(DateToText(VarDate)));
CSEncodeDate(VarDate : Date) : Text[12]
EXIT(CSEncodeText(DateToText(VarDate)));
SQLEncodeTime(VarTime : Time) : Text[14]
EXIT(SQLEncodeText(TimeToText(VarTime)));
SQLEncodeDateTime(VarDate : Date;VarTime : Time) : Text[19]
EXIT(SQLEncodeText(DateToText(VarDate) + ‘ ‘ + TimeToText(VarTime)));
LeftPadCode(VarCode : Code[250];MaxLength : Integer;FillChar : Text[1]) : Text[250]
Length := MaxLength – STRLEN(VarCode);
IF Length <= 0 THEN
EXIT(VarCode);
EXIT(PADSTR(”,Length,FillChar) + VarCode);
PutSynchMessageValue(VAR SynchMessageQueue : Record Table6224;No : Integer;Name : Text[190];Value : Text[250])
SynchMessageQueue.”No.” := No;
SynchMessageQueue.Name := Name;
SynchMessageQueue.Value := Value;
SynchMessageQueue.INSERT;
HEXEncode(Char : Char) : Text[3]
AsciiValue := Char;
IF AsciiValue > 255 THEN
ERROR(Text000,Char);
REPEAT
a := AsciiValue DIV 16;
b := AsciiValue – a * 16;
AsciiValue := a;
HexValue := COPYSTR(’0123456789ABCDEF’,b + 1,1) + HexValue;
UNTIL AsciiValue = 0;
IF STRLEN(HexValue) < 2 THEN
HexValue := ’0′ + HexValue;
EXIT(‘-’ + HexValue);
LinkEncode(WebSiteCode : Code[20];LinkNo : Code[20];TemplateNo : Code[20]) : Text[250]
IF NOT PagePropColl.GET(WebSiteCode,LinkNo,1) THEN
EXIT;
IF (TemplateNo <> ”) AND
(PagePropColl.”Template Prop. Coll. No.” <> 0)
THEN
IF TemplPropColl.GET(
WebSiteCode,PagePropColl.”Template No.”,
PagePropColl.”Template Prop. Coll. No.”)
THEN
IF TemplPropColl.”ASP FileName” <> ” THEN
EXIT(UPPERCASE(TemplPropColl.”ASP FileName”) + ‘,’ + LinkNo);
RemoveBackslashIfThere(VAR Path : Text[256])
IF Path <> ” THEN BEGIN
IF Path[STRLEN(Path)] = ‘\’ THEN
Path := COPYSTR(Path,1,STRLEN(Path) – 1);
END;
MakeFilePath(FileFolder : Text[256];FileName : Text[256]) : Text[250]
IF FileName = ” THEN
ERROR(Text001);
IF FileFolder = ” THEN
EXIT(FileName);
EXIT(FileFolder + ‘\’ + FileName);
ReportNonValidValue(Name : Text[150];Value : Text[256])
IF Value = ” THEN
ERROR(Text002,Name)
ELSE
ERROR(Text003,Value,Name);
CheckForInvCharAndMakeDescript(FieldName : Text[250];FieldValue : Text[250];VAR Description : Text[250])
FOR i := 1 TO STRLEN(FieldValue) DO
IF FieldValue[i] IN ['.',',',' '] THEN
ERROR(Text004,FieldName);
IF FieldValue = ” THEN
Description := ”
ELSE
Description := UPPERCASE(COPYSTR(FieldValue,1,1)) + LOWERCASE(COPYSTR(FieldValue,2));
Download the Codeunit by clicking the link below and renaming the file to .zip:Codeunit 6201, rename from .doc to .zip.
Make Exporting Excel Formulas Fully Multilanguage Aware
I have been struggling with exporting data from Dynamics NAV to Excel Spreadsheets including formulas lately. I had the problem with a custom developed export to excel so I checked the code in the Excel Buffer table to see how standard Navision solved the problem only to see that the problem also existed in standard NAV too.
Let’s explain the problem first. What happens is that for people with a Microsoft Office version in Language A and a Dynamics NAV client in language B, Navision does not translate the formulas correctly. You can reproduce it as follows.
Make sure the language of your Dynamics NAV client and of Microsoft Office are different. Open your Dynamics NAV client go to the Budget form and run the “Export to Excel …” function. You’ll see that the formulas aren’t be translated as they should, giving errors in Excel.
English NAV Client and Spanish Office, wrong formula
Standard Dynamics NAV uses multilanguage text constants to translate the Formulas in the Excel Buffer table. The problem is that this only works if your Microsoft Office language equals your Dynamics NAV Client language and on top of this, for it to work you need to translate ALL the formulas you want to use to ALL the languages you want to use. Not a fun thing to do but feel free to use a page like this fonctions XL en 9 langues or this Microsoft Excel function translations to get started putting all your formulas into multilanguage text constants.
Since we have some workstations where the Dynamics NAV client and Microsoft Office use a different language, I was looking for something better and this post on mibuso handed me the solution. Below I put the code if you want this feature to work for the Excel Buffer table but you’ll see that it is easy to adopt it to all of you custom code that exports to Excel with formulas.
Table 370 – Excel Buffer
Add the following local variables to the CreateSheet function:
- autScriptControl Automation variable with SubType ‘Microsoft Script Control 1.0′.ScriptControl
- txtCode Text variable with Length 1024
- autDictionary Automation variable with Subtype ‘Microsoft Scripting Runtime’.Dictionary
- txtFormula Text variable with Length 1024
- intI Integer variable
Remove all languages but English from the text constants SUM, SUMIF and others should there be others.
Change to code of the CreateSheet function as below:
XlWrkSht.Name := SheetName;
IF ReportHeader <> '' THEN
XlWrkSht.PageSetup.LeftHeader :=
STRSUBSTNO('%1%2%1%3%4',GetExcelReference(1),ReportHeader,CRLF,CompanyName);
XlWrkSht.PageSetup.RightHeader :=
STRSUBSTNO(Text006,GetExcelReference(2),GetExcelReference(3),CRLF,UserID2);
XlWrkSht.PageSetup.Orientation := XlLandscape;
IF FIND('-') THEN BEGIN
//CRQ.001 start insert
CREATE(autDictionary);
CREATE(autScriptControl);
//CRQ.001 end insert
REPEAT
RecNo := RecNo + 1;
Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));
IF NumberFormat <> '' THEN
XlWrkSht.Range(xlColID + xlRowID).NumberFormat := NumberFormat;
//CRQ.001 delete start
// IF Formula = '' THEN
// XlWrkSht.Range(xlColID + xlRowID).Value := "Cell Value as Text"
// ELSE
// XlWrkSht.Range(xlColID + xlRowID).Formula := GetFormula;
//CRQ.001 delete end
//CRQ.001 start insert
IF Formula = '' THEN
XlWrkSht.Range(xlColID + xlRowID).Value := "Cell Value as Text"
ELSE BEGIN
txtFormula := GetFormula();
txtCode :='objCell.Formula=objList.Item(1);';
intI := 1;
autScriptControl.Language := 'JScript';
autScriptControl.AddObject('objCell', XlWrkSht.Range(xlColID + xlRowID));// Add excel cell object
autScriptControl.AddObject('objList', autDictionary); // Add dictionary object
autDictionary.Add(intI,txtFormula); // Add formula to dictionary object
autScriptControl.ExecuteStatement(txtCode); // Execute javascript
autDictionary.RemoveAll(); // Remove item from dictionary
autScriptControl.Reset(); // Reset Script object for next using
END;
//CRQ.001 end insert
IF Comment <> '' THEN
XlWrkSht.Range(xlColID + xlRowID).AddComment := Comment;
IF Bold THEN
XlWrkSht.Range(xlColID + xlRowID).Font.Bold := Bold;
IF Italic THEN
XlWrkSht.Range(xlColID + xlRowID).Font.Italic := Italic;
XlWrkSht.Range(xlColID + xlRowID).Borders.LineStyle := XlLineStyleNone;
IF Underline THEN
XlWrkSht.Range(xlColID + xlRowID).Borders.Item(XlEdgeBottom).LineStyle := XlContinuous;
UNTIL NEXT = 0;
//CRQ.001 start insert
CLEAR(autDictionary);
CLEAR(autScriptControl);
//CRQ.001 end insert
XlWrkSht.Range(GetExcelReference(5) + ':' + xlColID + xlRowID).Columns.AutoFit;
END;
Let’s have a look at the “Export to Excel …” function on the Budget form again.
English NAV Client and Spanish Office, correct formula
That’s all, it is that simple, no more text constants, no more looking for translations of a formula, just put all your fomulas in English and let Excel do the work. Please let me know if you know a faster/better/cleaner way to do this.
Show Shipment Information on Sales Invoices
When using combined shipments or when using the “Get Shipment Lines…” function on the invoice form, a line with shipment information is shown for all shipments linked to the invoice lines.
Sales Invoice with shipment information.
This is very useful information for the customer and for customer service. It gives them a direct link between shipments and the invoices. Unfortunately this information line is not added when you “Ship and Invoice” an Order.
With the following adjustment to codeunit 80, the extra line is also added when you post “Ship and Invoice” an order.
Codeunit 80 – Sales-Post
...
IF Invoice THEN BEGIN
// Insert invoice line or credit memo line
IF "Document Type" IN ["Document Type"::Order,"Document Type"::Invoice] THEN BEGIN
//insertion start
//Add shipment info if this is the first line of an invoice and there is shipping info.
recSalesInvLines.SETRANGE("Document No.",SalesInvHeader."No.");
IF recSalesInvLines.ISEMPTY AND (SalesShptHeader."No." <> '') AND (SalesShptHeader."Shipment Date" <> 0D) THEN BEGIN
SalesInvLine.INIT;
SalesInvLine."Document No." := SalesInvHeader."No.";
SalesInvLine.Description :=
STRSUBSTNO(Text92000,SalesShptHeader."No.",SalesShptHeader."Shipment Date");
SalesInvLine.INSERT;
END;
//insertion end
SalesInvLine.INIT;
SalesInvLine.TRANSFERFIELDS(TempSalesLine);
SalesInvLine."Document No." := SalesInvHeader."No.";
SalesInvLine.Quantity := TempSalesLine."Qty. to Invoice";
SalesInvLine."Quantity (Base)" := TempSalesLine."Qty. to Invoice (Base)";
...
Multilanguage Value of Text92000: ENU=Shipment No. %1 of %2:;DES=Lieferung Nr. %1 vom %2:;ITS=Fornitura Nr. %1 del %2:;FRS=Livraison n° %1 du %2.
A text line with the Shipment No. and the Shipment Date will now be added to the invoice every time you ship and invoice an order.
Avoid “not a valid time unit” Errors with Multilanguage Calcdates
I had some problems with a German report giving errors when I tried to run it. The people that programmed it put not only the comments in German, but also all calcdates.
As all calcdates where entered in German when I ran the report in an English client, I got an error that ‘-1T’ was not a valid time unit. This is because in English you need to write D for day not T for Tage.
To avoid errors like this and make your reports multilanguage, always write your date formulas in English surrounded by ‘<’ and ‘>’. The report will now use the correct date calculation mo matter what the language of the client is.
Example: CALCDATE(‘+12M-1T’,BeginnGeschäftsjahr) becomes CALCDATE(‘<+12M-1D>’,BeginnGeschäftsjahr).
The date formula '-1T' should include a time unit.
Storing Password in SSIS Packages
Recently I had to make an SSIS package where data was loaded from an external database (Oracle in my case) to a SQL Server 2005 database.
All went well except one thing. How to save the Oracle and SQL 2005 connection credentials encrypted in the package? I wanted to store the credentials in a way that a user with rights to execute the package, could do this without seeing the credentials of the databases the package connects to.
I tried all kind ProtectionLevel settings in Business Intelligence Development Studio but nothing worked. I tried with a configuration file, which worked but this left the connection data visible for people with access to the configuration file.
The only ProtectionLevel I did not managed to save the package with was ServerStorage. When I tried to save the package I always got the following message:
In the end it seemed that the only way to set this property and to save the file without error is using Save Copy of Package.dtsx As….
- Select File > Save Copy of Package.dtsx As …
- Select the server and location where you want to save the package and click on the button with a dot next to the greyed out protection level.
- Now you can change the package protection level to the last option “Rely on server storage and roles for access control”.
Now everyone that has access to execute the package will be able to execute it without getting errors that the credentials of the datbases the package connects to are invalid or “Failed to decrypt protected XML node …” errors.
Too Many Invoice Copies
Ever had the “Too many copies are printed!” problem? Your customer wants to prints an invoice copy when posting invoices. So you set the Invoice Copies field on the Invocing tab of the Customer Card to 1.
Problem is, now the program will always print an invoice copy even when you manually reprint a posted invoice. Even though the option tab of the report shows No. Of Copies = 0 , a copy will be printed. If you change the No. Of Copies to 1, the system prints 2 copies, it doesn’t make sense!
The happens because of the following line of code in the CopyLoop OnPreDataItem() trigger:
NoOfLoops := ABS(NoOfCopies) + Cust."Invoice Copies" + 1;
The system actually takes the sum of the No. of Copies on the option tab and the Invoice Copies on the Customer Card to calculate how many copies it will print, this makes it impossible no to print an invoice copy when reprinting posted invoices, which is probable not what you want.
I used the solution below which will use the Invoice Copies field on the customer card to calculate the no. of copies when posting and the No. of Copies on the option tab of the report when manually printing a posted invoice, not the sum of both.
In the The CopyLoop OnPreDataItem() I changed
NoOfLoops := ABS(NoOfCopies) + Cust."Invoice Copies" + 1;
to
IF blManuallyOpened THEN NoOfLoops := ABS(NoOfCopies) + 1 ELSE NoOfLoops := Cust."Invoice Copies" + 1;
And in the CopyLoop – OnOpenForm() trigger I added the following line:
blManuallyOpened := TRUE;
Since the CopyLoop – OnOpenForm() trigger is only run when you open the report manually, it will now use the info from the customer card to calculate copies when posting and the info from the option tab of the report to calculate copies when printing manually.
Below you see the screens where the no. of copies are set.
Recent Comments