My 2p about ERP Solutions, Information Worker Solutions and other software products (mainly Microsoft Dynamics AX and Microsoft SharePoint).

30 April 2010

Links List April 2010

I decided to share all my interesting reads and resources month by month with my blog readers. You can find these posts by searching on the label Links. I'll try to order the resources in logical categories. If you would like to see some interesting stuff added in the next month, don't hesitate to post a comment.

So this is my Links post for April 2010.

Dynamics AX


Continue reading......

by Patrik Luca 0 comments

14 April 2010

Export to Excel with X++ code: the sequel

Introduction

This post is a sequel to my post Export to Excel with X++ code. In this post I add some formatting to my Excel export file.

  • autofit column width
  • working with styles to set fonts to bold
  • working with style to set font color
  • setting the format of a column

X++ code

Add following extra variables in the beginning of the job:

SysExcelStyles                  styles;
SysExcelStyle                   style;
SysExcelFont                    font;
SysExcelCells cells;

After having created the workbook, create a new style as follows:


styles = sysExcelWorkBook.styles(); 
// Create new style
style = styles.add('Header');
// Set font for this style to bold
font = style.font();
font.bold(true);
font.color(255);

Right after having created the title row on the first worksheet, set its style to our created Header style.


// Set the title row in the Header style
sysExcelWorksheet.rows().item(1).style('Header');

For each added worksheet with backorder lines, set the style of the title row also to our created Header style.


// Set the title row in the Header style
sysExcelWorksheetBackOrder.rows().item(1).style('Header');

Set the column width to autofit for the first worksheet.


// Set the column width to autofit
sysExcelWorksheet.columns().autoFit();

Set the column width to autofit for each added worksheet with backorder lines. Make sure you do this after you have added all rows in the worksheet, so it can take into account the width of the largest value in your column.


// Set the column width to autofit
sysExcelWorksheetBackOrder.columns().autoFit();

Format the fourth column (Deliver Remainder) for each added worksheet with backorder lines as a Number with two decimal places.


// Format the 4th column to a Number with 2 decimal places
cells = sysExcelWorksheetBackOrder.cells();
cells.range('D2:D99').numberFormat("0,00");

The complete job looks then like this:




