Month: June 2012

Posted on

Using the CRM Connector integration for other integration

In my work with the employee punching I needed to be able to answer a request on changed employees.  In NAV 2009 R2 we got the CRM Connector and that included database triggers that are executed when the data is modified.  Previously we only had global triggers that where executed when the table triggers where executed.  That meant that a C/AL INSERT, MODIFY, DELETE or RENAME did not fire a global trigger but they do fire the database triggers.

The database trigger handling is based on single instance codeunit 5150, Integration Management.  Microsoft just released a patch for this codeunit.

What I did is to create another single instance codeunit as an add in for this codeunit.  Added my codeunit as a global variable to codeunit 5150 and made a few additions to the code.
[code]GetDatabaseTableTriggerSetup(TableID : Integer;VAR Insert : Boolean;VAR Modify : Boolean;VAR Delete : Boolean;VAR Rename : Boolean)
IF COMPANYNAME = ” THEN
EXIT;

//#PU-
IF PunchIntegrationAddin.GetConnectorEnabled(TableID,Insert,Modify,Delete,Rename) THEN
EXIT;
//#PU+

IF NOT MarketingSetupRead THEN BEGIN
MarketingSetupRead := TRUE;
IF MarketingSetup.GET THEN
IntegrationActivated := MarketingSetup."Enable Connector";
END;

IF NOT IntegrationActivated THEN
EXIT;

IF IsIntegrationRecord(TableID) OR IsIntegrationRecordChild(TableID) THEN BEGIN
Insert := TRUE;
Modify := TRUE;
Delete := TRUE;
Rename := TRUE;
END;

OnDatabaseInsert(RecRef : RecordRef)
PunchIntegrationAddin.OnDatabaseInsert(RecRef); //#PU-+
TimeStamp := CURRENTDATETIME;
UpdateParentIntegrationRecord(RecRef,TimeStamp);
InsertUpdateIntegrationRecord(RecRef,TimeStamp);

OnDatabaseModify(RecRef : RecordRef)
PunchIntegrationAddin.OnDatabaseModify(RecRef); //#PU-+
TimeStamp := CURRENTDATETIME;
UpdateParentIntegrationRecord(RecRef,TimeStamp);
InsertUpdateIntegrationRecord(RecRef,TimeStamp);

OnDatabaseDelete(RecRef : RecordRef)
PunchIntegrationAddin.OnDatabaseDelete(RecRef); //#PU-+
TimeStamp := CURRENTDATETIME;
UpdateParentIntegrationRecord(RecRef,TimeStamp);
IF IsIntegrationRecord(RecRef.NUMBER) THEN BEGIN
IntegrationRecord.SETRANGE("Record ID",RecRef.RECORDID);
IF IntegrationRecord.FINDFIRST THEN BEGIN
// Handle exceptions where "Deleted On" should not be set.
IF RecRef.NUMBER = DATABASE::"Sales Header" THEN BEGIN
FieldRef1 := RecRef.FIELD(58); // Invoice
SkipDeletion := FieldRef1.VALUE;
END;

IF NOT SkipDeletion THEN
IntegrationRecord."Deleted On" := TimeStamp;

CLEAR(IntegrationRecord."Record ID");
IntegrationRecord."Modified On" := TimeStamp;
IntegrationRecord.MODIFY;
END;
END;

