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

Khushboo1626

How to insert record Id in record link table using SQL in Dynamics Nav,
thanks in advance.

AlexDen

Hi,

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

lubost

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

kriki

It is not possible. You need to do that through NAV code.

ftornero

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

Khushboo1626

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 :

lubost

1. Create RecordRef variable
2. Find requested record
3. Use Recordref.RecordID

It is simply and clear ... and probably much more faster

Khushboo1626

@lubost i have used recordref only

Khushboo1626

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

AlexDen

Hi,

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

Khushboo1626

@AlexDen Thanks it worked perfectly

markawilliamson

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.