View with Virtual Folder hierarchy (Property Folder - Grouping Levels)

Hello everyone,

Our customer needed a specific report for a document type, the objective was to calculate the total weight of each project (the weight was mentioned in the document properties).

So we have a View (Document : by project), the grouping level is done with the Project Lookup, so you have then one Virtual Folder for each existing Project. My goal was to look for each document in each virtual folder with the COM API (using Powershell).

The big problem I had, was to succeed in getting the items (ObjectVersion) from each virtual folder (which are the projects). When I set the PropertyFolder and tried to get its content, I had an “Invalid Search Condition” error

Hope it can help somebody out there, the point is to:

  1. Create a FolderDefs AND a FolderDef set with the View ID
  2. Get the FolderContents with these objects
  3. Create a second FolderDefs AND a FolderDef set with the property folders
  4. Add FIRST the FolderDef (View) and SECONDLY the FolderDef (PropertyFolder) in the second FolderDefs collection you've just created (The positions are very important)
  5. Get the FolderContents with these second objects
  6. Retrieve your ObjectVersion Slight smile

REFS:

Credits to to help me out with this Slight smile

P.S. I know the code might be very dirty (but don't have the time to polish it)

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, $FolderDef)

    Return $Vault.ViewOperations.GetFolderContents($FolderDefsView)
}
Function Get-MailContent {
    [CmdletBinding()]
    Param(
        [parameter(Mandatory = $True)]
        [long]$ViewID,
        [parameter(Mandatory = $True)]
        [object]$Vault
    )
    #This Variable will be used to create the content of the mail report
    $ListeContent = ""

    # Get the Folder Content items set with a view
    $FolderContentItems = Get-FolderContentItemsVIEW -ViewID $ViewID -Vault $Vault

    # Create the Folder Defs for the property Folder
    $FolderDefsPF = New-Object -COM MFilesAPI.FolderDefs
    $FolderDefPF = New-Object -COM MFilesAPI.FolderDef

    $FolderDefsPF.Add(-1, $FolderDefView)

    # Look for all the Property Folders found in the view
    for ($i = 0; $i -lt $FolderContentItems.Count; $i++){
        
        # Total weight of each project
        $TotWeight = [double]0

        #Retrieve the Property Folder
        $PropertyFolder = $FolderContentItems.item($i).PropertyFolder
        $ProjectName = $PropertyFolder.DisplayValue

        $ListeContent = $ListeContent + "<h4>" + $ProjectName + "</h4>" +"<table id='custom'><tr><th>N° de la liste</th><th>Date de réception</th><th>Etape du Workflow</th><th>Poids (To)</th></tr>" 
        # Set the property folder for the folder def
        $FolderDefPF.SetPropertyFolder($PropertyFolder)
    
        # Append the FD in the Folder Defs Collection
        $FolderDefsPF.Add(-1, $FolderDefPF)    

        # Get all the items in the Property Folder (Virtual Folder), these items should all be Object Versions
        $FolderContentItemsPF = $Vault.ViewOperations.GetFolderContents($FolderDefsPF)

        # Look for each item in the FC
        foreach ($item in $FolderContentItemsPF) {

            $ObjVerProp = $Vault.ObjectOperations.GetObjectVersionAndProperties($item.ObjectVersion.ObjVer, $true).Properties
        
            # Get all the object properties to build the mail content
            $ObjectURL = Get-ObjVerURL -FolderItem $item -Vault $Vault

            $NumDoc = $ObjVerProp.SearchForProperty(1473).TypedValue.DisplayValue
            $ReceptDate = $ObjVerProp.SearchForProperty(1356).TypedValue.DisplayValue
            $WFStep = $ObjVerProp.SearchForProperty(39).TypedValue.DisplayValue
            $Weight = $ObjVerProp.SearchForProperty(1601).TypedValue.DisplayValue

            $ListeContent = $ListeContent + "<tr><td><a href=" + $ObjectURL + ">" + $NumDoc + "</a></td>"
            $ListeContent = $ListeContent + "<td>" + $ReceptDate +"</td>"
            $ListeContent = $ListeContent + "<td>" + $WFStep +"</td>"
            $ListeContent = $ListeContent + "<td>" + $Weight +"</td></tr>"

            if($Weight -ne ""){
                $WeightReal = [double]$Weight
                $TotWeight = $TotWeight + $WeightReal
            }

        }
        $ListeContent = $ListeContent + "<tr><td></td><td></td>"
        $ListeContent = $ListeContent + "<td><b>Poids TOTAL (To)</b></td>"
        $ListeContent = $ListeContent + "<td>" + $TotWeight + "</tr></tr>"
        
        $TotWeight = [double]0
        $FolderDefsPF.Remove(2)
        $ListeContent = $ListeContent + "</table>"
    }
    
    Return $ListeContent

}