OnDatabaseRename(RecRef : RecordRef;XRecRef : RecordRef)
PunchIntegrationAddin.OnDatabaseRename(RecRef,XRecRef); //#PU-+
TimeStamp := CURRENTDATETIME;
UpdateParentIntegrationRecord(RecRef,TimeStamp);
IF IsIntegrationRecord(RecRef.NUMBER) THEN BEGIN
IntegrationRecord.SETRANGE("Record ID",XRecRef.RECORDID);
IF IntegrationRecord.FINDFIRST THEN BEGIN
IntegrationRecord."Record ID" := RecRef.RECORDID;
IntegrationRecord.MODIFY;
END;
END;
InsertUpdateIntegrationRecord(RecRef,TimeStamp);[/code]
I also made a copy of table 5151, Integration Record, just for my employee changes.  This is the code in my single instance integration add in codeunit.
[code]OBJECT Codeunit 10001037 Punch Integration Addin
{
OBJECT-PROPERTIES
{
Date=27.06.12;
Time=14:39:59;
Modified=Yes;
Version List=PUNCH6.00;
}
PROPERTIES
{
Permissions=TableData 5200=r,
TableData 10001021=rimd,
TableData 10010238=r;
SingleInstance=Yes;
OnRun=BEGIN
END;

}
CODE
{
VAR
PunchSetupRead@1200050000 : Boolean;
IntegrationActivated@1200050001 : Boolean;
Text001@1200050002 : TextConst ‘ENU=Error creating %1 – %2. It already exists.;ISL=Villa kom upp við stofnun %1 – %2. Er þegar til.’;

PROCEDURE GetConnectorEnabled@1200050000(TableID@1200050004 : Integer;VAR Insert@1200050003 : Boolean;VAR Modify@1200050002 : Boolean;VAR Delete@1200050001 : Boolean;VAR Rename@1200050000 : Boolean) : Boolean;
VAR
PunchSetup@1005 : Record 10001049;
BEGIN
IF COMPANYNAME = ” THEN
EXIT;

IF NOT PunchSetupRead THEN BEGIN
PunchSetupRead := TRUE;
IF PunchSetup.GET THEN
IntegrationActivated := PunchSetup."Enable Connector";
END;

IF NOT IntegrationActivated THEN
EXIT(FALSE);

IF IsIntegrationRecord(TableID) OR IsIntegrationRecordChild(TableID) THEN BEGIN
Insert := TRUE;
Modify := TRUE;
Delete := TRUE;
Rename := TRUE;
END;

EXIT(Insert OR Modify OR Delete OR Rename);
END;

PROCEDURE OnDatabaseInsert@2(RecRef@1000 : RecordRef);
VAR
TimeStamp@1001 : DateTime;
BEGIN
TimeStamp := CURRENTDATETIME;
UpdateParentIntegrationRecord(RecRef,TimeStamp);
InsertUpdateIntegrationRecord(RecRef,TimeStamp);
END;

PROCEDURE OnDatabaseModify@3(RecRef@1000 : RecordRef);
VAR
TimeStamp@1001 : DateTime;
BEGIN
TimeStamp := CURRENTDATETIME;
UpdateParentIntegrationRecord(RecRef,TimeStamp);
InsertUpdateIntegrationRecord(RecRef,TimeStamp);
END;

PROCEDURE OnDatabaseDelete@4(RecRef@1000 : RecordRef);
VAR
IntegrationRecord@1001 : Record 10001021;
TimeStamp@1003 : DateTime;
BEGIN
TimeStamp := CURRENTDATETIME;
UpdateParentIntegrationRecord(RecRef,TimeStamp);
IF IsIntegrationRecord(RecRef.NUMBER) THEN BEGIN
IntegrationRecord.SETCURRENTKEY("Record ID");
IntegrationRecord.SETRANGE("Record ID",RecRef.RECORDID);
IF IntegrationRecord.FINDFIRST THEN BEGIN
IntegrationRecord."Deleted On" := TimeStamp;

CLEAR(IntegrationRecord."Record ID");
IntegrationRecord."Modified On" := TimeStamp;
IntegrationRecord.MODIFY;
END;
END;
END;

PROCEDURE OnDatabaseRename@5(RecRef@1000 : RecordRef;XRecRef@1001 : RecordRef);
VAR
IntegrationRecord@1003 : Record 10001021;
FieldRef1@1200050000 : FieldRef;
TimeStamp@1002 : DateTime;
BEGIN
TimeStamp := CURRENTDATETIME;
UpdateParentIntegrationRecord(RecRef,TimeStamp);
IF IsIntegrationRecord(RecRef.NUMBER) THEN BEGIN
IntegrationRecord.SETCURRENTKEY("Record ID");
IntegrationRecord.SETRANGE("Record ID",XRecRef.RECORDID);
IF IntegrationRecord.FINDFIRST THEN BEGIN
IntegrationRecord."Record ID" := RecRef.RECORDID;
FieldRef1 := RecRef.FIELD(1); // "Employee No."
IntegrationRecord."Employee No." := FieldRef1.VALUE;
IntegrationRecord.MODIFY;
END;
END;
InsertUpdateIntegrationRecord(RecRef,TimeStamp);
END;

PROCEDURE EnableConnector@7();
VAR
WebServiceLog@1200050000 : Record 10001017;
BEGIN
SetupWebService;
SetupIntegrationTables;
END;

PROCEDURE DisableConnector@10();
VAR
PunchSetup@1000 : Record 10001049;
Company@1002 : Record 2000000006;
MultipleCompanies@1003 : Boolean;
BEGIN
MultipleCompanies := FALSE;
Company.SETFILTER(Name,’<>%1′,COMPANYNAME);
IF Company.FINDSET THEN BEGIN
REPEAT
PunchSetup.CHANGECOMPANY(Company.Name);
IF PunchSetup.GET THEN
IF PunchSetup."Enable Connector" THEN
MultipleCompanies := TRUE;
UNTIL (Company.NEXT = 0) OR MultipleCompanies;
END;

IF NOT MultipleCompanies THEN BEGIN
DeleteWebService;
DeleteIntegrationTables;
END;
END;

LOCAL PROCEDURE SetupWebService@11();
VAR
WebService@1001 : Record 2000000076;
Objects@1002 : Record 2000000038;
IntegrationPage@1004 : Record 5150;
BEGIN
WITH WebService DO BEGIN
INIT;
"Object Type" := "Object Type"::Codeunit;
"Service Name" := ‘NAVPunch’;
"Object ID" := CODEUNIT::"Punch Web Service Mgt.";
Published := TRUE;
IF NOT INSERT THEN
MESSAGE(Text001,TABLECAPTION,"Service Name");
END;
END;

LOCAL PROCEDURE DeleteWebService@1200050001();
VAR
WebService@1001 : Record 2000000076;
Objects@1002 : Record 2000000038;
IntegrationPage@1004 : Record 5150;
BEGIN
WITH WebService DO BEGIN
IF GET("Object Type"::Codeunit,’NAVPunch’) THEN
DELETE;
END;
END;

LOCAL PROCEDURE SetupIntegrationTables@8();
BEGIN
InitializeIntegrationRecords(DATABASE::Employee);
InitializeIntegrationRecords(DATABASE::"Punch Employee Setup");
END;

LOCAL PROCEDURE DeleteIntegrationTables@1200050008();
BEGIN
DeleteIntegrationRecords(DATABASE::Employee);
DeleteIntegrationRecords(DATABASE::"Punch Employee Setup");
END;

LOCAL PROCEDURE InitializeIntegrationRecords@9(TableID@1000 : Integer);
VAR
RecRef@1002 : RecordRef;
BEGIN
WITH RecRef DO BEGIN
OPEN(TableID,FALSE);
IF FINDSET(FALSE) THEN
REPEAT
InsertUpdateIntegrationRecord(RecRef,CURRENTDATETIME);
UNTIL NEXT = 0;
CLOSE;
END;
END;

LOCAL PROCEDURE UpdateParentIntegrationRecord@17(RecRef@1000 : RecordRef;TimeStamp@1008 : DateTime);
VAR
Employee@1200050000 : Record 5200;
FieldRef1@1004 : FieldRef;
ParentRecRef@1006 : RecordRef;
BEGIN
CASE RecRef.NUMBER OF
DATABASE::"Punch Employee Setup":
BEGIN
FieldRef1 := RecRef.FIELD(1); // "Employee No."
IF Employee.GET(FieldRef1.VALUE) THEN BEGIN
ParentRecRef.GETTABLE(Employee);
InsertUpdateIntegrationRecord(ParentRecRef,TimeStamp);
END;
END;
END;
END;

LOCAL PROCEDURE InsertUpdateIntegrationRecord@16(RecRef@1000 : RecordRef;IntegrationLastModified@1001 : DateTime);
VAR
IntegrationRecord@1002 : Record 10001021;
FieldRef1@1200050000 : FieldRef;
BEGIN
IF IsIntegrationRecord(RecRef.NUMBER) THEN BEGIN
WITH IntegrationRecord DO BEGIN
SETCURRENTKEY("Record ID");
SETRANGE("Record ID",RecRef.RECORDID);
IF FINDFIRST THEN BEGIN
"Modified On" := IntegrationLastModified;
MODIFY;
END ELSE BEGIN
INIT;
"Integration ID" := CREATEGUID;
"Record ID" := RecRef.RECORDID;
"Table ID" := RecRef.NUMBER;
"Modified On" := IntegrationLastModified;
FieldRef1 := RecRef.FIELD(1); // "Employee No."
"Employee No." := FieldRef1.VALUE;
INSERT;
END;
END;
END;
END;

LOCAL PROCEDURE DeleteIntegrationRecords@1200050004(TableID@1200050000 : Integer);
VAR
IntegrationRecord@1200050001 : Record 10001021;
BEGIN
WITH IntegrationRecord DO BEGIN
SETRANGE("Table ID",TableID);
IF NOT ISEMPTY THEN
DELETEALL;
END;
END;

LOCAL PROCEDURE IsIntegrationRecord@6(TableID@1000 : Integer) : Boolean;
BEGIN
IF TableID IN
[DATABASE::Employee]
THEN
EXIT(TRUE);

EXIT(FALSE);
END;

LOCAL PROCEDURE IsIntegrationRecordChild@18(TableID@1000 : Integer) : Boolean;
BEGIN
IF TableID IN
[DATABASE::"Punch Employee Setup"]
THEN
EXIT(TRUE);

EXIT(FALSE);
END;

BEGIN
END.
}
}[/code]
This makes it easy for me to find out if the employee has changed since the last synchronization with this function.
[code]EmployeeModified(VAR Employee : Record Employee;LastModified : DateTime) : Boolean
IF LastModified = 0DT THEN EXIT(TRUE);

