Share
Report
1,433I 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
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 T1How 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.
All responses (
Answers (
