Check for unique part number

The first time we wrote about Vault and PowerShell was back in 2012. Since then a lot happened. Meanwhile is PowerShell widely used either inside Vault Data Standard, powerJobs and powerGate, or as stand alone scripting language for Vault in combination with powerVault.

We recently received an interesting question about how to find duplicate part numbers inside Vault. You need to write few lines of code, and before creating a full blown application, why not creating a little script that prints out the duplicate part numbers in a CSV file that can be opened with Excel? And here it is:

Open-VaultConnection -Server "localhost" -Vault "vault" -User "Administrator" -Password ""
$srchConds = New-Object Autodesk.Connectivity.WebServices.SrchCond[] 1
$srchConds[0] = New-Object Autodesk.Connectivity.WebServices.SrchCond
$srchConds[0].PropDefId=88
$srchConds[0].PropTyp="SingleProperty"
$srchConds[0].SrchOper=5
$srchConds[0].SrchRule="Must"
$srchConds[0].SrchTxt=""

$counter = 0
$bookmark = ""
$partNumbers = @{}
$searchStatus = New-Object Autodesk.Connectivity.WebServices.SrchStatus
do {
  $result = $vault.DocumentService.FindFilesBySearchConditions($srchConds, $null, $null, $true, $true, [ref]$bookmark, [ref]$searchStatus)
  $fileIds = $result | Select-Object -ExpandProperty Id
  $props = $vault.PropertyService.GetProperties("FILE",$fileIds,@(88))
  foreach ($prop in $props) { if($prop.Val -ne $null -and $prop.Val -ne "") {$partNumbers[$prop.Val]++ } }
  $counter += $result.Count
  Write-Progress -Activity "searching..." -CurrentOperation "$counter/$($searchStatus.TotalHits) found" -PercentComplete ($counter/$searchStatus.TotalHits*100)
} while ($counter -lt $searchStatus.TotalHits)

$partNumbers.GetEnumerator() | Where-Object { $_.Value -gt 1 } | Export-Csv c:\temp\notUniquePartNumbers.csv -Delimiter ';'

The script uses powerVault for connecting to Vault. Then we use the native API, as in this case speed is key. powerVault would make the coding simpler, but in order to make it simpler, it also loads more information as requested for this case. Usually you don’t care, as you deal just with few records, but in this case, we go over the complete Vault, which might contain hundreds of thousands of files.

So, via the search (FindFilesBySearchConditions) we collect all the files. Now, the search requires search criteria. In our case we look for files that have a Part Number which is not empty. Now, the Vault API requires the property ID for the part number, which is 88, and other search criteria. In order to quickly find out what I need, I just opened the Vault search and configured it as needed and before I’ve started the search, I’ve also opened the vapiTrace.

2016-02-04_17-31-36

Now I can start the search and look how Vault executes the search on an API level. The vapiTrace gives me all the information I need.

2016-02-04_17-36-03

Obviously I can use the Vault API in order to figure out the property ID and so on, but as I don’t have much time and don’t pretend to win the Oscar for the best code, I just write down those lines I need and hard code the IDs for this particular situation.

As you can see in the PowerShell code, the search is executed inside a loop. The reason is that the search just gives us a set of records, usually 100. So, we need to execute the search several times until we have all the files. The $bookmark variable is empty the first time and is then filled by the search to an according value. By re-executing the search with the given bookmark, we get the next set of records. For each results, we ask for the part number property for the given files and populate a hash table ($partNumbers) where the key is the part number and the value is the frequency. So, if the part number shows up several time, the according value in the array gets incremented. At the end we look for all entries in the $partNumbers array with a value (frequency) higher then 1. Those are then exported to a CSV file that you can open with Excel.

To me, this is a beautiful example how scripting can quickly solve a problem.

This entry was posted in PowerShell, powerVault, Vault API. Bookmark the permalink.

One Response to Check for unique part number

  1. Nash Milenkovic says:

    Could you please post a similar example related to Change Orders; for example find all closed change orders for specific user?
    Thanks & regards,
    Nash

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s