IF Integration.ISEMPTY THEN
EXIT(Employee."Last Date Modified" >= DT2DATE(LastModified));

RecRef.GETTABLE(Employee);
Integration.SETCURRENTKEY("Record ID");
Integration.SETRANGE("Record ID",RecRef.RECORDID);
IF Integration.FINDFIRST THEN
EXIT(Integration."Modified On" > LastModified)
ELSE
EXIT(FALSE);[/code]

Posted on

bioPDF writer

I have been using PDFCreator to create PDF documents from my NAV client.  I am also using it with NAS (Nav Application Server) on my Windows 2003 32bit server.  I needed to get this up an running on a 64bit Windows 2008 server and I ran into problems.

I could easily create the PDF documents from my client.  I saw the pdf file appear in my file system and attached it to my record.  But, when NAS was running the same job no files where created and the job failed.  I unsuccessfully tried a few tricks but nothing changed.  I decided to find another path and looked at bioPDF writer.

The result is a new single instance codeunit to handle the print of any report to a pdf file.  Lets say that the user would like to be able to print and open a sales invoice in pdf format.  Copy the function PrintRecords to PrintRecordsToPDF for table no. 112, Sales Invoice Header.  Add two lines and you are done.

[code htmlscript=”false”]WITH SalesInvHeader DO BEGIN
COPY(Rec);
FIND(‘-‘);
ReportSelection.SETRANGE(Usage,ReportSelection.Usage::"S.Invoice");
ReportSelection.SETFILTER("Report ID",'<>0’);
ReportSelection.FIND(‘-‘);
REPEAT
bioPDFMgt.BeforeReportPrint(ReportSelection."Report ID"); // Dynamics.is
REPORT.RUNMODAL(ReportSelection."Report ID",ShowRequestForm,FALSE,SalesInvHeader);
HYPERLINK(bioPDFMgt.AfterReportPrintGetFileName(ReportSelection."Report ID")); // Dynamics.is
UNTIL ReportSelection.NEXT = 0;
END;[/code]

Where bioPDFMgt is a local variable for the BioPDF Management Codeunit.

The BioPDF Management Codeunit needs to be added to global variable in Codeunit 1, ApplicationManagement.  Add to the FindPrinter function.

[code htmlscript=”false”]FindPrinter(ReportID : Integer) : Text[250]
// Dynamics.is start
IF bioPDFMgt.PrinterBufferExists(ReportID) THEN
EXIT(bioPDFMgt.GetPrinterName);
// Dynamics.is end

CLEAR(PrinterSelection);

IF NOT PrinterSelection.GET(USERID,ReportID) THEN
IF NOT PrinterSelection.GET(”,ReportID) THEN
IF NOT PrinterSelection.GET(USERID,0) THEN
IF PrinterSelection.GET(”,0) THEN;

// Dynamics.is start
bioPDFMgt.SaveLastPrinter(PrinterSelection."Printer Name");
// Dynamics.is end

EXIT(PrinterSelection."Printer Name");[/code]

The BioPDF Management Codeunit includes these functions:

Name Description
BeforeReportPrint Setup PDF printer and printer selection
AfterReportPrintGetBLOB Copy the PDF file to a tempBLOB record
AfterReportPrintGetFileName Return the PDF file name
CleanUp Clear PDF automation objects
ConfirmFileExists Return an error if file does not exist
FileExists Return a boolean value for given file name
FileRename Rename a file (copy and delete combined)
FileErase Delete a file
FileCopy Copy a file
ClearPrinterBuffer Clear the printer selection buffer
CreatePrinterBuffer Adds a report and a printer name to the printer selection buffer
PrinterBufferExists Check if a printer selection buffer exists for given report
GetPrinterName Get the printer name from the printer selection buffer
SaveLastPrinter Save the name of the last printer used
GetLastPrinter Get the name of the last printer used

BioPDF Management Codeunit (2009 R2)

BioPDF Management Codeunit (5 SP1)

Posted on

Using Common Dialog Cancel button

In the default Codeunit 412 is a function that opens a open or a save dialog.  The problem with this function is that we do not know if the open, save or cancel button was pressed.  I used the code
[code htmlscript=”false”]FileName := CommonDialogMgt.OpenFile(”,FileName,1,CommonDialogMgt.GetFilterString(1),1);[/code]
where CommondialogMgt is Codeunit 412 to ask the user where to save the file.  If I passed a default file name to this function and the user simply approved the suggestion I would get the same result as if the user pressed cancel.  I wanted to change this and added a few lines to Codeunit 412.

I added a global function SetProperties and GetFileName and a local function OpenFileWithError.
[code htmlscript=”false”]SetProperties(WindowTitle : Text[50];DefaultFileName : Text[1024];DefaultFileType : ‘ ,Text,Excel,Word,Custom,Xml,Htm,Xsd,Xslt’;FilterS
GlobalWindowTitle := WindowTitle;
GlobalDefaultFileName := DefaultFileName;
GlobalDefaultFileType := DefaultFileType;
GlobalFilterString := FilterString;
GlobalAction := Action;

GetFileName() : Text[1024]
EXIT(GlobalFileName);

OpenFileWithError(WindowTitle : Text[50];DefaultFileName : Text[1024];DefaultFileType : ‘ ,Text,Excel,Word,Custom,Xml,Htm,Xsd,Xslt’;Fil
IF DefaultFileType = DefaultFileType::Custom THEN BEGIN
GetDefaultFileType(DefaultFileName,DefaultFileType);
Filter := FilterString;
END ELSE
Filter := GetFilterString(DefaultFileType);

CommonDialogControl.MaxFileSize := 2048;
CommonDialogControl.FileName := DefaultFileName;
CommonDialogControl.DialogTitle := WindowTitle;
CommonDialogControl.Filter := Filter;
CommonDialogControl.InitDir := DefaultFileName;
CommonDialogControl.CancelError := TRUE;

IF Action = Action::Open THEN
CommonDialogControl.ShowOpen
ELSE
CommonDialogControl.ShowSave;

EXIT(CommonDialogControl.FileName);[/code]
I added a few global variables and a code to the onRun trigger
[code htmlscript=”false”]GlobalFileName :=
OpenFileWithError(GlobalWindowTitle,GlobalDefaultFileName,GlobalDefaultFileType,GlobalFilterString,GlobalAction);[/code]This means that I have to change the code
[code htmlscript=”false”]FileName := CommonDialogMgt.OpenFile(”,FileName,1,CommonDialogMgt.GetFilterString(1),1);[/code]to
[code htmlscript=”false”]CommonDialogMgt.SetProperties(”,FileName,1,CommonDialogMgt.GetFilterString(1),1);
IF CommonDialogMgt.RUN THEN
FileName := CommonDialogMgt.GetFileName
ELSE
FileName := ”;[/code]
and I will get an empty file name if cancel is pressed.  Attached is the NAVW16.00.01 Codeunit for NAV 2009 R2.

Codeunit 412

Posted on

Creating an Excel Pivot table from a ledger table

I just finished creating a report that can export job ledger entries to Excel and build a Pivot Table for analysis of the data.  Global dimensions and data from the master and supplement tables are included in the export.  A similar report can be built for other ledger tables.

The first step is to create a workbook.

Fields for rows and columns are mandatory.  They can be selected from a field list.

The user can filter the job ledger entry table before exporting.  A text file will be created and opened by Excel.  I use a system object to create the text file in the system code page.  This will result in a simple pivot table in Excel.

The Excel workbook will include three sheets.  One for the pivot table, one for filter information and one for the data.  The user can now customize the pivot table to display what ever is needed and then save the workbook to a secure location.  When saving a dialog similar to the one below will appear.  Here the uses selects No and saves the workbook in an Excel format.

Then the user can update this workbook with new data.  When updating a workbook the previous data is completely replaced.  Run the same report and change the option to Update Workbook.  Select the Excel Document and the sheet names will be automatically applied with standard names.

This will give the user a fresh data and a updates pivot table that can be saved to a different file name if needed.  Then code is somewhat based on the solution from Microsoft that exports Account Schedules to pivot table and I use four basic functions as shown below.
[code htmlscript=”false”]CreateExcel()
CREATE(xlApp,TRUE,TRUE);

CreateWorkbook()
WITH "Job Ledger Entry" DO BEGIN
xlApp.Workbooks.OpenText(TextFileName);
xlWorkSheet := xlApp.ActiveSheet;
xlSheetName := GetDataSheetName;
xlWorkSheet.Name := xlSheetName;

xlApp.ActiveWorkbook.Sheets.Add();
xlWorkSheet3 := xlApp.ActiveSheet;
xlWorkSheet3.Name := GetInfoSheetName;
xlWorkSheet3.Range(‘A1’).Value := FORMAT(Text009);
xlWorkSheet3.Range(‘B1’).Value := TABLECAPTION;

IF GETFILTER("Work Type Code") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A2’).Value := FIELDCAPTION("Work Type Code") + Text010;
xlWorkSheet3.Range(‘B2’).Value := GETFILTER("Work Type Code");
END;
IF GETFILTER("Entry Type") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A3’).Value := FIELDCAPTION("Entry Type") + Text010;
xlWorkSheet3.Range(‘B3’).Value := GETFILTER("Entry Type");
END;
IF GETFILTER("Posting Date") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A4’).Value := FIELDCAPTION("Posting Date") + Text010;
xlWorkSheet3.Range(‘B4’).Value := GETFILTER("Posting Date");
END;
IF GETFILTER("Global Dimension 1 Code") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A5’).Value := FIELDCAPTION("Global Dimension 1 Code") + Text010;
xlWorkSheet3.Range(‘B5’).Value := GETFILTER("Global Dimension 1 Code");
END;
IF GETFILTER("Global Dimension 2 Code") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A6’).Value := FIELDCAPTION("Global Dimension 2 Code") + Text010;
xlWorkSheet3.Range(‘B6’).Value := GETFILTER("Global Dimension 2 Code");
END;
IF GETFILTER("Job No.") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A7’).Value := FIELDCAPTION("Job No.") + Text010;
xlWorkSheet3.Range(‘B7’).Value := GETFILTER("Job No.");
END;
IF GETFILTER("Job Task No.") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A8’).Value := FIELDCAPTION("Job Task No.") + Text010;
xlWorkSheet3.Range(‘B8’).Value := GETFILTER("Job Task No.");
END;
IF GETFILTER("Job Posting Group") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A9’).Value := FIELDCAPTION("Job Posting Group") + Text010;
xlWorkSheet3.Range(‘B9’).Value := GETFILTER("Job Posting Group");
END;
xlWorkSheet3.Range(‘A1:B9’).Columns.AutoFit;

