PO line Upload

                        Creating a New Item in Item Master Table by Importing from an Excel File

The following code written in job explains us, how to create an item for a PO/SO/TO....etc in the item master table by importing from an excel file.

when an item is created the following tables should be affected,

1. EcoResDistinctProduct

2.EcoResProductIdentifier

3.EcoResStorageDimensionGroupProduct

4.EcoResTrackingDimensionGroupProduct

5.InventTable

6.InventTableModule

7.InventItemSetupSupplyType

8.EcoResStorageDimensionGroupItem

9.EcoResTrackingDimensionGroupItem

10.InventModelGroupItem

11.InventItemGroupItem

Before writing the code , create a an excel file containing the required records which we need to import.

Code::

static void RB_ReadExcel78(Args _args)

{

SysExcelApplication application;

SysExcelWorkbooks workbooks;

SysExcelWorkbook workbook;

SysExcelWorksheets worksheets;

SysExcelWorksheet worksheet;

SysExcelCells cells;

COMVariantType type;

FilenameOpen filename;

EcoResDistinctProduct ecoResDistinctProduct;

EcoResProductIdentifier ecoResProductIdentifier;

EcoResStorageDimensionGroupProduct ecoResStorageDimensionGroupProduct;

EcoResTrackingDimensionGroupProduct ecoResTrackingDimensionGroupProduct;

InventTable inventTable;

InventTableModule inventTableModule;

InventItemSetupSupplyType inventItemSetupSupplyType;

EcoResStorageDimensionGroupItem ecoResStorageDimensionGroupItem;

EcoResTrackingDimensionGroupItem ecoResTrackingDimensionGroupItem;

InventModelGroupItem inventModelGroupItem;

InventItemGroupItem inventItemGroupItem;

int row ;

str DisplayProductNumber;

str SearchName;

str StorageDimensionGroup;

str TrackingDimensionGroup;

str ModelGroupId;

str ItemGroupId;
str ProductType;

;

application = SysExcelApplication::construct();

workbooks = application.workbooks();

filename = "C:\\Users\\charlie\\Desktop\\NewImport.xlsx"; // file path

try

{

workbooks.open(filename);

}

catch (Exception::Error)

{

throw error("File not found");

}

workbook = workbooks.item(1);

worksheets = workbook.worksheets();

worksheet = worksheets.itemFromNum(1);

cells = worksheet.cells();

//Iterate through cells and get the values

do

{

//Incrementing the row line to next Row

row++;

DisplayProductNumber =cells.item(row,1).value().bStr();

SearchName =cells.item(row,2).value().bStr();

ModelGroupId =cells.item(row,3).value().bStr();

ItemGroupId =cells.item(row,4).value().bStr();

StorageDimensionGroup =cells.item(row,5).value().bStr();

TrackingDimensionGroup =cells.item(row,6).value().bStr();
ProductType =cells.item(row,7).value().bStr();

info(DisplayProductNumber);

info(SearchName);

info(ModelGroupId);

info(ItemGroupId);

info(StorageDimensionGroup);

info(TrackingDimensionGroup);

try

{

//Product

ecoResDistinctProduct.clear();

ecoResDistinctProduct.initValue();

ecoResDistinctProduct.ProductType =ProductType;

ecoResDistinctProduct.DisplayProductNumber = DisplayProductNumber;

ecoResDistinctProduct.SearchName = SearchName;

if (ecoResDistinctProduct.validateWrite())

{

ecoResDistinctProduct.insert();

ecoResProductIdentifier.clear();

ecoResProductIdentifier.initValue();

ecoResProductIdentifier.ProductNumber = DisplayProductNumber;

ecoResProductIdentifier.Product = ecoResDistinctProduct.RecId;

ecoResProductIdentifier.insert();

//Storage dimension group

ecoResStorageDimensionGroupProduct.clear();

ecoResStorageDimensionGroupProduct.initValue();

ecoResStorageDimensionGroupProduct.Product = ecoResDistinctProduct.RecId;

ecoResStorageDimensionGroupProduct.StorageDimensionGroup = EcoResStorageDimensionGroup::findByDimensionGroupName(StorageDimensionGroup).RecId;

//if (ecoResStorageDimensionGroupProduct.validateWrite())

//{

ecoResStorageDimensionGroupProduct.insert();

//}

//Tracking dimension group

ecoResTrackingDimensionGroupProduct.clear();

ecoResTrackingDimensionGroupProduct.initValue();

ecoResTrackingDimensionGroupProduct.Product = ecoResDistinctProduct.RecId;

ecoResTrackingDimensionGroupProduct.TrackingDimensionGroup = EcoResTrackingDimensionGroup::findByDimensionGroupName(TrackingDimensionGroup).RecId;

ecoResTrackingDimensionGroupProduct.insert();

EcoResProductTranslation::createOrUpdateTranslation(ecoResDistinctProduct.RecId, "Allexy","Allexyy");

//Released product

inventTable.clear();

inventTable.initValue();

inventTable.initFromEcoResProduct(ecoResDistinctProduct);

inventTable.ItemId = DisplayProductNumber;

inventTable.NameAlias = SearchName;

if (inventTable.validateWrite())

{

inventTable.insert();

//Inventory model group

inventModelGroupItem.clear();

inventModelGroupItem.initValue();

inventModelGroupItem.ItemDataAreaId = inventTable.dataAreaId;

inventModelGroupItem.ItemId = inventTable.ItemId;

inventModelGroupItem.ModelGroupId = ModelGroupId;

inventModelGroupItem.ModelGroupDataAreaId = curext();

inventModelGroupItem.insert();

//Item group

inventItemGroupItem.clear();

inventItemGroupItem.initValue();

inventItemGroupItem.ItemDataAreaId = inventTable.dataAreaId;

inventItemGroupItem.ItemId = inventTable.ItemId;

inventItemGroupItem.ItemGroupId = ItemGroupId;

inventItemGroupItem.ItemGroupDataAreaId = curext();

inventItemGroupItem.insert();

//Extended product details – Inventory

inventTableModule.clear();

inventTableModule.initValue();

inventTableModule.ItemId = inventTable.ItemId;

inventTableModule.ModuleType = ModuleInventPurchSales::Invent;

inventTableModule.insert();

//Extended product details – Purchase

inventTableModule.clear();

inventTableModule.initValue();

inventTableModule.ItemId = inventTable.ItemId;

inventTableModule.ModuleType = ModuleInventPurchSales::Purch;

inventTableModule.insert();

//Extended product details – Sales

inventTableModule.clear();

inventTableModule.initValue();

inventTableModule.ItemId = inventTable.ItemId;

inventTableModule.ModuleType = ModuleInventPurchSales::Sales;

inventTableModule.insert();

//Warehouse items

InventItemLocation::createDefault(inventTable.ItemId);

//Supply type setup

inventItemSetupSupplyType.clear();

inventItemSetupSupplyType.initValue();

inventItemSetupSupplyType.ItemId = inventTable.ItemId;

inventItemSetupSupplyType.ItemDataAreaId = inventTable.DataAreaId;

inventItemSetupSupplyType.insert();

//Product storage dimension group

ecoResStorageDimensionGroupProduct = EcoResStorageDimensionGroupProduct::findByProduct(ecoResDistinctProduct.RecId);

if (ecoResStorageDimensionGroupProduct.RecId)

{

ecoResStorageDimensionGroupItem.clear();

ecoResStorageDimensionGroupItem.initValue();

ecoResStorageDimensionGroupItem.ItemDataAreaId = inventTable.DataAreaId;

ecoResStorageDimensionGroupItem.ItemId = inventTable.ItemId;

ecoResStorageDimensionGroupItem.StorageDimensionGroup = ecoResStorageDimensionGroupProduct.StorageDimensionGroup;

ecoResStorageDimensionGroupItem.insert();

}

//Product tracking dimension group

ecoResTrackingDimensionGroupProduct = EcoResTrackingDimensionGroupProduct::findByProduct(ecoResDistinctProduct.RecId);

if (ecoResTrackingDimensionGroupProduct.RecId)

{

ecoResTrackingDimensionGroupItem.clear();

ecoResTrackingDimensionGroupItem.initValue();

ecoResTrackingDimensionGroupItem.ItemDataAreaId = inventTable.DataAreaId;

ecoResTrackingDimensionGroupItem.ItemId = inventTable.ItemId;

ecoResTrackingDimensionGroupItem.TrackingDimensionGroup = ecoResTrackingDimensionGroupProduct.TrackingDimensionGroup;

ecoResTrackingDimensionGroupItem.insert();

}

}

}

}

catch

{

error("Error!");

return;

}

info("Done!");

type = cells.item(row+1, 1).value().variantType();

}

while (type != COMVariantType::VT_EMPTY);

// quits the application

application.quit();

}

