Dynamics Community Forum Thread Details

I am trying to follow the below link but there is something missing in the call to getDirPartyNameFromParty: Using Computed Columns in Microsoft Dynamics AX 2012 (stoneridgesoftware.com)

I have a view that has a query as its datasource. This query has both the RetailTransactionTable and SalesTable linked to my custom table TMC_CCOrderOverride via TransactionId and SalesId repsectively.

I want to add the Ordering customer name as a computed column. The CustAccount can be either on the SalesTable or on the RetailTransactionTable, depending which one is the source for the record in TMC_CCOrderOVerride.

    public static server str getDirPartyNameFromParty(        TableName _viewName,        str _DataSourceCustTable,        str _DataSourceCustTableField)    {        str expression;        expression = 'SELECT dpt.NAMEALIAS FROM DIRPARTYTABLE dpt, CUSTTABLE ct WHERE dpt.RECID = ct.PARTY AND ct.ACCOUNTNUM = '            + SysComputedColumn::returnField(_viewName, _DataSourceCustTable, _DataSourceCustTableField);        return expression;    }    private static server str OrderCustName()    {        TableName viewName = tableStr(TMC_CCOrderOverrideView);        str vRecid = SysComputedColumn::returnField(viewName, identifierStr(RetailTransactionTable), fieldStr(RetailTransactionTable, Recid));        str vNameS = TMC_CCOrderOverrideView::getDirPartyNameFromParty(viewName, identifierStr(Salestable), fieldStr(SalesTable, CustAccount));        str vNameT = TMC_CCOrderOverrideView::getDirPartyNameFromParty(viewName, identifierStr(RetailTransactionTable), fieldStr(RetailTransactionTable, CustAccount));        return SysComputedColumn::if(SysComputedColumn::equalExpression(vRecid, SysComputedColumn::returnLiteral(0)),            vNameS,            vNameT            );    }    private static str InvoiceCustName()    {        TableName viewName = tableStr(TMC_CCOrderOverrideView);        str vName = TMC_CCOrderOverrideView::getDirPartyNameFromParty(viewName, identifierStr(Salestable), fieldStr(SalesTable, InvoiceAccount));        return vName;    }

The View-method InvoiceCustName works without an issue.

However the View-methos OrderCustName has a problem because the build fails with error:

Severity   Code   Description   Project   File   Line   Suppression State
Error       Log level - Error | Managed Data Sync Entity Worker encountered an exception, but is continuing because ContinueOnError is true. View Name and ID: 'TMC_CCOrderOverrideView(22009).' Exception: System.InvalidOperationException: Database execution failed: Incorrect syntax near the keyword 'SELECT'.           0
Incorrect syntax near the keyword 'ELSE'.           0
Incorrect syntax near 'T1'.           0
Incorrect syntax near ')'.           0

When it creates the view for the InvoiceCustName, the CAST is added as:

(CAST ((SELECT dpt.NAMEALIAS FROM DIRPARTYTABLE dpt, CUSTTABLE ct WHERE dpt.RECID = ct.PARTY AND ct.ACCOUNTNUM = T3.INVOICEACCOUNT) AS NVARCHAR(20))) AS INVOICECUSTCOMPUTED,

The CAST for the OrderCustName is added as follow:

(CAST ((CASE WHEN T4.RECID = 0 THEN SELECT top 1 dpt.NAMEALIAS FROM DIRPARTYTABLE dpt, CUSTTABLE ct WHERE dpt.RECID = ct.PARTY AND ct.ACCOUNTNUM = T3.CUSTACCOUNT ELSE SELECT top 1 dpt.NAMEALIAS FROM DIRPARTYTABLE dpt, CUSTTABLE ct WHERE dpt.RECID = ct.PARTY AND ct.ACCOUNTNUM = T4.CUSTACCOUNT END) AS NVARCHAR(10))) AS ORDERCUSTCOMPUTED FROM TMC_CCOVERRIDEORDERS T1

How should I create the View-method OrdercustName for it to generate the view correctly.

Any help is much appreciated.

The reason I want to add the column in the view is because I have a simplelist form to show all the TMC_CCOverride records and I know the users will want to search on the name of the customer. I can use a display-method on the form and add it to the simpleList column but a display-method is not searchable.