AX - How to import data from Excel to AX table

This example shows job for import of two columns from Excel to AX table AssetSorting.

static void c_Import_AssetSorting( Args _args )
{
    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    COMVariantType type;
    int row = 1;    
    str s1, s2;
    AssetSorting assetSorting;

    /* create excel instance */
    
    application = SysExcelApplication::construct();
    workbooks = application.workbooks();

    /* try to open excel data */
    
    startLengthyOperation();    
    try
    {
        workbooks.open( "c:\\ax\\cz_cpa.xlsx" );
    }
    catch ( Exception::Error )
    {
        endLengthyOperation();
        error( strFmt( "@CIE269", "c:\\ax\\cz_cpa.xlsx" ) );
        return;
    }
        
    workbook = workbooks.item( 1 );
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum( 1 );
    cells = worksheet.cells();

    /* go throught excel rows, insert data to table */
    
    ttsBegin;

    do
    {
      s1 = cells.item(row, 1).value().bStr();
      s2 = cells.item(row, 2).value().bStr();

      //info( s1 + " - " + s2 );

      /* TYPE is for testing, if we are at end of data */
      row++;
      type = cells.item( row, 1 ).value().variantType();

      //if ( row > 500 ) break;

      if ( !AssetSorting::Exist( s1, AssetSortValue::Sorting2 ) ) 
      {  
        assetSorting.SortCode = AssetSortValue::Sorting2;
        assetSorting.SortingId = s1;
        assetSorting.Description = s2;
        assetSorting.insert();
      }
        else info( "Exists - >" + s1 );
        
    }
    while ( type != COMVariantType::VT_EMPTY );

    ttsCommit;

    /* end excel */
    application.quit();

    info( "OK = " + int2str( row ) );
}

AX - How to insert new row based on existing row data in table

This example shows how to duplicate one row data to new row in one table (with update of some values).

static void job_duplicate_row(Args _args)
{
    EmployeeTable_RU tab1, tab2;
         
    tab1 = EmployeeTable_RU::findByEmployeeId( '32' );
    if ( !tab1 ) {
        info( "Source row not found." );
        return;
    }
            
    ttsBegin;
    
    /* copy row data from tab1 to tab2 + change two columns values */

    tab2.data( tab1 );
    tab2.EmployeeId = '463';
    tab2.HcmEmployment = 5637145327;

    if ( !tab2.validateWrite() ) throw Exception::Error;

    tab2.insert();

    ttsCommit;
    
    info( "OK" );
}

POWERSHELL - How to close all windows of specified process

This example shows script for closing all processes of "iexplore".

List of these running processes:

Get-Process iexplore

Output could be:

Handles  NPM(K)    PM(K)      WS(K) VM(M)   CPU(s)     Id ProcessName                                                                                                    
-------  ------    -----      ----- -----   ------     -- -----------                                                                                                    
    499      46    29208      58308   303     0,45  25512 iexplore                                                                                                       
    641      54    13280      31924   212     0,31  26804 iexplore   

Here is the script for killing of this running processes:

# get process (check if exists)

$process = Get-Process iexplore -ErrorAction SilentlyContinue

# kill it

if ( $process ) { 
   
    echo " -> process found..it will be killed.." 
    
    $process.Kill()
}
else { 
    echo " -> process not found.." 
}

Output:

-> process found..it will be killed..

ORACLE - How to find table name by table name substring

You can use CAT table (catalog). It shows to you all object in user profile.

select * from cat
where
table_name like 'MEN%'

Output:

TABLE_NAME                     TABLE_TYPE 
------------------------------ -----------
MENU                           TABLE      
MENU_SEQ                       SEQUENCE   
MENU_ZAL                       TABLE 

C# - How to add items to TreeView

treeView1.Nodes.Clear();
TreeNode parentNode, node;

parentNode = treeView1.Nodes.Add( "Parent item" );

node = parentNode.Nodes.Add( "Child item 1" );
node = parentNode.Nodes.Add( "Child item 2" );
node = parentNode.Nodes.Add( "Child item 3" );

treeView1.ExpandAll();

Output:

WIN OS - How check if port is blocked

This commnad will check, if port 1760 is blocked.

netstat -aon | find "1760"

Output:

TCP    127.0.0.1:17600        0.0.0.0:0              LISTENING       7944

C# - How add values to DataGridView (with source in DataTable)

DataTable table = new DataTable();

table.Columns.Add(new DataColumn( "Text", typeof( string ) ) );
table.Columns.Add(new DataColumn( "Number", typeof( double ) ) );