IF NoOfRows &gt; 0 THEN BEGIN
ExcelBuffer.VALIDATE("Column No.",NoOfColumns);
PivotTableRange := STRSUBSTNO(‘%1!$A:$%2′,xlSheetName, ExcelBuffer.xlColID);
xlWorkSheet.Range(PivotTableRange).Columns.AutoFit;
xlPivotCache := xlApp.ActiveWorkbook.PivotCaches.Add(1,PivotTableRange);
xlPivotCache.CreatePivotTable(”,’PivotTable1’);

xlWorkSheet2 := xlApp.ActiveSheet();
xlPivotTable := xlWorkSheet2.PivotTables(‘PivotTable1′);
xlWorkSheet2.Name := GetPivotSheetName;

xlPivotField := xlPivotTable.PivotFields(RowFieldName);
xlPivotField.Orientation := 1; //xlRowField
xlPivotField.Position := 1;

xlPivotField := xlPivotTable.PivotFields(ColFieldName);
xlPivotField.Orientation := 2; //xlColumnField
xlPivotField.Position := 1;

xlPivotField := xlPivotTable.PivotFields(FIELDCAPTION("Total Price (LCY)"));
xlPivotField.Orientation := 4; //xlDataField
xlPivotField.Position := 1;
xlPivotField."Function" := 0; //sum
xlPivotField.Caption := STRSUBSTNO(Text026,FIELDCAPTION("Total Price (LCY)"));

