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

Answers

• lubost Member Posts: 633

  2019-02-15

  Never do that. Try to find scenario to create links by NAV.

  0
• kriki Member, Moderator Posts: 9,121

  2019-02-15

  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!
  
  

  0
• ftornero Member Posts: 524

  2019-02-15

  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
     }  
  }
  

  0
• Khushboo1626 Member Posts: 7

  2019-02-18

  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 :
  
  
  
  
  

  0
• lubost Member Posts: 633

  2019-02-18

  1. Create RecordRef variable
  2. Find requested record
  3. Use Recordref.RecordID
  
  It is simply and clear ... and probably much more faster
  

  0
• Khushboo1626 Member Posts: 7

  2019-02-18

  @lubost i have used recordref only

  0
• Khushboo1626 Member Posts: 7

  2019-02-18

  I referred this link : https://community.dynamics.com/nav/b/navisiontechnicalkulla/archive/2015/11/10/how-to-read-write-notes-in-navision-using-c-al

  0
• AlexDen Member Posts: 86

  2019-02-18 Answer ✓

  Hi,
  
  Try to use function WriteRecordLinkNote() from Codeunit 10 "Type Helper" to save notes.

  5
• Khushboo1626 Member Posts: 7

  2019-02-19

  @AlexDen Thanks it worked perfectly

  0
• markawilliamson Member Posts: 1

  2019-06-04

  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.

  0