table.Rows.Add( new object[] { "Value 1", 1 } );
table.Rows.Add( new object[] { "Value 2", 2 } );
table.Rows.Add( new object[] { "Value 3", 3 } );

dataGridView1.DataSource = table;

Output:

AX - How check if specified configuration key is enabled

if ( isConfigurationkeyEnabled( configurationKeyNum( Retail) ) ) ...

C# - How add items to ListView with two columns

/* add two new columns */
            
listView1.Columns.Clear();
listView1.Columns.Add( "Name", 150, HorizontalAlignment.Left );
listView1.Columns.Add( "Detail", 100, HorizontalAlignment.Left );

listView1.View = View.Details;

/* enable row selection */
listView1.HideSelection = false;
listView1.FullRowSelect = true;

/* add three items */

ListViewItem lvi = new ListViewItem( "Item 1" );
listView1.Items.Add( lvi );
lvi.SubItems.Add( "Detail 1" );
lvi.Selected = true; 

lvi = new ListViewItem( "Item 2" );
listView1.Items.Add( lvi );
lvi.SubItems.Add( "Detail 2" );

lvi = new ListViewItem( "Item 3" );
listView1.Items.Add( lvi );
lvi.SubItems.Add( "Detail 3" );

listView1.Focus();

Output:

C# - How add items to ListView

listView1.View = View.List;
            
ListViewItem lvi = new ListViewItem( "Item 1" );
listView1.Items.Add( lvi );

lvi = new ListViewItem( "Item 2" );
listView1.Items.Add( lvi );

lvi = new ListViewItem( "Item 3" );
listView1.Items.Add( lvi );    

Output:

C# - How change cursor and how to load own cursor

/* set cursor for current form */        
Cursor = Cursors.WaitCursor;
      
/* ..with exception for this button */
button1.Cursor = Cursors.Default;

/* load own cursor from file; and set it for one button */ 
Cursor myCursor = new Cursor( @"c:\cursorfilename.cur" );
if ( myCursor != null ) button2.Cursor = myCursor;

DELPHI - How to load own (animated) cursor

There is a several steps:

1) Prepare cursor - here it is animated cursor in file "cursor_busy.ani".

2) Create file with .rc extension, write path to cursor to it.

CUR_ANIM 21 "c:\delphi\projects\project_name\cursors\cursor_busy.ani"

3) Add link to .rc file to project:

{$R 'myResources.res' 'myResources.rc'}

4) Define internal number for own cursor:

const
  crCursor_Busy = 115;

5) Load defined cursor to app:

const
  Screen.Cursors[ crCursor_Busy ] := LoadCursor( hInstance, 'CUR_ANIM' );

6) Now, when you use this code:

Screen.Cursor := crCursor_Busy;

..you will see this loaded (animated) cursor:

AX - How read specified note from production order (DocuRefSearch)

static void cieb_GetProdNote(Args _args)
{
    Notes notes = "";
    DocuRef docuRefTmp;
    ProdTable prodTable;
    DocuRefSearch docuRefSearchTmp;
    
    /* - find production order */
    
    prodTable = prodTable::find( "18-037399" );
    if ( ! prodTable ) return;
    
    /* - read its notes */
        
    docuRefSearchTmp = DocuRefSearch::newCommon( prodTable );
    docuRefSearchTmp.parmDocuTypeId( "Poznámka" );
    docuRefSearchTmp.init();
    
    if ( docuRefSearchTmp.next() )
    {
        docuRefTmp = docuRefSearchTmp.docuRef();
        
        notes += docuRefTmp.Notes;
    }

    /* - show result */
    
    info( notes );
}

Output:

C# - How add more items to multiline TextBox

textBox1.Text = null;

textBox1.Text += "Row 1\r\n";
textBox1.Text += "Row 2\r\n";
textBox1.Text += "Row 3"; 

Output:

C# - How to show the most easy message box

MessageBox.Show( "This is a message.." );

Output:

AX, SSRS - How to solve error "Type mismatch. Report parameter is of type String, but value is of type Int64"

This error could be shown when you deployed report from VS.

Effective solution was: redeploy SSRS report again from AX AOT tree (..or maybe deploy it from VS with CLEAN step).

C# - How to add formatted text to RichTextBox

richTextBox1.Clear();

richTextBox1.SelectionFont = new Font( "Courier New", 10, FontStyle.Bold );
richTextBox1.SelectionColor = Color.Red;
richTextBox1.SelectedText = "This ";