FormatString := DELCHR(FORMAT(ROUND(1000.01,GLSetup."Unit-Amount Rounding Precision"),0),’ ” THEN BEGIN
xlWorkSheet3.Range(‘A2’).Value := FIELDCAPTION("Work Type Code") + Text010;
xlWorkSheet3.Range(‘B2’).Value := GETFILTER("Work Type Code");
END;
IF GETFILTER("Entry Type") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A3’).Value := FIELDCAPTION("Entry Type") + Text010;
xlWorkSheet3.Range(‘B3’).Value := GETFILTER("Entry Type");
END;
IF GETFILTER("Posting Date") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A4’).Value := FIELDCAPTION("Posting Date") + Text010;
xlWorkSheet3.Range(‘B4’).Value := GETFILTER("Posting Date");
END;
IF GETFILTER("Global Dimension 1 Code") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A5’).Value := FIELDCAPTION("Global Dimension 1 Code") + Text010;
xlWorkSheet3.Range(‘B5’).Value := GETFILTER("Global Dimension 1 Code");
END;
IF GETFILTER("Global Dimension 2 Code") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A6’).Value := FIELDCAPTION("Global Dimension 2 Code") + Text010;
xlWorkSheet3.Range(‘B6’).Value := GETFILTER("Global Dimension 2 Code");
END;
IF GETFILTER("Job No.") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A7’).Value := FIELDCAPTION("Job No.") + Text010;
xlWorkSheet3.Range(‘B7’).Value := GETFILTER("Job No.");
END;
IF GETFILTER("Job Task No.") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A8’).Value := FIELDCAPTION("Job Task No.") + Text010;
xlWorkSheet3.Range(‘B8’).Value := GETFILTER("Job Task No.");
END;
IF GETFILTER("Job Posting Group") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A9’).Value := FIELDCAPTION("Job Posting Group") + Text010;
xlWorkSheet3.Range(‘B9’).Value := GETFILTER("Job Posting Group");
END;
xlWorkSheet3.Range(‘A1:B9’).Columns.AutoFit;

