Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Cannot insert the valu...
Microsoft Dynamics GP (Archived)

Cannot insert the value NULL into column 'ACTINDX', table 'tembdb.dbo.##XXXXXXX', column does not allow nulls. INSERT fails.

(0) ShareShare
ReportReport
Posted on by Kimberley Stevens 720

Does anyone know which tables the Account Transactions List on the Financial Navigation Pane pulls from? When clicking on this report, we are receiving the error, "Cannot insert the value NULL into column 'ACTINDX', table 'tembdb.dbo.##XXXXXXX', column does not allow nulls.  INSERT fails."

In addition to eyeballing all segments, accounts, etc from both the front end and the back end, I've also tried the following queries.  None of them result in any data:

SELECT * FROM GL10001 WHERE ACTINDX NOT IN (SELECT ACTINDX FROM GL00100)

SELECT * FROM GL20000 WHERE ACTINDX NOT IN (SELECT ACTINDX FROM GL00100)

SELECT * FROM GL30000 WHERE ACTINDX NOT IN (SELECT ACTINDX FROM GL00100)

SELECT * FROM GL10001 WHERE ACTINDX = ''

SELECT * FROM GL20000 WHERE ACTINDX = ''

SELECT * FROM GL30000 WHERE ACTINDX = ''

SELECT * FROM GL10001 WHERE ACTINDX = '0'

SELECT * FROM GL20000 WHERE ACTINDX = '0'

SELECT * FROM GL30000 WHERE ACTINDX = '0'

SELECT * FROM GL10001 WHERE ACTINDX NOT IN (SELECT ACTINDX FROM GL00105)

SELECT * FROM GL20000 WHERE ACTINDX NOT IN (SELECT ACTINDX FROM GL00105)

SELECT * FROM GL30000 WHERE ACTINDX NOT IN (SELECT ACTINDX FROM GL00105)

I can't figure out where else to look!

I've also run Check Links and it doesn't result in any errors.

Thanks!

Kimberley

*This post is locked for comments

  • Kimberley Stevens Profile Picture
    Kimberley Stevens 720 on at
    RE: Cannot insert the value NULL into column 'ACTINDX', table 'tembdb.dbo.##XXXXXXX', column does not allow nulls. INSERT fails.

    Deleted the GL10100 record with no ill effects.  Check Links runs okay, and so does the Trial Balance in the Navigation Pane.  

    This user is having lots of issues with connectivity, and we're not sure why yet.

    Thanks for your assistance!

  • Verified answer
    soma Profile Picture
    soma 24,408 on at
    RE: Cannot insert the value NULL into column 'ACTINDX', table 'tembdb.dbo.##XXXXXXX', column does not allow nulls. INSERT fails.

    Heather is absolutely correct. The records exist in the table GL10100 had an interrupted posting records. So, check if all the entries are correct in your GL open table GL20000. If everything is correct then delete the records from GL10100 table. Because, after posted and closed the quick journal entry window, the posted transactions will move from GL10100 table to GL20000 table.

    Note: Before deleting records from GL10100 table, make sure you have a latest backup for company database.

    Hope this helps!!!

  • Verified answer
    Heather Roggeveen Profile Picture
    Heather Roggeveen 9,144 on at
    RE: Cannot insert the value NULL into column 'ACTINDX', table 'tembdb.dbo.##XXXXXXX', column does not allow nulls. INSERT fails.

    You may have had an interrupted posting that has created the orphan record.  If everything is where is should be in the 20000 range tables, then you may just have to delete that line from the GL10100 table.  Do a backup of the full database or just that table first.

  • Kimberley Stevens Profile Picture
    Kimberley Stevens 720 on at
    RE: Cannot insert the value NULL into column 'ACTINDX', table 'tembdb.dbo.##XXXXXXX', column does not allow nulls. INSERT fails.

    Thank you very much for your help!  It still required a fair amount of backtracking, but I was eventually able to find the problem records this way.  Both are quick journal entries.  One had a record in GL10100, but nothing in GL10101 and the other was the opposite scenario.  

    I was able to find and delete one from the front end, although it took two tries.  After I deleted it the first time and re-ran Check Links, it then noticed the problem and added the missing header record.  Then I still had to go back and delete it a second time in order to accurately remove it from both tables.

    The second record is not being as cooperative.  This is a record that has been posted and voided, so I cannot do anything from the front end.  The entry exists in GL20000 already, but the header record still exists in GL10100.  This is resulting in one line showing up in the glWorkTransactions view with an ACTINDX of NULL and a SEQNUMBR of NULL.  I can't figure out what was wrong with the entry in the first place, or why I still have a record in GL10100.  The record in GL10100 shows an ERRSTATE value of 20048.  Can anyone provide insight on this?

  • Verified answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Cannot insert the value NULL into column 'ACTINDX', table 'tembdb.dbo.##XXXXXXX', column does not allow nulls. INSERT fails.

    Hi,

    if you can run profiler trace, you will be able to find the query which is causing the error, so it would be easy to figure it out.

    to run trace, you need to open SSMS

    1. go to tools menu and open SQL profiler

    2.in Trace properties window, select "errors and warnings", "Stored procedures" and "TSQL" and deselect all the other options,

    3. click on Run.

    then verify the error.

    Regards,

    SantoshG

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

Congratulations 2024 Spotlight Honorees

Kudos to all of our 2024 community stars! 🎉

Meet the Top 10 leaders for December

Congratulations to our December super stars! 🥳

Start Your Super User Journey

Join the ranks of our community heros! 🦹

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,735 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,466 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans