How to insert record Id in record link table using SQL in Dynamics Nav

Khushboo1626Khushboo1626 Member Posts: 7
How to insert record Id in record link table using SQL in Dynamics Nav,
thanks in advance.

Best Answer

Answers

  • lubostlubost Member Posts: 611
    Never do that. Try to find scenario to create links by NAV.
  • krikikriki Member, Moderator Posts: 9,094
    It is not possible. You need to do that through NAV code.
    Regards,Alain Krikilion
    No PM,please use the forum. || May the <SOLVED>-attribute be in your title!


  • ftorneroftornero Member Posts: 522
    Actually it's possible, at least in NAV 2009, where I get somewhere the RecordID format

    This is the code in Powershell to create the RecordID
    #---------------------------------------------------------------------------------------------------------
    function createRecordID {
    #---------------------------------------------------------------------------------------------------------
      Param
      (
    	[Parameter(Mandatory=$True)]
    	[string]$documento
      )
     
      $long = 7 + $documento.Length + 3
      $sqlBytes = new-object System.Data.SqlTypes.SqlBytes # -ArgumentList $long
      $sqlBytes = new-object byte[] -ArgumentList $long
      $sqlBytes[0] = 110  # Table number
      $sqlBytes[1] = 0
      $sqlBytes[2] = 0
      $sqlBytes[3] = 0
      $sqlBytes[4] = 0
      $sqlBytes[5] = 137  # code field
      $sqlBytes[6] = 255  # alfanuméric
    
      for($i=0; $i -lt $documento.Length; $i++){
        $sqlBytes[$i+7] = [byte][char]$documento.Substring($i, 1)
      }
    
      $sqlBytes
    }
    

    And you can do the insert with this code
    #---------------------------------------------------------------------------------------------------------
    function insRecordID {
    #---------------------------------------------------------------------------------------------------------
      Param
      (
    	[Parameter(Mandatory=$True)]
    	[string]$FileName,
    	[Parameter(Mandatory=$True)]
    	[string]$documento
    
      )
      
      $query = @" 
           SELECT 
          [Record ID],[URL1]
          FROM [dbo].[Record Link] 
           WHERE 
          ([Record ID] = @RecordID) AND ([URL1] = @URL1)       
    "@
    
        $connection=new-object System.Data.SqlClient.SQLConnection
    	$connection.ConnectionString="Server={0};Database={1};User={2};Password={3}" -f $server,$database,$user,$pass
        $command=new-object system.Data.SqlClient.SqlCommand($query,$connection)
        $command.CommandTimeout=120
        $connection.Open()
    
        [byte[]]$bytes = ceateRecordID($documento)
        $command.Parameters.Add("@RecordID", [System.Data.SqlDbType]"VarBinary", $sqlBytes.Length) | Out-Null
        $command.Parameters["@RecordID"].Value = $bytes
    
        $command.Parameters.Add("@URL1", [System.Data.SqlDbType]"NChar") | Out-Null
        $command.Parameters["@URL1"].Value = $FileName
    
        $resultado = $command.ExecuteReader()
     
        if (!$resultado.Read()) {
          $query = @" 
                 INSERT [Record Link] 
                ([Record ID]
                ,[URL1]
                ,[URL2]
                ,[URL3]
                ,[URL4]
                ,[Description]
                ,[Type]
                ,[Note]
                ,[Created]
                ,[User ID]
                ,[Company])
                 VALUES 
                 (@RecordID, @URL1, '', '', '', @Desc, 0, '', @Created, @UserID, @Company)
    "@
    
    
          $command=new-object system.Data.SqlClient.SqlCommand($query,$connection)
          $command.CommandTimeout=120
    
          $command.Parameters.Add("@RecordID", [System.Data.SqlDbType]"VarBinary", $sqlBytes.Length) | Out-Null
          $command.Parameters["@RecordID"].Value = $bytes
    
          $command.Parameters.Add("@URL1", [System.Data.SqlDbType]"NChar") | Out-Null
          $command.Parameters["@URL1"].Value = $FileName
    
          [string]$Desc = Split-Path $FileName -Leaf
          $command.Parameters.Add("@Desc", [System.Data.SqlDbType]"NChar") | Out-Null
          $command.Parameters["@Desc"].Value = $Desc
    
          $command.Parameters.Add("@Company", [System.Data.SqlDbType]"NChar") | Out-Null
          $command.Parameters["@Company"].Value = $empresaNAV
    
          $command.Parameters.Add("@UserID", [System.Data.SqlDbType]"NChar") | Out-Null
          $command.Parameters["@UserID"].Value = $user
    
          $command.Parameters.Add("@Created", [System.Data.SqlDbType]"DateTIme") | Out-Null
          $Fecha = (Get-Date).ToUniversalTime().ToString("yyyy-MM-dd HH:mm:ss.fff")
          $command.Parameters["@Created"].Value = $Fecha
    
          $command.ExecuteNonQuery()
          
          $connection.Close() 
           
        } else {
         $connection.Close() 
         LogError(' >> Registro ya existe >>>>> ' + $documento)     
         return 1
       }  
    }
    
  • Khushboo1626Khushboo1626 Member Posts: 7
    The problem with me is when i try to import data using xmlport in record link table the view shows missing data while i insert same data manually it shows complete data but the data in table in inserted fully when i import using xmlport,
    refer the screenshots :

    5ighi5rtsita.png


  • lubostlubost Member Posts: 611
    1. Create RecordRef variable
    2. Find requested record
    3. Use Recordref.RecordID

    It is simply and clear ... and probably much more faster
  • Khushboo1626Khushboo1626 Member Posts: 7
    @lubost i have used recordref only
  • Khushboo1626Khushboo1626 Member Posts: 7
    @AlexDen Thanks it worked perfectly :)
  • markawilliamsonmarkawilliamson Member Posts: 1
    kriki wrote: »
    It is not possible. You need to do that through NAV code.

    Yes, it is possible. It's difficult, but not impossible. You just have to have the right knowledge of the RecordID format and some SQL skills.
Sign In or Register to comment.