END;

xlRange := xlWorkSheet.Range(‘A1’).SpecialCells(11);
ExcelBuffer.VALIDATE("Column No.",xlRange.Column);
xlWorkSheet.Range(STRSUBSTNO(‘A1:%1%2’,ExcelBuffer.xlColID,xlRange.Row)).ClearContents;
xlRange := xlWorkSheet.Range(‘$A$1’);
xlQueryTables := xlWorkSheet.QueryTables;
xlQueryTable := xlQueryTables.Add(STRSUBSTNO(‘TEXT;%1’,TextFileName),xlRange);
xlQueryTable.Name := Text025;
xlQueryTable.FieldNames := TRUE;
xlQueryTable.RowNumbers := FALSE;
xlQueryTable.FillAdjacentFormulas := FALSE;
xlQueryTable.RefreshOnFileOpen := FALSE;
xlQueryTable.RefreshStyle := 1; //xlInsertDeleteCells
xlQueryTable.SavePassword := FALSE;
xlQueryTable.SaveData := FALSE;
xlQueryTable.AdjustColumnWidth := TRUE;
xlQueryTable.RefreshPeriod := 0;
xlQueryTable.TextFilePromptOnRefresh := FALSE;
xlQueryTable.TextFilePlatform := 1252;
xlQueryTable.TextFileStartRow := 1;
xlQueryTable.TextFileParseType := 1; //xlDelimited
xlQueryTable.TextFileTextQualifier := 1; //xlTextQualifierDoubleQuote
xlQueryTable.TextFileConsecutiveDelimiter := FALSE;
xlQueryTable.TextFileTabDelimiter := TRUE;
xlQueryTable.TextFileSemicolonDelimiter := FALSE;
xlQueryTable.TextFileCommaDelimiter := FALSE;
xlQueryTable.TextFileSpaceDelimiter := FALSE;
xlQueryTable.TextFileTrailingMinusNumbers := TRUE;
xlQueryTable.Refresh(FALSE);

