10 Replies Latest reply on Jun 29, 2017 3:50 PM by Tim Webb

    Where used in EPDM - Only if Un-suppressed

    Shawn Casebolt

      Does anyone know of a way to search EPDM for where used on a part / assy and have the results return ONLY assy's in which the component is un-suppressed?

       

      We have many sub assy's in which we have multiple parts (options) contained within them.  We leave the options suppressed that the customer does not choose, but do not delete them out.  This is because at any point during the build,  they can get added and it is very efficient to be able to just open the assy and un-suppress.

       

      Our problem comes when we have a problematic part, or and ECO change affecting a certain part and we want to search the vault to see what jobs contain this.  The where used function returns every assy / job.  Whether it is surpressed or not.

       

      Any help is appreciated.

       

      Thanks,

      Shawn

        • Re: Where used in EPDM - Only if Un-suppressed
          Martin Solem

          Hi Shawn,

          Have a look at Knowledge Base article S-064879.

          It depends on the selection you have made in the Contains tab view, viewing all configurations, or a specific configuration.

           

          Kind regards

          Martin

            • Re: Where used in EPDM - Only if Un-suppressed
              Shawn Casebolt

              Hi Martin,

               

              This references the contains tab...........I am wanting to look at the "where used" tab and only have the results show Assy's in which the part is un-surpressed.   So similiar to this........just opposite!

                • Re: Where used in EPDM - Only if Un-suppressed
                  Jason Capriotti

                  PDM just stores the file links in the assembly, its not aware of the suppression state within the assembly. I think your only option would be to delete the file in the assembly instead of suppressing it.

                  • Re: Where used in EPDM - Only if Un-suppressed
                    Martin Solem

                    Hi Shawn,

                     

                    Sorry, i was not paying attention and got fixed on Contains view.

                    Thing is, PDM is actually aware, but i could not get my SQL query which in turn could have been used as a PDM Report, to give me a useful result. This is a shortcoming of my SQL skills, and not the database.

                     

                    If you know a SQL wizard, there is information in tables Xrefs, XrefConfiguration, so i think it can be done.

                    XrefConfiguration has a value called SuppressedCount, that i believe holds the key.

                     

                    Kind regards

                    Martin

                      • Re: Where used in EPDM - Only if Un-suppressed
                        Steve Ostrovsky

                        I don't have time to write the query but here's what I think you'll need:

                         

                        Documents - DocumentID

                        DocumentsInProjects - DocumentID, ProjectID

                        Projects - ProjectID, Path

                        Xrefs - DocumentId,RevNr (need MAX Value), XrefDocument, XrefProjectID

                        XRefConfiguration - XrefID, ConfigurationID, XrefConfigurationID, SuppressedCount

                        DocumentConfiguration - ConfigurationID, ConfigurationName

                         

                        I'm intrigued by this query but I really have to get a presentation together for tomorrow, otherwise I would spend some time on it

                          • Re: Where used in EPDM - Only if Un-suppressed
                            Shawn Casebolt

                            I have no idea how to do anything in sequel / or reports.  Unfortunately that is a handicap I have....maybe someday though.  Thank you for the idea on whats needed!  I at least have a direction should we ever get anyone here who does have that skillset.  I understand having to do production work!

                              • Re: Where used in EPDM - Only if Un-suppressed
                                Tim Webb

                                You can try this query. I have run a few tests and it appears to function as expected. This is provided as-is without warranty and STRONGLY RECOMMENDED to run this query in a test vault before running in a production environment.

                                 

                                USE <your vault name>
                                ---------------------------------------------------
                                -- EXAMPLE Where Used script (KB solution S-059635)
                                ---------------------------------------------------
                                DECLARE @FilePath nvarchar(255)
                                DECLARE @FileName nvarchar(255)
                                DECLARE @ShowAllVersions int
                                
                                
                                --------------------------------------------------------------------------------------
                                --- *** Enter path from vault root and filename
                                --- Do not include the local view path and include the leading and trailing \
                                --- e.g. C:\MyLocalViews\VaultName\Folder\In\MyVault\ becomes \Folder\In\MyVault\
                                --- Can use SET @FilePath=N'%' if @FileName is unique in the vault
                                SET @FilePath=N<enter path name in single quotes without vault root folder ex. '\Projects\Project 1001\Engineering\'>
                                SET @FileName=N<enter a filename in single quotes ex. 'ENG-0706.SLDPRT'>
                                --- *** Set to 1 is same as selecting 'Show all versions' in Windows Explorer
                                SET @ShowAllVersions=1
                                --------------------------------------------------------------------------------------
                                
                                
                                DECLARE @SortOrder Int
                                DECLARE @Level Int
                                DECLARE @RowCount int
                                
                                
                                DECLARE @FileList TABLE(DocumentID int, FileName nvarchar(255), FolderName nvarchar(255),
                                RevisionNo int, LatestRevisionNo int, SortOrder nvarchar(200), Level int, SuppressedCount int)
                                
                                
                                SET @Level=1
                                INSERT INTO @FileList
                                SELECT D.DocumentID, FileName, Path,
                                CASE WHEN @ShowAllVersions=1 THEN NULL ELSE LatestRevisionNo END,
                                CASE WHEN @ShowAllVersions=1 THEN NULL ELSE LatestRevisionNo END,
                                1, @Level, xrc1.SuppressedCount
                                FROM Documents D
                                INNER JOIN DocumentsInProjects DIP ON DIP.DocumentID=D.DocumentID
                                INNER JOIN Projects P ON P.ProjectID=DIP.ProjectID
                                INNER JOIN XRefConfiguration xrc1 on xrc1.XRefID=d.DocumentID
                                WHERE Filename=@FileName AND Path LIKE @FilePath AND D.Deleted=0
                                
                                
                                WHILE 1=1
                                BEGIN
                                  SET @RowCount=(SELECT COUNT(*) FROM @FileList)
                                
                                
                                  SET @Level=@Level+1
                                  INSERT INTO @FileList
                                  SELECT DocumentID, Filename, Path, MAX(RevNr), LatestRevisionNo,
                                  SortOrder+RIGHT('00'+CAST(ROW_NUMBER() OVER(ORDER BY FileName, MAX(RevNr)) AS varchar),3),
                                  @Level, SupressedCount
                                  FROM
                                  (SELECT XR.XRefProjectID AS ProjectID, D.DocumentID AS DocumentID, D.Filename AS Filename,
                                  Path, XR.RevNr AS RevNr,
                                  D.LatestRevisionNo AS LatestRevisionNo, SortOrder, XRc.SuppressedCount as SupressedCount
                                  FROM @FileList FL
                                  INNER JOIN XRefs XR ON XR.XRefDocument=FL.DocumentID
                                  INNER JOIN Documents D ON D.DocumentID=XR.DocumentID
                                  INNER JOIN XRefConfiguration XRc on XRc.XRefID=XR.XRefID
                                    AND
                                    (
                                    ((XR.XRefRevNr=FL.RevisionNo OR Level=1) AND @ShowAllVersions=1)
                                    OR
                                    (XR.XRefRevNr=FL.RevisionNo AND @ShowAllVersions<>1)
                                    )
                                    INNER JOIN DocumentsInProjects DIP ON DIP.DocumentID=D.DocumentID
                                  INNER JOIN Projects P ON P.ProjectID=DIP.ProjectID
                                  WHERE FL.Level=@Level-1 AND XR.XrType=1 AND D.Deleted=0 AND xrc.SuppressedCount=0) T
                                  GROUP BY ProjectID, DocumentID, Filename, Path, LatestRevisionNo, SortOrder, SupressedCount
                                
                                  IF @RowCount=(SELECT COUNT(*) FROM @FileList)
                                  BREAK
                                
                                
                                  UPDATE @FileList
                                  SET SortOrder=SortOrder+'000'
                                  WHERE Level<@Level
                                END
                                
                                
                                -- Basic File List
                                SELECT Level, FileName, FolderName, RevisionNo, LatestRevisionNo, SuppressedCount
                                FROM @FileList
                                ORDER BY SortOrder, FileName
                                
                                
                                -- EXAMPLE getting other data about the files from Documents/Status tables
                                --SELECT Level, FolderName + FL.FileName AS FullFileName, S.Name AS CurrentStatus
                                --FROM @FileList FL
                                --INNER JOIN Documents D ON D.DocumentID=FL.DocumentID
                                --INNER JOIN Status S ON S.StatusID=D.CurrentStatusID
                                --ORDER BY SortOrder, FL.FileName
                                

                                 

                                Hope this helps,

                                Tim CEPA

                                Believe in The Q!