I think that the only solution is to create SQL query.
I was secretly hoping this wouldn't be the solution!!
Can anyone explain how the Revision No. search variable on search cards works? I assume it looks at the "system" revision; does it look at all versions even if the "search all vresions" option is off?
The "system" revision and the Revision value on the data card are two separate things.
So when you import files to EPDM vault, the Revision value on the data card may read "B but the "system" revision may not.
Using EPDM search you can only search for Revision value on the data card, not "system" revision. To search for "system" revision you may need to use some SQL query.
when you import files to EPDM vault, the Revision value on the data card may read "B but the "system" revision may not
This is exactly what I am tring to search for! Most of our files came in OK, but we found a few last week that didn't match, so the question is "how many others are there"???
On the "Complete Search" card, there is an option to search "Revision No". This is a ystem variable, not a custom variable, so it is not looking at our custom revision number variable on the documents. The problem is that is doesn't appear to be looking at the "system rev" either. So what's it looking at???
I try to put "Revision No." on Complete Search card and uncheck "Search in all versions", the search result is correct as expected, the system does look into "system" revision of the latest version.
Here's how I did the test: (see the attached image):
Version 4 'has' revision B, C, D
Version 5 'has' revision E
So when the file is still at its latest version 4, if I enter B, C, or D in "Revision No." search field then in all three cases the search does return this file.
After being modified and checked in, this file has version 5 and revision E. So if I enter B, C, or D in "Revision No." search field then this file is no longer in the result list. Only when I search for revision E then I can see this file.
Btw, I'm on EPDM 2010 SP4.
History.jpg 114.5 KB
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)...
- Use search card to search the vault for revision "A".
- Save the search results to excel (This list represents all the drawings at that revision level).
- Repeat 1 & 2 for all other possible revisions (B, C, D...).
- Combine the lists into a single spreadsheet. You now have lists of all drawings set at each revision level.
- 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.
- These are the files that I'll need to clean up.
- 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.
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
select UserRevID, DocumentID
select DocumentID, Filename
A couple notes...
- I'm not a SQL guru. I knew nothing about it till I got started with EPDM.
- I can't guarantee this works for everyone.
- 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).
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):
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:
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