xlWorkSheet2.Select;
xlPivotTable := xlWorkSheet2.PivotTables(‘PivotTable1’);
xlPivotTable.RefreshTable;

ShowExcel()
xlApp.Visible := TRUE;[/code]
And after creating the text file these functions are executed.
[code htmlscript=”false”]CreateExcel;
IF ExcelOption = ExcelOption::"Create Workbook" THEN
CreateWorkbook
ELSE
UpdateWorkbook;
ShowExcel;[/code]
If you, your company or your client would like to have this solution for other ledger tables just contact me.

The zipped attachment below is encrypted, please look at the product page for more information.

Job Pivot Report

 

Posted on

NAV 2013 and ClickOnce Install

I have installed NAV 2013 on a Windows 2008 Server.  Everything is working fine and it is time to create the OneClick installation source.  I followed the documentation from Microsoft.  Make sure that when you install NAV 2013 to select ClickOnce Installer Tools.

I first found that the SDK needed for the mage.exe command is not available on the server.  I downloaded and installed Microsoft Windows SDK for Windows 7 and .NET Framework 4 from Microsoft.

I created the folder C:\inetpub\wwwroot\NAV2013Beta as the source for deployment and the folder C:\inetpub\wwwroot\NAV2013Beta\Deployment\ApplicationFiles for the application.  I created a read only share for the folder NAV2013Beta.  I copied everything from the folder C:\Program Files (x86)\Microsoft Dynamics NAV\70\RoleTailored Client to this folder.