Compile and run the code .......................................

the code contain two logics

1.Importing from excel to ax

2.Creating new PO line using the imported data

Happy DAXing......................,,,,

Retrieving The Online Application Users

Retrieving The Online Application Users

Using the following code we can retrieve the online application users list

static void onlineusers(Args _args)

{

sysclientsessions s;

;

while select s

where s.Status == NoYes::Yes

{

info(s.userId);

}

}

Compile and run the code,,,,,,,,,,,,,,,,,,,

IP Address Retrieval

                                     Retrieving The current IP addresses

The folliowing code explains how to retrieve the IP adddesses that used to acces the network

static void retrieveip(Args _args)

{

System.String hostName = System.Net.Dns::GetHostName();

System.Net.IPHostEntry hostEntry = System.Net.Dns::GetHostEntry(hostName);

System.Net.IPAddress[] addresses = hostEntry.get_AddressList();

System.Net.IPAddress address;

System.Net.Sockets.AddressFamily addressFamily;

System.Collections.IEnumerator enumerator = addresses.GetEnumerator();

while (enumerator.MoveNext())

{

address = enumerator.get_Current();

addressFamily = address.get_AddressFamily();

if (addressFamily == System.Net.Sockets.AddressFamily::InterNetwork)

{

info(address.ToString());

}

}

}