richTextBox1.SelectionFont = new Font( "Courier New", 15, FontStyle.Bold );
richTextBox1.SelectionColor = Color.Blue;
richTextBox1.SelectedText = "is ";

richTextBox1.SelectionFont = new Font("Courier New", 20, FontStyle.Bold | FontStyle.Italic);
richTextBox1.SelectionColor = Color.Green;
richTextBox1.SelectedText = "a text";

Output:

C# - How to add items to ListBox from DataTable

DataTable table = new DataTable();

table.Columns.Add( new DataColumn( "Text", typeof( string ) ) );
table.Columns.Add( new DataColumn( "Number", typeof( double ) ) );

table.Rows.Add( new object[] { "Value 1", 1 } );
table.Rows.Add( new object[] { "Value 2", 2 } );
table.Rows.Add( new object[] { "Value 3", 3 } );

listBox1.DisplayMember = "Text";
listBox1.DataSource = table;

Output:

C# - How to add items to ListBox

listBox1.Items.Clear();

listBox1.Items.Add( "One" );
listBox1.Items.Add( "Two" );
listBox1.Items.Add( "Three" );

, or (from array):

listBox1.Items.Clear();
   
String[] sArray = { "One", "Two", "Three" };
listBox1.Items.AddRange( sArray );

, or

listBox1.DataSource = new string[] { "One", "Two", "Three" };

Output:

WIN OS - How to restart computer from CMD

shutdown /r /t 0

For shutdown:

shutdown -s

AX - How get active country code

This country code is readed from legal entity. The example shows how to work with macro. #isoCZ is in #ISOCountryRegionCodes macro defined as "CZ".

#ISOCountryRegionCodes
static void Job(Args _args)
{
  LogisticsAddressCountryRegionISOCode isoCode = SysCountryRegionCode::countryInfo();
    
  if (isoCode == #isoCZ ) info( isoCode );
}

Output:

CZ

Note: To defined macro you can pass parameters. In macro you access to its by order -> %1, %2 ...and so on.

SQL SERVER - How to interpret empty string as NULL value

When is in varchar field saved NULL value, is situation easy:

select isnull( revision, '< null >' ) from drawing_drawing;

Output is:

----------
< null >

Different situation is when varchar field is only empty. In that case isnull() returns only empty string:

select isnull( revision, '< null >' ) from drawing_drawing;

Output is:

----------

The solution is to use inner function nullif() - ...question: which value is NULL ?

select isnull( nullif( revision, '' ), '< null >' ) from drawing_drawing;

Output is:

----------
< null >

ORACLE - How declare variable accross existing table field (%type)

Both variables are declared accross existing PEOPLE.NAME table field:

function Month_Execute( pPeriod varchar2, sErrorMessage out varchar2 ) return number
is
  sMinName people.name%type;
  sMaxName people.name%type;
begin
...

SQL SERVER - How disable updating of the table/field, UPDATE trigger

This code disable updating of the table:

create trigger [dbo].[drawing_drawing_update]
on [dbo].[drawing_drawing]
for update
as
begin
  set nocount on;

  raiserror( N'Table can`t be updated.', 16, 1 ) with nowait;
  rollback tran;
  return;
end;

Alternatively, you can protect only specified updated field(s):

create trigger [dbo].[drawing_drawing_update]
on [dbo].[drawing_drawing]
for update
as
begin
  set nocount on;

  if UPDATE( validsince )
  begin
    raiserror( N'Field can`t be updated.', 16, 1 ) with nowait;
    rollback tran;
    return;
  end;
end;

When you trying to save row in application, this message will appears:

AX - How calculate only with opened days in calendar

Use WorkCalendarSched class:

WorkCalendarSched workCalendarSched;
date dateOnlyWorking, dateAll;

workCalendarSched = new workCalendarSched();

dateOnlyWorking = 
  workCalendarSched.schedDate( SchedDirection::Backward, today(), 6, NoYes::Yes, "5x24" );
dateAll = 
  workCalendarSched.schedDate( SchedDirection::Backward, today(), 6, NoYes::No, "5x24" );

info( date2str( dateOnlyWorking, 321, DateDay::Digits2, DateSeparator::Hyphen, 
      DateMonth::Digits2, DateSeparator::Hyphen, DateYear::Digits4 ) );
info( date2str( dateAll, 321, DateDay::Digits2, DateSeparator::Hyphen, 
      DateMonth::Digits2, DateSeparator::Hyphen, DateYear::Digits4 ) );

Output: