Object Type Properties - Connection External Database - Change Object ID

Question

Hello everyone, 
 We have a Project Object connected to an external Database. We've used the Project Number as the M-Files Object ID. Unfortunately, we've noticed (a bit too late) that the customer was changing the Project Numbers on the external application which created inconsistent metadata in certain documents. We had documents where the project was removed/deleted because the Object ID was changed from the Project Number. 
 For example : The project was created in M-Files with Project Number 021004 and the Object ID with the same identifier. The customer changed the Project Number to 622003 and the reaction on M-Files was that 021004 project was deleted, it only remained a project with the number 622003 without the history before that changement (Creation of 622003 Project) 
 We've asked the Database Projects Provider to add in the DB's view a unique identifier that we'll use as Object ID in M-Files. 
 My question is : Changing the object ID will unlink the project in the documents metadata right ? (Project deleted metadata) 
 Thanks for your answers, Leitao Claudio

ant_ibrahim · Accepted Answer

Yes, I have run into an issue like this before where a client was updating a value in their system which was also being used as the object ID in M-Files. If the object ID changes, the impacts in M-Files is that it deletes the object with the initial ID and creates a brand new object with the new ID. Unfortunately I did not find an easy way to fix the items displayed as (Deleted), I ended up writing an external application which connected to the vault and looped through the documents for that class to update the deleted values to reference the correct object ID.

Claudio_Leitao · Answer

Dear All, 
 I've created a PowerShell script that does the work. If it can help any of you, here it goes. 
 <#
@Author : Claudio Leitao
@Date : 28.04.2022
@LastModification : 28.04.2022
@Version : 1.0
@Description : This scripts replaces deleted lookups in documents and replace them with the new ones
#>

# Retrieve the necessary DLL in order for this script to call the native M-Files COM API Function
Add-Type -Path "C:\Program Files\M-Files\$env:mfilesversion\Common\Interop.MFilesAPI.dll"

# Returns the M-Files VaultConnection Object
Function Get-MFilesVault {
 [CmdletBinding()]
 Param(
 [Parameter(Mandatory=$True)]
 [string]$Name,
 [parameter(Mandatory=$True)]
 [object[]]$Connection
 )

 $Connection.GetVaultConnection($Name)
}

# Tests the connection to the Specified Vault and returns its result
Function Test-MFilesVaultConnection {
 [CmdletBinding()]
 Param(
 [parameter(Mandatory=$True)]
 [object[]]$VaultConnection
 )

 return $VaultConnection.TestConnectionToVaultSilent()
}

# Create a connection to the vault and returns the Vault with the user connected to it
Function Connect-MFilesVault {
 [CmdletBinding()]
 Param(
 [parameter(Mandatory=$True)]
 [object[]]$VaultConnection,
 [Parameter(Mandatory=$True)]
 [object]$AuthType,
 [Parameter(Mandatory=$False)]
 [string]$UserName,
 [Parameter(Mandatory=$False)]
 [string]$Password,
 [Parameter(Mandatory=$False)]
 [string]$Domain
 )
 
 return $VaultConnection.LogInAsUser($AuthType, $UserName, $Password, $Domain)

}

# Binds the VaultConnection to the Vault
Function Set-MFilesVaultConnection {
 [CmdletBinding()]
 Param(
 [parameter(Mandatory=$True)]
 [object[]]$VaultConnection
 )

 $VaultConnection.BindToVault(0, $true, $false)
}

# Get the content of a specfic VIEW ID
Function Get-FolderContentItemsVIEW {
 [CmdletBinding()]
 Param(
 [parameter(Mandatory = $True)]
 [long]$ViewID,
 [parameter(Mandatory = $True)]
 [object]$Vault
 )

 $FolderDefsView = New-Object -COM MFilesAPI.FolderDefs
 $FolderDefView = New-Object -COM MFilesAPI.FolderDef
 $FolderDefView.SetView($ViewID)
 $FolderDefsView.Add(-1, $FolderDefView)

 Return $Vault.ViewOperations.GetFolderContents($FolderDefsView)
}

# Get the project lookup based on its submission number
Function Get-ProjectLookup-FromSubNum {
 [CmdletBinding()]
 Param(
 [parameter(Mandatory = $True)]
 [long]$ViewID,
 [parameter(Mandatory = $True)]
 [object]$Vault,
 [parameter(Mandatory = $True)]
 [String]$ProjectSubNum
 )

 # Get the Folder Content items set with a view ID
 $FolderContentItems = Get-FolderContentItemsVIEW -ViewID $ViewID -Vault $Vault

 # Look for each Project
 foreach ($item in $FolderContentItems){

 # Get the property of the current ObjVer Item
 $ObjVerProp = $Vault.ObjectOperations.GetObjectVersionAndProperties($item.ObjectVersion.ObjVer, $true).Properties

 # Get the projects name
 $ProjectSubNumber = $ObjVerProp.SearchForProperty(1435).TypedValue.DisplayValue

 if ($ProjectSubNumber -eq $ProjectSubNum){
 $ProjectLookup = New-Object -COM MFilesAPI.Lookup
 $ProjectLookup.item = $item.ObjectVersion.ObjVer.ID
 $ProjectLookup.version = -1
 Return $ProjectLookup
 }
 }

}

