Hello everyone, I have received a requirement to create a computed column in a view using X++.
Please follow the steps below
Step 1:
Right-click on the 'Fields' node, and you can select the type of computed column that is required.
Step 3 :
In the field properties, you need to select the method. Before selecting the method, make sure to add that method to the view.
Step 2 :
I have created two methods in the view to meet my requirements:
1st method: This method retrieves the active status based on the sales status
public static server str ActiveStatus()
{
str value;
value = strFmt(@"SELECT
CASE
WHEN SalesTable.SalesStatus = 3 THEN 'In active'
WHEN SalesTable.SalesStatus = 0 or SalesTable.SalesStatus = 1 THEN 'Active'
ELSE ' '
END AS ActiveStatus
FROM SalesTable where SalesTable.Recid = %1",
SysComputedColumn::returnField(identifierStr(TestView),
identifierStr(SalesTable),
fieldStr(SalesTable, RecId)));
return value;
}2nd method: Counting the number of lines available for the sales order.
public static server str LineCount()
{
str value;
value = strFmt(@"SELECT count(Recid) as LinesCount
FROM SalesLine where SalesLine.SalesId = %1",
SysComputedColumn::returnField(identifierStr(TestView),
identifierStr(SalesTable),
fieldStr(SalesTable, SalesId)));
return value;
}After adding these methods in the view field properties, you need to perform a 'Build and Sync' operation.
Different methods:
Getting Sales category.
public server static str salesCategory()
{
TableName viewName = identifierStr(TestView);
str itemIdField = SysComputedColumn::returnField(viewName,
identifierStr (InventTable_1),
fieldStr(InventTable, ItemId));
return @* (select Max(EC.NAME) from InventTable IT
inner join ECORESPRODUCTCATEGORY EPC on EPC.PRODUCT = IT.PRODUCT
inner join ECORESCATEGORYHIERARCHY ECH on ECH.RECID = EPC.CATEGORYHIERARCHY
inner join ECORESCATEGORY EC on EPC.CATEGORY = EC.RECID
where ECH.NAME = 'Sales hierarchy' AND IT.ITEMID - "+itemIdField+")");
}I have a requirement to apply an 'if-else' condition to the 'PriceQuantity' field.
If the price quantity is 0, I need to set it to 1 and then apply division with the markup.
private server static str MarkupdivPriceQty()
{
str value;
value = strFmt(@"select
case
when InventItemPrice.PriceQty = 0 then markup/1
else markup/InventItemPrice.PriceQty
end as PriceQty from InventItemPrice WHERE RECID = %1",
SysComputedColumn::returnField(identifierStr(TestVarianceView),
identifierStr(InventItemPrice),
fieldStr(InventItemPrice, RecId)));
return value;
}
Keep Daxing!!