I found the folder C:\ProgramData\Microsoft\Microsoft Dynamics NAV\70 and copied the ClientUserSettings.config file to my C:\inetpub\wwwroot\NAV2013Beta\Deployment\ApplicationFiles folder.  First I made sure that the server was not set to localhost but to the network server name.

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <appSettings>
    <!--
      Name of the machine hosting the Microsoft Dynamics Nav Server to
      be connected to.
    -->
    <add key="Server" value="nav2013.dynamics.is"/>

I found the folder C:\Program Files (x86)\Microsoft Dynamics NAV\70\ClickOnce Installer Tools\TemplateFiles and copied all files to my C:\inetpub\wwwroot\NAV2013Beta folder, selecting to merge the Deployment folder.

Next I opened Command Prompt and typed

set PATH=%PATH%;C:\Program Files\Microsoft SDKs\Windows\v7.1\Bin\NETFX 4.0 Tools;C:\Program Files\Microsoft SDKs\Windows\v7.1\Bin
cd "\inetpub\wwwroot\NAV2013Beta\Deployment\ApplicationFiles"
mage.exe -Update Microsoft.Dynamics.Nav.Client.exe.manifest -FromDirectory .\

I got the following errors

Warning MSB3112: Two or more assemblies have the same identity 'Microsoft.Dynamics.Nav.OpenXml, Version=7.0.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35, ProcessorArchitecture=msil'.
Warning MSB3182: File name 'C:\inetpub\wwwroot\NAV2013Beta\Deployment\ApplicationFiles\Add-ins\CodeViewer\EditorComponents\Microsoft.VisualStudio.UI.Text.Wpf.KeyProcessor.Implementation.dll' exceeds 100 characters.

Microsoft.Dynamics.Nav.Client.exe.manifest successfully updated. However, some errors were encountered.

Decided to remove the Development Client and only install the Role Tailored Client and restarted the process and got a better result.

Microsoft.Dynamics.Nav.Client.exe.manifest successfully updated

Continuing in the command prompt

cd ..
mage.exe -Update Microsoft.Dynamics.Nav.Client.application -AppManifest \\nav2013\NAV2013Beta\Deployment\ApplicationFiles\Microsoft.Dynamics.Nav.Client.exe.manifest -AppCodeBase \\nav2013\nav2013beta\Deployment\ApplicationFiles\Microsoft.Dynamics.Nav.Client.exe.manifest
mage.exe

I change the Name to identify the installation

I changed the description

and I needed to change the Start Location

Again, I saved the file selecting not to sign it.

In my \\nav2013\NAV2013Beta folder I used Word to create the Software License Terms for my company and saved is as Rich Text Format as PartnerSolutionLicense.rtf.

Then I opened the NAVClientInstallation.html file in Notepad and removed the TODO both for the text and for the link.

Next I tried the installation by browsing to the folder \\nav2013\NAV2013Beta on a Windows 2008 Remote Desktop Server and double clicking on NAVClientInstallation.html file.  After a few click I had my NAV 2013 Beta up and running.