# Set the new project on the document
Function Set-NewProject {
 [CmdletBinding()]
 Param(
 [parameter(Mandatory = $True)]
 [long]$ViewID,
 [parameter(Mandatory = $True)]
 [object]$Vault
 )

 # Get the Folder Content items set with a view
 $FolderContentItems = Get-FolderContentItemsVIEW -ViewID $ViewID -Vault $Vault

 foreach ($item in $FolderContentItems){
 
 # Get the property of the current ObjVer's Item
 $ObjVerProp = $Vault.ObjectOperations.GetObjectVersionAndProperties($item.ObjectVersion.ObjVer, $true).Properties

 # Get the projects name
 $ProjectName = $ObjVerProp.SearchForProperty(1320).TypedValue.DisplayValue
 # Get the projects submission number from its name
 $ProjectSubNumber = $ProjectName.Substring(0,8)

 # Gets the project Lookup
 $ProjectObjLookup = Get-ProjectLookup-FromSubNum -ViewID 428 -Vault $Vault -ProjectSubNum $ProjectSubNumber

 # Creates a new ObjVer for Check Out and Check In
 $newDocObjId = New-Object -COM MFilesAPI.ObjID
 $newDocObjId.SetIDs(0, $item.ObjectVersion.ObjVer.ID)
 $newDocObjVer = $Vault.ObjectOperations.GetLatestObjVer($newDocObjId, $False, $True)
 $newDocObjVer.Version = ([int]$newDocObjVer.Version + [int]1)

 # checks out the document
 $Vault.ObjectOperations.CheckOut($newDocObjVer.ObjID)

 # Sets the new project lookup
 $projectPropVal = New-Object -COM MFilesAPI.PropertyValue
 $projectPropVal.PropertyDef = 1320
 $ProjectPropVal.Value.SetValue([MFilesAPI.MFDataType]::MFDatatypeLookup, $ProjectObjLookup)
 $Vault.ObjectPropertyOperations.SetProperty($newDocObjVer, $ProjectPropVal)

 # Checks the object IN
 $Vault.ObjectOperations.CheckIn($newDocObjVer)

 
 }
}

# MAIN PART OF THIS SCRIPT
# Execute all the correct functions with the correct paramaters 

# Using M-Files Client
$MFClient = New-Object -COM MFilesAPI.MFilesClientApplication

# Get M-Files Vault Connection
$VaultConnection = Get-MFilesVault -Name "PPSA" -Connection $MFClient

# Test Connection to Document Vault
$ConnectResult = Test-MFilesVaultConnection -VaultConnection $VaultConnection

# View ID
$ViewID = 427

If($ConnectResult -eq 0){
 # Connect to Document Vault
 $Vault = Set-MFilesVaultConnection -VaultConnection $VaultConnection

 Set-NewProject -ViewID $ViewID -Vault $Vault

} ElseIf ($ConnectResult -eq 1){
 # Connect to Document Vault using different credentials
 $Vault = Connect-MFilesVault -VaultConnection $VaultConnection -AuthType $([MFilesAPI.MFAuthType]::MFAuthTypeSpecificWindowsUser) -UserName "xxxxxx" -Password "xxxxxx" -Domain "AD" 

 Set-NewProject -ViewID $ViewID -Vault $Vault
} Else {

 $SMTPPwd = "xxxxx"
 $SMTPUsername = "xxxxxx"
 $secpasswd = ConvertTo-SecureString $SMTPPwd -AsPlainText -Force; 
 $credentials = New-Object System.Management.Automation.PSCredential($SMTPUsername, $secpasswd);

 Send-MailMessage -To "xxxxxx" -from "xxxxx" -SmtpServer "xxxxxx" -Port xxxx -Subject "xxxxxxx" -Credential $credentials -BodyAsHtml -Encoding utf8; 
} 
 Regards, Claudio

Craig Hawker · Answer

I am aware that there is a rarely-used way to change the column used for the object ID. You can effectively configure both the "old" and "new" object ID columns, M-Files will ensure that everything is updated, then you can remove the old one. This should keep the relationships intact. 
 However: it is rarely used, and I am aware of at least one partner having an issue with it that meant they had to revert to a backup. So I would urge caution (and to ensure that you have a good backup, and a tested restore procedure). I seem to recall that their specific issue was that there was an overlap in the IDs (one of the new ID values was used as an old ID as well) and that caused the problem. 
 Support should be able to tell you more.

Object Type Properties - Connection External Database - Change Object ID

Top Replies