compile and run the code ,,,,,,,,,,,,,,,,,

Getting The Screen Resolution in AX

                                               Getting The Screen Resolution in AX

To get the screen Resolution we can write a job in AX,,,,,,,,,,,,,,,,,,,,,

static void ResolnMetrics(Args _args)

{

#WinAPI

int screenWidth, screenHeight;

;

screenWidth = WinAPI::getSystemMetrics(#SM_CXSCREEN);

screenHeight = WinAPI::getSystemMetrics(#SM_CYSCREEN);

info(strfmt("Screen resolution: %1 x %2",screenWidth, screenHeight));

}

Run the job and see............................!!!!!!!!!!!!

The Screen resolution is  1280*720                                                                                                           

CURRENCY CONVERSION IN AX 2012

                                           CONVERSION OF CURRENCY

To convert the currency to standard USD |From any other Currency like EUR,INR etc......

Try the following code in a job.

static void SrchCurrncy(Args _args)

{

CurrencyExchangeHelper currencyExchangeHelper;

CurrencyCode transCurrency = 'INR'; // try with EUR also

AmountCur amountCur = 600.00;

AmountMst amountMST;

currencyExchangeHelper = CurrencyExchangeHelper::newExchangeDate(Ledger::current(), systemDateGet());

amountMST = currencyExchangeHelper.calculateTransactionToAccounting(transCurrency, amountCur ,true);

info(strFmt("%1",amountMST));

}

Now run the code and see........... 

 The  Rs600 =$11.35  only.... !!!!!!!!!!!!!!!!!!!!!!!!!!!

Creation of a Form using X++ code

                                 Creation of a Form using X++ code

To write an X++ job to create a basic form with a Tab page and inside a grid having two contols and we are taking PurchTable as Datasourse.

Job:

static void FormCreation(Args _args)

{

Form form;

FormRun formRun;

Args args;

FormBuildDesign formBuildDesign;

FormBuildControl formBuildControl;

FormBuildTabControl formBuildTabControl;

FormBuildTabPageControl formBuildTabPageControl;

FormBuildGridControl formBuildGridControl;

FormBuildDatasource formBuildDatasource;

FormBuildStringControl formString1;

;

form = new Form();

formBuildDatasource = form.addDataSource(tableStr(PurchTable));

formBuildDesign = form.addDesign("design");

formBuildTabControl = formBuildDesign.addControl(FormControlType::Tab, "Tab");

formBuildTabPageControl = formBuildTabControl.addControl(FormControlType::TabPage, "TabPage1");

formBuildGridControl = formBuildTabPageControl.addControl(FormControlType::Grid, "Grid");

formString1 = formBuildGridControl.addDataField(formBuildDatasource.id(), fieldNum(PurchTable, PurchId));

formString1.label("PurchIds");

formString1 = formBuildGridControl.addControl(FormControlType::Real,"albion");

formString1.label("RaelValue");

args = new Args();

args.object(form);

formRun = classFactory.formRunClass(args);

formRun.init();

formRun.run();

formRun.wait();

}

Run the job and see................... the following form

Retrieving The Address,State and Country Region id of a Vendor

Retrieving the address,state and country region id of purticular vendor whose “Purpose” is “Business”.

Scenario: To write a job to retrieve the address,state and cuontry region id of purticular vendor whose “Purpose” is Bussiness.

Step1

AP-->common-->vendors-->all vendors . Select a purticular vendor , select the Business address and give Edit . In the Address part you can see the State and country region id also. Now you have to write a job to fetch all these details for the selected the Business address.

Step2

write the job

static void retrievingcountry(Args _args)

{

VendTable vendTable;

LogisticsPostalAddress logisticsPostalAddress;

str aa;

str address;

str country;

str state;

vendTable = VendTable::find("JP-001");

address = DirParty::getPostalAddressByType(vendTable.Party,LogisticsLocationRoleType::Business);

while select logisticsPostalAddress

if (logisticsPostalAddress.Address == address)

{

info(logisticsPostalAddress.CountryRegionId);

info(logisticsPostalAddress.State);

info(logisticsPostalAddress.Address);

}

}

run the job