Below is the sql get enum id and enum value of a enum in D365 F&O using sql.
SELECT enumidtable.NAME 'Enum Name',
enumvaluetable.enumvalue 'Enum Id',
enumvaluetable.NAME 'Enum Label'
FROM enumidtable enumIdTable
INNER JOIN enumvaluetable enumValueTable
ON enumidtable.id = enumvaluetable.enumid
WHERE enumidtable.NAME = 'BomWHSReleasePolicy'
Result of above SQL
Exception handling is important when we write code. Below is the sample code to catch exception when code is running.
class TryCatchTest
{
SysInfologEnumerator enumerator;
SysInfologMessageStruct msgStruct;
Exception exception;
Counter i = 0;
str errorGlobal;
public void createCustomer()
{
CustTable custTable;
try
{
custTable.AccountNum = "Account1";
custTable.insert();
}
catch
{
++i;
enumerator = SysInfologEnumerator::newData(infolog.cut(i));
while (enumerator.moveNext())
{
msgStruct = new SysInfologMessageStruct(enumerator.currentMessage());
exception = enumerator.currentException();
errorGlobal += msgStruct.message();
}
}
}
}
If you want to select/insert/update/delete data from specific company or legal entity, then changecompany keyword we can use. Below is sample code.
public str ChangeCompanyTest()
{
changecompany('ART') // ART is the legal entity name
{
//Your X++ Code
}
}
If you need to execute sql query inside your X++ code then below example can help you. To pass value to SQL query we need to use strfmt() function.
str sql;
Connection conn;
SqlStatementExecutePermission permission;
;
sql= strFmt("update ProdBOM set InventDimId = '%1' where RecId = %2", inventDim.inventDimId,ProdBOM.RecId);
permission = new SqlStatementExecutePermission(sql);
conn = new Connection();
permission = new SqlStatementExecutePermission(sql);
permission.assert();
conn.createStatement().executeUpdate(sql);
CodeAccessPermission::revertAssert();
If you want to get Product Master Size,Color,Style, ConfigID and thier names then below three SQL queries can help you get data quickly from D365FO compared to X++ development. Normally the volume of data is high when we try to export these types of data.
1) Product Master Size
SELECT ecoresproduct.recid [PRODUCTMASTERNUMBER],
ecoressize.NAME [PRODUCTSIZEID],
isnull(ecoresproducttranslation.NAME,'') [PRODUCTNAME],
inventitemgroupitem.itemgroupid [ITEMGROUP],
''''+ inventtable.itemid [ITEMID],
isnull(translate.NAME,'') [SIZENAME]
FROM ecoresproductmasterdimensionvalue EcoResProductMasterDimensionValue
INNER JOIN ecoresproduct ecoResProduct
ON ecoresproduct.recid =
ecoresproductmasterdimensionvalue.sizeproductmaster
LEFT OUTER JOIN ecoresproducttranslation
ON ecoresproducttranslation.product = ecoresproduct.recid
LEFT JOIN inventtable
ON inventtable.product = ecoresproduct.recid
LEFT JOIN inventitemgroupitem
ON inventitemgroupitem.itemid = inventtable.itemid
AND inventitemgroupitem.itemdataareaid = inventtable.dataareaid
INNER JOIN ecoressize ecoressize
ON ecoressize.recid = ecoresproductmasterdimensionvalue.size_
LEFT JOIN ecoresproductmasterdimvaluetranslation translate
ON translate.productmasterdimensionvalue =
ecoresproductmasterdimensionvalue.recid
WHERE inventtable.DataAreaId ='@DataAreaID'
2) Product Master Color
SELECT ecoresproduct.recid [PRODUCTMASTERNUMBER],
ecorescolor.NAME [PRODUCTCOLORID],
isnull(ecoresproducttranslation.NAME,'') [PRODUCTNAME],
inventitemgroupitem.itemgroupid [ITEMGROUP],
''''+ inventtable.itemid [ITEMID],
isnull(translate.NAME,'') [COLORNAME]
FROM ecoresproductmasterdimensionvalue EcoResProductMasterDimensionValue
INNER JOIN ecoresproduct ecoResProduct
ON ecoresproduct.recid =
ecoresproductmasterdimensionvalue.colorproductmaster
LEFT OUTER JOIN ecoresproducttranslation
ON ecoresproducttranslation.product = ecoresproduct.recid
LEFT JOIN inventtable
ON inventtable.product = ecoresproduct.recid
LEFT JOIN inventitemgroupitem
ON inventitemgroupitem.itemid = inventtable.itemid
AND inventitemgroupitem.itemdataareaid = inventtable.dataareaid
INNER JOIN ecorescolor ecoResColor
ON ecorescolor.recid = ecoresproductmasterdimensionvalue.color
LEFT JOIN ecoresproductmasterdimvaluetranslation translate
ON translate.productmasterdimensionvalue =
ecoresproductmasterdimensionvalue.recid
WHERE inventtable.DataAreaId ='@DataAreaID'
3) Product Master Style
SELECT ecoresproduct.recid [PRODUCTMASTERNUMBER],
ecoresstyle.NAME [PRODUCTSTYLEID],
ecoresproducttranslation.NAME [PRODUCTNAME],
inventitemgroupitem.itemgroupid [ITEMGROUP],
''''+ inventtable.itemid [ITEMID],
isnull(translate.NAME,'') [STYLENAME]
FROM ecoresproductmasterdimensionvalue EcoResProductMasterDimensionValue
INNER JOIN ecoresproduct ecoResProduct
ON ecoresproduct.recid =
ecoresproductmasterdimensionvalue.styleproductmaster
LEFT OUTER JOIN ecoresproducttranslation
ON ecoresproducttranslation.product = ecoresproduct.recid
LEFT JOIN inventtable
ON inventtable.product = ecoresproduct.recid
LEFT JOIN inventitemgroupitem
ON inventitemgroupitem.itemid = inventtable.itemid
AND inventitemgroupitem.itemdataareaid = inventtable.dataareaid
INNER JOIN ecoresstyle ecoresstyle
ON ecoresstyle.recid = ecoresproductmasterdimensionvalue.STYLE
LEFT JOIN ecoresproductmasterdimvaluetranslation translate
ON translate.productmasterdimensionvalue =
ecoresproductmasterdimensionvalue.recid
WHERE inventtable.DataAreaId ='@DataAreaID'
4) Product Master Config
SELECT ecoresproduct.recid [PRODUCTMASTERNUMBER],
ecoresconfiguration.NAME [PRODUCTCONFIGURATIONID],
ISNULL(ecoresproducttranslation.NAME,'') [PRODUCTNAME],
inventitemgroupitem.itemgroupid [ITEMGROUP],
''''+ inventtable.itemid [ITEMID],
isnull(translate.NAME,'') [CONFIGNAME]
FROM ecoresproductmasterdimensionvalue EcoResProductMasterDimensionValue
INNER JOIN ecoresproduct ecoResProduct
ON ecoresproduct.recid =
ecoresproductmasterdimensionvalue.CONFIGPRODUCTMASTER
LEFT OUTER JOIN ecoresproducttranslation
ON ecoresproducttranslation.product = ecoresproduct.recid
LEFT JOIN inventtable
ON inventtable.product = ecoresproduct.recid
LEFT JOIN inventitemgroupitem
ON inventitemgroupitem.itemid = inventtable.itemid
AND inventitemgroupitem.itemdataareaid = inventtable.dataareaid
INNER JOIN ecoresconfiguration ecoresconfiguration
ON ecoresconfiguration.recid = ecoresproductmasterdimensionvalue.configuration
LEFT JOIN ecoresproductmasterdimvaluetranslation translate
ON translate.productmasterdimensionvalue =
ecoresproductmasterdimensionvalue.recid
WHERE inventtable.DataAreaId ='@DataAreaID'
If you want get released product variants of AX 2012 using SQL, then SQL script can help you. Here I am fetching Item Number, Product Variant Number, Style,Color, Size, Product Name.
Please notice the join condition of InventDimCombination with EcoResProduct table. I wrote this query while doing data migration from AX 2012 R3 to D365F&O.
SELECT inventdimcombination.itemid [ITEMNUMBER],
ecoresproduct.displayproductnumber [PRODUCTVARIANTNUMBER],
inventdim.inventstyleid [PRODUCTSTYLEID],
inventdim.inventcolorid [PRODUCTCOLORID],
inventdim.inventsizeid [PRODUCTSIZEID],
inventdim.configid [PRODUCTCONFIGURATIONID],
Isnull(ecoresproducttranslation.NAME, '') [PRODUCTNAME],
inventitemgroupitem.itemgroupid [ITEMGROUP]
FROM inventdimcombination
INNER JOIN inventdim
ON inventdim.inventdimid = inventdimcombination.inventdimid
INNER JOIN inventtable
ON inventtable.itemid = inventdimcombination.itemid
INNER JOIN ecoresproduct
ON ecoresproduct.recid =
inventdimcombination.distinctproductvariant
INNER JOIN ecoresproducttranslation
ON ecoresproducttranslation.product = ecoresproduct.recid
INNER JOIN inventitemgroupitem
ON inventitemgroupitem.itemid = inventtable.itemid
AND inventitemgroupitem.itemdataareaid =
inventtable.dataareaid
WHERE inventtable.dataareaid = '@DataAreaID'
When there is a requirement to generate dynamic report name. For example when saving report, the report name should be appended with Customer ID, Invoice ID etc. To do this in the controller class we have to write code for parmDialogCaption() method shown below:-
class DemoController extends SrsReportRunController
{
public void prePromptModifyContract()
{
ProjInvoiceJour projInvoiceJour;
DemoContract contract;
FormDataSource fds;
contract = this.parmReportContract().parmRdpContract() as DemoController;
projInvoiceJour = args.record();
fds = args.record().dataSource();
contract.parmProjInvoiceId(projInvoiceJour.ProjInvoiceId);
}
public static client void main(Args args)
{
DemoController demoController;
ProjInvoiceJour projInvoiceJour;
demoController= new DemoController();
demoController.parmArgs(args);
demoController.parmReportName(ssrsReportStr(RAR_AmazonBillingReport, Report));
demoController.parmShowDialog(false);
demoController.parmReportContract().parmReportCaption("Project Invoice Report");
projInvoiceJour = args.record();
demoController.parmDialogCaption("Project Invoice Report" + "_"+ projInvoiceJour.ProjInvoiceId); // This will generate dynamic report name
demoController.startOperation();
}
}
when you export the report in excel, pdf or csv, the report name will be appended with ProjInvoiceId.
Deploy all the SSRS Reports using power shell in D365FnO. Please follow below two steps:
1) Open Windows Power Shell
Start > Run > Windows PowerShell
2) Run the below Power Shell command
K:\AosService\PackagesLocalDirectory\Plugins\AxReportVmRoleStartupTask\DeployAllReportsToSSRS.ps1 -PackageInstallLocation "K:\AosService\PackagesLocalDirectory"
This is common requirement in X++ to create a class to upload data from Excel in a table. So below code you can use and customize as per your requirement.
using System.IO;
using OfficeOpenXml;
using OfficeOpenXml.ExcelPackage;
using OfficeOpenXml.ExcelRange;
class ExcelUpload
{
/// <summary>
/// Runs the class with the specified arguments.
/// </summary>
/// <param name = “_args”>The specified arguments.</param>
public static void main(Args _args)
{
System.IO.Stream stream;
ExcelSpreadsheetName sheeet;
FileUploadBuild fileUpload;
DialogGroup dlgUploadGroup;
FileUploadBuild fileUploadBuild;
FormBuildControl formBuildControl;
ProjTable projTable;
Dialog dialog = new Dialog("Import the Projects data from Excel");
dlgUploadGroup = dialog.addGroup("Select excel file");
formBuildControl = dialog.formBuildDesign().control(dlgUploadGroup.name());
fileUploadBuild = formBuildControl.addControlEx(classstr(FileUpload), 'Upload');
fileUploadBuild.style(FileUploadStyle::MinimalWithFilename);
fileUploadBuild.fileTypesAccepted('.xlsx');
if (dialog.run() && dialog.closedOk())
{
FileUpload fileUploadControl = dialog.formRun().control(dialog.formRun().controlId('Upload'));
FileUploadTemporaryStorageResult fileUploadResult = fileUploadControl.getFileUploadResult();
if (fileUploadResult != null && fileUploadResult.getUploadStatus())
{
stream = fileUploadResult.openResult();
using (OfficeOpenXml.ExcelPackage package = new OfficeOpenXml.ExcelPackage(stream))
{
int rowCount, i;
Package.Load(stream);
OfficeOpenXml.ExcelWorksheet worksheet = package.get_Workbook().get_Worksheets().get_Item(1);
OfficeOpenXml.ExcelRange range = worksheet.Cells;
rowCount = worksheet.Dimension.End.Row - worksheet.Dimension.Start.Row + 1;
try
{
for (i = 2; i<= rowCount; i++)
{
str projId = range.get_Item(i, 1).value;
str projStatus = range.get_Item(i, 2).value;
//str startDate = range.get_Item(i, 3).value;
select firstonly projTable where projTable.ProjId == projId;
if(projTable)
{
ttsbegin;
projTable.selectForUpdate(true);
projTable.reread();
if(projTable.Status == ProjStatus::Created)
{
projTable.status().runStartUp();
}
//projTable.StartDate = str2Date(startDate, 213);
if(range.get_Item(i, 3).value)
{
projTable.StartDate = DateTimeUtil::date(range.get_Item(i, 3).value);
projTable.doUpdate();
}
ttscommit;
}
}
info("Data inserted succesfully");
}
catch (Exception::Error)
{
throw error("error here");
}
}
}
}
}
}
While doing customization I came across a scenario where the datatype of a column was of string type and value was stored as "1111.93799999999999994". And the requirement was to get exact two digits after the decimal point. No rounding up/down was required. In the below code example you can achieve this.
class stringSplitFunctionDemo
{
/// <summary>
/// Runs the class with the specified arguments.
/// </summary>
/// <param name = "_args">The specified arguments.</param>
public static void main(Args _args)
{
str str1='1111.93799999999999994';
List strlist=new List(Types::String);
ListIterator iterator;
str _Value,beforeDec,afterDec,finalOutput;
int x;
;
strlist=strSplit(str1,'.');
iterator = new ListIterator(strlist);
while(iterator.more())
{
x= x+1;
if(x==1)
{
beforeDec =iterator.value();
}
if(x==2)
{
afterDec = iterator.value();
}
iterator.next();
}
finalOutput = beforeDec +'.'+ subStr(afterDec,1,2);
Info(finalOutput);
}
}
How to create Microsoft hosted VM in D365FnO using LCS
There are two types of VM in D365FnO Microsoft hosted and Cloud hosted. Here are we will discuss about Microsoft hosted.
Step 1: Login to LCS and Click on your project and you will
get list of Microsoft Hosted Environments on right hand side of screen.
Step2: Click on the Configure button
Step 3: Choose D365FnO version and platform update
Step 4: After step 3, a pop screen will come up asking to Choose environment topology. And topology means do you want to create a development VM or build VM.
Step 5: Now keep clicking on the Next button and it will prompt you to enter certain details like VM Name and it will also ask you to enter email id where notifications from LCS will be sent. All the screens are self-explanatory, and you are done. A request in LCS is submitted for VM creation and it takes generally 24 hours for VM creation.
Below is the sql get enum id and enum value of a enum in D365 F&O using sql. SELECT enumidtable . NAME 'Enum Name' ...