static void ExportToExcel(Args _args)
{
#AviFiles
SysOperationProgress progress = new SysOperationProgress();
SysExcelApplication sysExcelApplication;
SysExcelWorkbooks sysExcelWorkBooks;
// Filename to which you will be writing your data
FileName fileName = "C:\\Windows\\Temp\\ExportToExcel.xlsx";
SysExcelWorkbook sysExcelWorkBook;
SysExcelWorkSheets sysExcelWorkSheets;
SysExcelWorkSheet sysExcelWorkSheet;
SysExcelWorkSheet sysExcelWorksheetBackOrder;
SysExcelWorksheet sysExcelWorkSheetToBeDeleted;
SysExcelStyles styles;
SysExcelStyle style;
SysExcelFont font;
SysExcelCells cells;
int row = 1;
int rowBackOrder;
CustTable custTable;
SalesTable salesTable;
SalesLine salesLine;
boolean workSheetAdded = false;
int nbrOfCustomers;
;

// Initialising progress bar
progress.setCaption("Export To Excel in progress...");
progress.setAnimation(#AviTransfer);
// Initialisation of some objects
sysExcelApplication = SysExcelApplication::construct();
// Create new workbook
sysExcelWorkBooks = sysExcelApplication.workbooks();
sysExcelWorkBook = sysExcelWorkBooks.add();
// Create new style
styles = sysExcelWorkBook.styles();
style = styles.add("Header");
// Set font for this style to bold and color to red
font = style.font();
font.bold(true);
font.color(255);
// Get worksheets collection
sysExcelWorkSheets = sysExcelWorkbook.worksheets();
// Excel visible on desktop running the job or not?
sysExcelApplication.visible(false);
// Newly created Excel files have by default some worksheets
// Delete those worksheets created by default
while(sysExcelWorkSheets.count() > 1)
{
sysExcelWorkSheetToBeDeleted = sysExcelWorkSheets.itemFromNum(2);
sysExcelWorkSheetToBeDeleted.delete();
}
// Add as many worksheets as there are customers
select count(RecId) from CustTable;
sysExcelWorkSheet = sysExcelWorkSheets.add(null,null,CustTable.RecId);
// Add another worksheet
sysExcelWorkSheet = sysExcelWorkSheets.add();
//Rename the first worksheet
sysExcelWorkSheet.name("Customers");
// Make a title row
// set a value in cell on row 1 column 1
sysExcelWorkSheet.cells().item(1,1).value("Customer account");
// set a value in cell on row 1 column 2
sysExcelWorksheet.cells().item(1,2).value("Name");
// set the title row in the Header style
sysExcelWorksheet.rows().item(1).style("Header");
while select custTable
{
progress.setText(strfmt("Customer %1", custTable.Name));
row++;
rowBackOrder = 1;
sysExcelWorksheet.cells().item(row,1).value(custTable.AccountNum);
sysExcelWorksheet.cells().item(row,2).value(custTable.Name);
while select salesLine
where salesLine.SalesStatus == salesStatus::Backorder
&& salesLine.ConfirmedDlv < Today()
&& salesLine.RemainSalesPhysical > 0
join salesTable
where salesTable.SalesId == salesLine.SalesId &&
salesTable.CustAccount == custTable.AccountNum
{
if(!workSheetAdded)
{
// Use the next Excel worksheet and rename it
sysExcelWorksheetBackOrder = sysExcelWorkSheets.itemFromNum(
row);
//Name of worksheet can have maximum 31 characters
sysExcelWorksheetBackOrder.name(substr(custTable.Name,1,31));
workSheetAdded = true;
// Make a title row
// set a value in cell on row 1 column 1
sysExcelWorksheetBackOrder.cells().item(1,1).value(
"Ship Date");
// set a value in cell on row 1 column 2
sysExcelWorksheetBackOrder.cells().item(1,2).value(
"Item Number");
// set a value in cell on row 1 column 3
sysExcelWorksheetBackOrder.cells().item(1,3).value(
"Item Name");
// set a value in cell on row 1 column 4
sysExcelWorksheetBackOrder.cells().item(1,4).value(
"Deliver Remainder");
// set the title row in the Header style
sysExcelWorksheetBackOrder.rows().item(1).style("Header");
}
rowBackOrder++;
sysExcelWorksheetBackOrder.cells().item(rowBackOrder,1).value(
salesLine.ConfirmedDlv);
sysExcelWorksheetBackOrder.cells().item(rowBackOrder,2).value(
salesLine.ItemId);
sysExcelWorksheetBackOrder.cells().item(rowBackOrder,3).value(
InventTable::find(salesLine.ItemId).ItemName);
sysExcelWorksheetBackOrder.cells().item(rowBackOrder,4).value(
salesLine.RemainSalesPhysical);
}
// Set the column width to autofit
sysExcelWorksheetBackOrder.columns().autoFit();
// Format the 4th column as Number with 2 decimal places
cells = sysExcelWorksheetBackOrder.cells();
cells.range("D2:D99").numberFormat("0,00");
workSheetAdded = false;
}
// Set the column width to autoFit
sysExcelWorksheet.columns().autoFit();
// Suppress the pop-up window:
// A file named foo already exists in this location. Do you want to replace it?
sysExcelApplication.displayAlerts(false);
// Save the Excel file
sysExcelWorkbook.saveAs(fileName);
sysExcelWorkBook.comObject().save();
sysExcelWorkBook.saved(true);
// Make sure you close the Excel application
// Especially if you run the job without showing Excel on the desktop
// (sysExcelApplication.visible(false))
sysExcelApplication.quit();

}



The result should look like this if you execute the job on the demo data in Microsoft Dynamics AX 2009 with Microsoft Excel 2007:





This is the first worksheet in my Excel workbook with the complete list of my customers in it.





This is one of the other worksheets, being the one created for customer Forest Wholesales and showing all the backorder lines for this particular customer.



Continue reading......

by Patrik Luca 21 comments

05 April 2010

Fill Utility: some remarks

The Fill Utility is a handy new tool since Dynamics AX 2009 with which you can update multiple records at the same time. However in some cases, you’ll notice it isn’t available.

This depends on the Table Group. The Fill Utility can only be used on tables belonging to the Table Group Main.

The Fill Utility can’t be used on tables belonging to the Table Groups:

  • Parameter
  • Miscellaneous
  • Group
  • Transaction
  • WorksheetHeader
  • WorksheetLine

To be able to use the Fill Utility on those tables, just change the TableGroup property temporarily.


Continue reading......

by Patrik Luca 0 comments

X++ Code Snippet: Copying records

This X++ Code Snippet post describes how you can easily copy records by running over all of the fields of the from-record. During this looping over all the fields, you can exclude some fields if needed.

X++ Code Snippet: Copying records

Create following method in a class:

static void CopyFields(Common _from, Common _to) 
{
    DictTable           t;
    DictField           f;
    int                 i;
    fieldId             id;
    FieldName           fieldname;
    ;

    t = new DictTable(_to.TableId);
    if (t)
    {
        for (i=1; i<=t.fieldCnt(); i++)
        {
            id = t.fieldCnt2Id(i);
            f  = new DictField(_to.TableId,t.fieldCnt2Id(i));
// excluding system fields (or add other fields to exclude during
// the copy action
            if (f && !f.isSystem())
            {
                _to.(id) = _from.(id);
            }
        }
    }
}


To copy for example a CustTrans to a new one, just call your class like this:



YourClass::CopyFields(custTrans, custTransTo);
custTransTo.insert();


Continue reading......

by Patrik Luca 7 comments

04 April 2010

Incrementing Date in Repeating Table

Business Requirement: Incrementing Date in Repeating Table

The business requirement of this post covers the following topic: each time the user adds a line in a Repeating Table on an InfoPath form, the Date Field of the line should increment by one day automatically.

An example is elaborated in this post with the by default installed Expense Report Sample. Each time an Expense line is added, the Date of the Expense line will be incremented with one day.

Solution

  1. Add a new Date Field to the Data Source.


  2. Set the Default Value of the Date Field in the Repeating Table equal to the value of the previously added Date Field (LastAddedDate). Make sure you uncheck Update this value when the result of the formula is recalculated.

     
  3. Add a Rule to the Repeating Table which adds a day to the LastAddedDate Field (Use the function addDays for it).


  4. Add a second Rule to the Repeating Table which sets the value of Date Field of the Repeating Table equal to the LastAddedDate Field.

     
  5. Add a Rule to the Date Field in the Repeating Table which sets the value of the LastAddedDate Field equal to the value of the Date Field in the Repeating Table.


Result: Date Field in Repeating Table gets default value

If you enter as Date 4/04/2010 for the first Expense line, the Date will be automatically set to 5/04/2010 on the second added Expense line and so on. If you enter as Date 10/4/2010 for the third added Expense line, the adding of Dates will go further from April the 10th for Expense lines added afterwards.


Continue reading......

by Patrik Luca 1 comments

Patrik Luca, Ieper, BELGIUM
Feel free to use or spread all of the content on my blog. In return, linking back to my blog would be greatly appreciated. All my posts and articles are provided "AS IS" with no warranties.

Subscribe feeds via e-mail
Subscribe in your preferred RSS reader

Subscribe feeds rss Most Read Entries

Categories

Recommended Books


Subscribe feeds rss Recent Comments

This Blog is part of the U Comment I Follow movement in blogosphere. Means the comment field of this blog is made DOFOLLOW. Spam wont be tolerated.

Blog Archive

My Blog List

Followers

Links