web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Finance | Project Operations, Human Resources, ...
Unanswered

Cannot insert the value NULL into column

(0) ShareShare
ReportReport
Posted on by 75

I am using insert_recordset to insert data in a custom table by using following query;

insert_recordset myTable (LineAmountMST,Qty, ItemId, InventDimId, InvoiceDate
            , InventSizeId, InventColorId
            , OperatingUnitID
            , COGS
            )
        SELECT SUM(LineAmountMST), SUM(Qty), ItemId, InventDimId, InvoiceDate
            from CustInvoiceTrans
            JOIN CustInvoiceJour
            where CustInvoiceTrans.SalesId == CustInvoiceJour.SalesId
            && CustInvoiceTrans.InvoiceId == CustInvoiceJour.InvoiceId
            && CustInvoiceTrans.InvoiceDate == CustInvoiceJour.InvoiceDate
            && CustInvoiceTrans.numberSequenceGroup == CustInvoiceJour.numberSequenceGroup
            && CustInvoiceTrans.InvoiceDate >= start
            && CustInvoiceTrans.InvoiceDate <= end
            OUTER JOIN InventSizeId, InventColorId
            from InventDim
            where CustInvoiceTrans.InventDimId == InventDim.InventDimId
            OUTER JOIN OMInternalOrganization
            from RetailCustInvoiceJourTable
            group by RetailCustInvoiceJourTable.OMInternalOrganization,
            CustInvoiceTrans.ItemId, CustInvoiceTrans.inventdimid, CustInvoiceTrans.InvoiceDate
            , InventDim.InventSizeId, InventDim.InventColorId
            where CustInvoiceJour.RecId == RetailCustInvoiceJourTable.CustInvoiceJour
            OUTER JOIN InventTransOrigin
            where CustInvoiceTrans.InventTransId == InventTransOrigin.InventTransId
            OUTER JOIN sum(COGS) from KSPInventTransGrouped
            where CustInvoiceTrans.InvoiceId == KSPInventTransGrouped.InvoiceId
            && InventTransOrigin.RecId == KSPInventTransGrouped.InventTransOrigin;

but it is showing following errors;

  • [SQL ServerCannot insert the value NULL into column 'Field', table 'AxDB.dbo.MYTABLE'; column does not allow nulls. INSERT fails.
  • Object Server Azure:
  • Cannot insert multiple records in MYTABLE (MYTABLE). The SQL database has issued an error.

Any solution to resolve this issue?

I have the same question (0)
  • GirishS Profile Picture
    27,833 Moderator on at

    Hi Ashraf,

    I think you have missed to add OperatingUnitId reference in the select statement.

    Try to run the select statement separately and find which values are null.

    Thanks,

    Girish S.

  • Martin Dráb Profile Picture
    238,769 Most Valuable Professional on at

    You're getting values of OperatingUnitID and COGS from outer-joined data sources; I think that that one of them (or both) doesn't return any record, therefore the value is NULL. That's not a valid field value in F&O.

  • Ashraf_ahk Profile Picture
    75 on at

    OperatingUnitId = OMInternalOrganization

  • Ashraf_ahk Profile Picture
    75 on at

    Multiple lines have no record.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Introducing the 2026 Season 1 community Super Users

Congratulations to our 2026 Super Stars!

Congratulations to our 2025 Community Spotlights

Thanks to all of our 2025 Community Spotlight stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 509 Super User 2026 Season 1

#2
Giorgio Bonacorsi Profile Picture

Giorgio Bonacorsi 375

#3
Adis Profile Picture

Adis 268 Super User 2026 Season 1

Last 30 days Overall leaderboard

Product updates

Dynamics 365 release plans