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.
Below is the sql get enum id and enum value of a enum in D365 F&O using sql. SELECT enumidtable . NAME 'Enum Name' ...
