Skip to main content

Notifications

Microsoft Dynamics NAV (Archived)

SQL statement to Modify Items and comments

Posted on by Microsoft Employee

Hi All, 

I have a SQL Query to show the Production BOM Lines where there is a item with a Quantity per less then 1. With this list of parts I would like to do two things. Set a field called 'Blocked' to true. Secondly I would like to create a comment in the comment table for that item with the comment field saying 'Item Blocked'.

How can I achieve this is SQL? 

My query to find the lines

select DISTINCT [Production BOM No_], [Version Code] from dbo.[TIOGA$Production BOM Line]
where [Unit of Measure Code] = 'EACH'
and [Quantity per] < 1
AND [Quantity per] != 0
AND [Version Code] = ''

*This post is locked for comments

  • Suggested answer
    Alex A Profile Picture
    Alex A 2,348 on at
    RE: SQL statement to Modify Items and comments

    Hello,

    If you can do this within the C/AL language and the safety of the NAV Development Environment, then by all means you should do it that way. Those of us who know T-SQL/DML only use it as a last resort to fix problems when it cannot be done functionally or when it isn't possible to do it through the NAV Development Environment - usually only at the recommendation of Microsoft.

    It's fine if you're writing reports (read only), but when it comes to inserting records, why not use RapidStart to accomplish this?

  • Suresh Kulla Profile Picture
    Suresh Kulla 43,745 on at
    RE: SQL statement to Modify Items and comments

    To improve your SQL Skills you are doing it in SQL ?

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: SQL statement to Modify Items and comments

    Hi does anyone have a solution for this?

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: SQL statement to Modify Items and comments

    I've wrote this, but I'm getting the following error

    Msg 515, Level 16, State 2, Line 35
    Cannot insert the value NULL into column 'Line No_', table 'LH_RESTORE_20171211.dbo.TIOGA$Comment Line'; column does not allow nulls. INSERT fails.

    DECLARE @ItemNo VARCHAR(50)
    DECLARE @Comment VARCHAR(250)
    DECLARE @LineNo INT;
    -- Set the variables
    SET @Comment = 'Unit of Measure parts must be reviewed'

    -- Select the Items which need amending
    DECLARE db_Cursor CURSOR FOR
    SELECT [No_]
    from dbo.[TIOGA$Production BOM Line] L
    where [Unit of Measure Code] = 'EACH'
    and [Quantity per] < 1
    AND [Quantity per] != 0
    AND [Version Code] = ''

    OPEN db_Cursor
    FETCH NEXT FROM db_Cursor INTO @ItemNo

    WHILE @@FETCH_STATUS = 0
    BEGIN

    -- Get the last Line No.
    select @LineNo = max([Line No_]) from dbo.[TIOGA$Comment Line] C
    where [No_] = @ItemNo
    and [Table Name] = 3

    SET @LineNo = @LineNo + 10000;

    -- Set Blocked on the Item
    update dbo.[TIOGA$Item]
    set [Blocked] = 1
    where [No_] = @ItemNo

    -- Insert the comment line
    INSERT INTO dbo.[TIOGA$Comment Line] ([Table Name],[No_],[Line No_],[Date],
    [Code],[Comment],[Comment Type],[Indentation],[Complete])
    VALUES (3,@ItemNo,@LineNo,'20/12/2017','',@Comment,0,0,0)

    FETCH NEXT FROM db_cursor INTO @ItemNo
    END
    CLOSE db_Cursor
    DEALLOCATE db_cursor

  • Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: SQL statement to Modify Items and comments

    I could write this in CAL very easily, I'm just trying to improve SQL Skills. 

  • Suggested answer
    Suresh Kulla Profile Picture
    Suresh Kulla 43,745 on at
    RE: SQL statement to Modify Items and comments

    First of all why are you doing that using SQL. It is not recommeded to update using SQL Statement.

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

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Community AMA December 12th

Join us as we continue to demystify the Dynamics 365 Contact Center

New! Quick response templatesâš¡

Save time with the new custom templates!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

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

#2
Martin Dráb Profile Picture

Martin Dráb 230,056 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans