9 Replies Latest reply on Jun 22, 2014 6:46 PM by Jason Clarke

    Find documents where system Rev doesn't match document Rev

    Jonathan Gamble

      We appear to have some drawings in which the custom revision property doesn't match the EPDM "System" revision (I think something went wrong during the original import).  The drawings have the custom revision variable mapped to the title block, so the drawing may be at rev 'B', but the history shows the latest released rev to be 'A'.  I am trying to conduct a search to find all these drawings.

       

      Problem #1 is that it doesn't appear possible to compare two variables on a search card, so I can't do something like "Custom_Rev_Variable <> System_Rev"

       

      So my next attempt was to modify the 'version data' tab on the Complete Search Card to reference the "Revision No." search variable (with the little magnifying glass next to it), then run the search with 'A' in this field (without the quotes), and set the variable tab to look for "Custom_Rev_Variable" text-not-equal-to 'A' (again without the quotes). I cleared the "search all versions" check box so it would only look in the latest version.   Worst case I would only have to run this 26 times to check all possible rev values.

       

      The problem is that this doesn't work.  The search returns drawings with all different released revision levels, which suggests that the "Revision No." search variable doesn't just look at the latest released ("system") rev.

       

      So my question is, does anyone have a good way to find documents in which the revision doesn't match the system rev???  All comments very gratefully received!!

        • Re: Find documents where system Rev doesn't match document Rev
          Faur Arama

          I think that the only solution is to create SQL query.

          • Re: Find documents where system Rev doesn't match document Rev
            Brian Drifka

            Hi,

            My first post on the SW Forum .

             

            I noticed we have a lot of drawings in which, although the revision level on the drawing matches the SolidWorks revision property/variable, they don't match the revision in the EPDM 'system' (when you look into a drawing's history and see a revision level there).

             

            Here's what I did that seemed to help me identify the drawings with mismatched revisions (kind of a pain, but seems to work)...

            1. Use search card to search the vault for revision "A".
            2. Save the search results to excel (This list represents all the drawings at that revision level).
            3. Repeat 1 & 2 for all other possible revisions (B, C, D...).
            4. Combine the lists into a single spreadsheet. You now have lists of all drawings set at each revision level.
            5. I then manipulated the spreadsheet (using "Match", "Index", etc.) to determine if any of the drawing numbers were listed in more than one revision level list. For example, a drawing may be listed in the rev A list, as well as in the C list.
            6. These are the files that I'll need to clean up.
            7. Still trying to figure out how to do that easily (I'm reading something about using an Item Explorer to set the revisions manually).

             

            It seems to me that the search function serches for and keeps track of ANY revision change... whether a revision was saved in the SolidWorks drawing properties, or in the EPDM system (via workflow, etc.). I have drawing revisions that were rolled back in EPDM history, and others that were changed only in the drawing, etc. and it looks like the EPDM 'system' keeps track of ALL of the changes.

             

            Hope this helps and makes sense.

            • Re: Find documents where system Rev doesn't match document Rev
              Brian Drifka

              Jonathan,

              I've been working on this now for about a week and according to my VAR there is no way to do this.

              There is a "search variable" called "Revision No." that you can use in your search card that I hoped would do the trick (like you did)... however the VAR said that is not what it is for (curious what its for then???)

               

              He said the only way to get the system revision level is via SQL.

              Here's what I did (pain in the butt)...https://forum.solidworks.com/thread/82580

               

              select UserRevID, Counter

              from dbo.UserRevCounters

               

              select UserRevID, DocumentID

              from dbo.UserRevs

               

              select DocumentID, Filename

              from dbo.Documents

               

              A couple notes...

              1. I'm not a SQL guru. I knew nothing about it till I got started with EPDM.
              2. I can't guarantee this works for everyone.
              3. I was fickle... Occasionally it would come up with duplicates - same file with two rev leves. It seemed to have trouble with understanding the difference between "-" and "A" the most. Unfortunately I'm not sure why or how to decipher from the query which rev is most current (see #1 above).
              • Re: Find documents where system Rev doesn't match document Rev
                Jason Clarke

                Once you have correctly identified the SQL query you can convert it into an EPDM report (so that users don't have to go meddling in SQL management studio).

                 

                 

                The following is given as a suggestion of how to implement this, not as a production solution, and is not supported by me in anything other than a goodwill manner.

                 

                If  you start by trying to identify the "Variable ID" of the variables that you are interested in using a query like this (adjust the bold text as per your environment):

                 

                SELECT [VariableID]

                      ,[VariableName]

                  FROM [YOURVAULTDB].[dbo].[Variable] where IsDeleted = 0 and VariableName like '%rev%'

                 

                 

                After you have identified the variable you can use something like this to get the the mistmatched revs/variables (again replacing the bold content). This assumes  that youre saving your Revision Variable as a string rather than an integer:

                 

                SELECT distinct

                      [Filename]

                      ,t1c.Path

                      ,LatestRevisionNo

                      ,t3.Data

                      ,t4.ValueText

                  FROM [YOURVAULTDB].[dbo].[Documents]  t1 inner join

                  [YOURVAULTDB].[dbo].[DocumentsInProjects]  t1b  on t1.DocumentID = t1b.DocumentID inner join

                  [YOURVAULTDB].[dbo].[Projects]  t1c  on t1b.ProjectID = t1c.ProjectID inner join

                  [YOURVAULTDB].[dbo].[RevGenCounters] t2 on t1.DocumentID  = t2.DocumentID inner join

                  [YOURVAULTDB]].[dbo].[RevGeneratorList] t3 on t2.RevGenID = t3.RevGenID and t2.Counter = t3.CounterIndex inner join

                  [YOURVAULTDB].[dbo].[VariableValue] t4 on t4.DocumentID  =  t1.DocumentID and t4.RevisionNo = t1.LatestRevisionNo and t4.VariableID = YOURVARIABLEID where t4.ValueText <> t3.Data

                 

                 

                Good Luck!