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

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

Best Answer

Answers

  • lubostlubost Posts: 552Member
    Never do that. Try to find scenario to create links by NAV.
  • krikikriki Posts: 8,799Member, Moderator
    It is not possible. You need to do that through NAV code.
    Regards,Alain Krikilion
    Use the SEARCH,Luke! || No PM,please use the forum. || May the <SOLVED>-attribute be in your title!
    NAV TechDays 2019: 21 & 22 November 2019, Antwerp (Belgium)
  • ftorneroftornero Posts: 263Member
    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 Posts: 6Member
    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 Posts: 552Member
    1. Create RecordRef variable
    2. Find requested record
    3. Use Recordref.RecordID

    It is simply and clear ... and probably much more faster
  • Khushboo1626Khushboo1626 Posts: 6Member
    @lubost i have used recordref only
  • Khushboo1626Khushboo1626 Posts: 6Member
    @AlexDen Thanks it worked perfectly :)
  • markawilliamsonmarkawilliamson Posts: 1Member
    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.