I have an old datasource that seems to have a number of db records that have no associated files. Is there a way to use Powershell to remove this database records?
thanks,
Kirk
Kirk, as always, it is best not to try to "clean up" a ProjectWise database directly.
Assuming that you mean that you have a number of ProjectWise documents that do not have a file assigned, is this because they are abstract (or placeholder) documents or because the assigned file does not exist in the associated storage location?
If they are "abstract" documents, you should be able to create a saved search to find them all, and then use that saved search in a PS script and delete each of the documents. You might find it easier to do with the "Search Builder" tool and specify "General Properties", "File name" and use "NULL" for the relation type for the value.
If they are documents with "missing" files, if you haven't done so already, take a look at Brian Flaherty's PowerWiseScripting.blog post named "How To: Report on Missing Files Within ProjectWise Using PowerShell" https://powerwisescripting.blog/2018/10/24/how-to-report-on-missing-files-within-projectwise-using-powershell/
Once you have identified the documents that you want to delete, you can use Remove-PWDocuments to delete them even if they have missing files.
Dan WilliamsSolution ConsultantBentley Systems, IncorporatedPortland, OR, USA (Pacific Time UTC-08:00)
Dan and Kevin,
OK I wrote a script that (1) generates a spreadsheet of orphaned docs, (2) runs Remove-PWDocuments do delete the files. I tested on one file, and the script said it was deleted, but when I went back to PW the file record was still there. I still can't delete the orphaned record.
It looks like you are trying to delete the document with a missing file using ProjectWise Explorer. It doesn't surprise me that it didn't work as it is a WAD and is intended to indicate that something is amiss with the associated storage area and needs attention.
I tested the scenario of deleting a document with a missing file and it worked, but I did it with the Remove-PWDocuments Cmdlet found in PWPS_DAB.
In Kevin van Haaren's follow-up, he mentions the same "workaround" that I mentioned, i.e. just place a file with the same name as the missing one in the appropriate storage location, and he suggested another potential workaround where you replace the file with a "dummy document", also with PWPS_DAB. That probably will work as well as long as the storage area is valid and available.
The "WAD" behavior shouldn't be overlooked. The fact that you have documents that have missing files should be investigated if you haven't done so already. Sometimes this happens when "things change", i.e. a new server, organizational changes, etc. and a storage location that contains files that aren't used much, or not at all, gets "lost" along the way, but this typically results in all of the documents with files in that storage location to be "missing" as well.
Dan, I used Remove-PWDocuments as well, and that didn't work for me.
I also placed a file with the same name in the storage area, and that didn't help either. Dan Dimitrio in support has a video (have an open ticket).
WAD? Not familiar with that acronym.
Works as Designed
OK, I'll let Dan Dimitro help you sort it out.
FWIW, Here's what versions I used to test the approach I offered:
ProjectWise Explorer v10.00.02.265
ProjectWise Integration Server v10.00.02.265
SQL Server 2014
PWPS_DAB v1.12.1.0 (and older version), but I verified that it also works with version 1.24.4.0.
Dan and Kevin - thanks, here is what finally works. (other stuff above it about logging into a datasource and etc)
## Should return the target datasourceGet-PWCurrentDatasource$CurrentUser = Get-PWCurrentUser$WorkDir = Get-PWUserWorkingDirectory -UserName $CurrentUser.Name #$MissingDocs = Get-PWMissingStorageAreaFiles -FolderPath '<project stuff>' -UseAdminPaths -Verbose
Get-PWStorageAreaReport -FolderPath '<project stuff' -OutputFolder 'c:\ps\data' -UseAdminPaths -SkipFileSizeCheck -Verbose
$OrphanDocs = Get-TablesFromXLSXWorkbook -InputFileName 'c:\ps\data\StorageAreaReport_20210113_132753.xlsx' -Verbose
forEach($PWDoc in $OrphanDocs.PWFolder){ write-host $PWDoc $PWD = Get-PWDocumentsBySearch -FolderPath $PWDoc
if(-not($PWD -eq $null)) { Update-PWDocumentFile -InputDocuments $PWD -NewFilePathName 'c:\temp\scanrefs.log' -Verbose -ErrorAction Continue write-host $PWD Remove-PWDocuments -InputDocument $PWD -Verbose -ErrorAction Continue }}