Dynamics Community Forum Thread Details

Computed Column in View with Condition

Hello, I'm trying to make a computed column with condition.

If TransactionCurrencyAmount >0, use LedgerJournalTrans.IVS_AssignmentParty(custom field) to get AccountNum. Else use CustTrans to get AccountNum.

But my condition seems to be not working. The value in view is always obtained from LedgerJournalTrans, no matter what the TransactionCurrencyAmount value is.

public static server str getAccountNum()
{
    SysDictTable CustTable          = new SysDictTable(tableNum(CustTable));
    SysDictTable CustTrans          = new SysDictTable(tableNum(CustTrans));
    SysDictTable LedgerJournalTrans = new SysDictTable(tableNum(LedgerJournalTrans));

    str amount, strQuery;
    DictView dv = new DictView(tableNum(IVS_InsuranceTransactionList_v));

    amount = SysComputedColumn::returnField(
                tableStr(IVS_InsuranceTransactionList_v),
                identifierStr(GeneralJournalAccountEntry),
                fieldStr(GeneralJournalAccountEntry, TransactionCurrencyAmount));
    
    if(any2real(amount) < 0)
    {
        strQuery = strFmt("SELECT TOP 1 %1 FROM %2 WHERE %2.%3 = %4 AND %2.%5 = %6",
            CustTrans.fieldName(fieldNum(CustTrans, AccountNum), DbBackend::Sql),
            CustTrans.name(DbBackend::Sql),
            CustTrans.fieldName(fieldNum(CustTrans, Voucher), DbBackend::Sql),
            dv.computedColumnString(tableStr(GeneralJournalEntry), fieldStr(GeneralJournalEntry, SubledgerVoucher), FieldNameGenerationMode::WhereClause),
            CustTrans.fieldName(fieldNum(CustTrans, TransDate), DbBackend::Sql),
            dv.computedColumnString(tableStr(GeneralJournalEntry), fieldStr(GeneralJournalEntry, AccountingDate), FieldNameGenerationMode::WhereClause));
    
    } 
    else
    {
        strQuery = strFmt("SELECT TOP 1 %1 FROM %2 WHERE %2.%1 != '' AND %2.%3 = %4 AND %2.%5 = %6",
            LedgerJournalTrans.fieldName(fieldNum(LedgerJournalTrans, IVS_AssignmentParty), DbBackend::Sql),
            LedgerJournalTrans.name(DbBackend::Sql),
            LedgerJournalTrans.fieldName(fieldNum(LedgerJournalTrans, Voucher), DbBackend::Sql),
            dv.computedColumnString(tableStr(GeneralJournalEntry), fieldStr(GeneralJournalEntry, SubledgerVoucher), FieldNameGenerationMode::WhereClause),
            LedgerJournalTrans.fieldName(fieldNum(LedgerJournalTrans, TransDate), DbBackend::Sql),
            dv.computedColumnString(tableStr(GeneralJournalEntry), fieldStr(GeneralJournalEntry, AccountingDate), FieldNameGenerationMode::WhereClause));
    }

    return strFmt('ISNULL((%1), \'\')', strQuery);
}

Quick Links

Leaderboard