Cannot insert the value NULL into column message in insert_recordset in AX 2012

Hi,

When you are trying to insert the insert the record in to a table using insert_recordset method in the case of joining to table either pulling from view or a query then we cannot assign null vaues.

Example:
2 tables are joined in view and some filed in child table values may not be available on join which will result in null, in that case we need to insert null values in target table using insert_recordset.

Solution:
Ignore those fields on insert_recordset selection later update if there is a record using update_recordset.

Sample.
In the below example Building,Community,Project,RetailStore,Unit  are dimension, so values may not come always.

insert_recordset custVendPDCTmp (LedgerDimension, JournalNum, PDCStatus, StopPayment, AmountCurCredit,
        AmountCurDebit, BankChequeNum, BankAccountId, CurrencyCode, DefaultDimension,
        LineNum,MaturityDate,
        PaymentStatus,PaymentRealizedStatus,Voucher,IsReplacementCheck
        // Building,Community,Project,RetailStore,Unit,
        )
        select LedgerDimension,JournalNum, PDCStatus,StopPayment,AmountCurCredit,
        AmountCurDebit,BankChequeNum,BankAccountId,CurrencyCode,DefaultDimension,
        LineNum,MaturityDate,
        PaymentStatus,PaymentRealizedStatus,Voucher,IsReplacementCheck
        //Building,Community,Project,RetailStores,Unit,
            from custVendPDCQueryView where custVendPDCQueryView.AccountType == accountType;

Solution:


update_recordSet custVendPDCTmp
        setting Building = custVendPDCQueryView.Building
        Join custVendPDCQueryView
        where custVendPDCQueryView.DefaultDimension == custVendPDCTmp.DefaultDimension &&            custVendPDCQueryView.Building != "";