PDM what do you use for <Private State> nag?

CAD CAD Admin (Install, PDM, etc)
1

All users are a bit different. Some are paranoid of loosing data and will check in the file every time they accomplish something. Some don’t bother checking in the first time until their done with it. Some respond to reminders, others are too busy. Anyway, I’d like to set up notification to users that have files in the elusive <private state>, Since it’s not actually a state there’s no way to do it out of the box. Which really stinks in my mind as it’s one of the worst “states” a file could be left in. Either delete it or check it in, right?

So what are other people using to nag those users that are “too busy” to clean up or just don’t think it’s that important for whatever reason? Hoping for an existing tool for this in PDM that’s not a cobbled box of work arounds. Thanks.
image.png

2

I periodically email those users with a list of the file counts. Nothing automated, just an output from sql. I can share the t-sql if you like.

3

I’m glad to see that I’m not the only one with this problem. I’m tired of reminding them. I can’t even get support from the managers - checking in takes too long and the engineers don’t have time to spend it checking in files every day.
o[

4

If management won’t help, I’m afraid there is little you can do. We send out a report from time to time from the DB with how many files the users have checked out and for how long.

Another thought is to stress to management that if a users hard drive dies, they potentially lose all checked out file edits unless you have backups.

5

Here’s some sql code to build a list

SELECT USR.FullName, COUNT(doc.LockPath) as 'Checked out files'
from Documents DOC
join Users USR on USR.UserID=DOC.UserID
where LockPath != '' and DOC.Deleted=0 and DOC.UserID != 1 and DOC.ObjectTypeID=1 --and LockDate < '2019-12-01 00:00:00.000'
group by USR.Fullname
go 


SELECT DOC.Filename
      ,USR.FullName
      ,DOC.LockDomain as Computer
      ,DOC.LockPath as Path
      ,DOC.LatestRevisionNo as Version
	  ,STA.Name as State
      ,DOC.LockDate
  FROM Documents DOC
  join Users USR on USR.UserID=DOC.UserID
  join Status STA on STA.StatusID=DOC.CurrentStatusID
  where doc.LockDomain != '' and DOC.Deleted=0 and DOC.ObjectTypeID=1 --and LockDate < '2019-12-01 00:00:00.000' --Object type 1 is normal file, 2 is virtual
  --order by LockDate
  order by USR.FullName,DOC.Filename
  go
6

That’s close to a sql command we have in a C# program that spits out an html doc, sort of a wall of shame. We included the lock date to get the longest checked out file and the mean days checked out. We started with API calls, first searched by files locked by users, that didn’t work very well. So switched to get the list of locked files from vault object and work that by user. That was better but we could not find any way to get the lock date from API. So we went to the trusty SQL query method and now have number of files locked, longest file locked for and mean days locked.

It crossed my mind to make some hideous traveling trophy that’s awarded to the worst offender.

7

I have a SQL script that runs each day that sends an email to users that have files checked out greater than 3 days. If you use this as shown below you will need to create a new table to log when emails are sent. Mine is listed as reporting.dbo.EmailsDelivered below.

/*
Email Checked Out Summary

This script looks for checked out files in PDM that have been checked out for a set amount of days
and emails a list with the location and description to each user.

Created by Jamey Evans 07/11/2022

*/

DECLARE
@MailProfile NVARCHAR(MAX),
@EmailSubject NVARCHAR(MAX),
@DescriptionVariableID DECIMAL,
@ConfigurationID DECIMAL,
@vault NVARCHAR(MAX),
@MinDays int;

– Customisation

– Database
SET @vault = ‘’
USE [your vault database]
– Mail Profile
SET @MailProfile = ‘SolidWorks_Email_Send’;
– Description VariableID - this is the variable ID (from the “Variables”
– table) that will populate the Description column
SET @DescriptionVariableID = 49;
– Default configurationID - the description must be pulled from a specific configuration
SET @ConfigurationID = 2;
– Min number of days file has been checked out
SET @MinDays = 3;

– For summary query
DECLARE
@Name NVARCHAR(MAX),
@Email NVARCHAR(MAX),
@UserID DECIMAL,
@CO DECIMAL,
@PS DECIMAL,
@T DECIMAL,
@OldestCheckoutDate DATETIME,
@NewestCheckoutDate DATETIME,
@LastEmailDate DATETIME,
@SendEmail BIT;

– For detail query
DECLARE
@Filename NVARCHAR(MAX),
@Folder NVARCHAR(MAX),
@Description NVARCHAR(MAX),
@PrivateState BIT,
@DaysCheckedOut NVARCHAR(MAX),
@LockDomain NVARCHAR(MAX);

DECLARE
@emailbody NVARCHAR(MAX);

DROP TABLE IF EXISTS #Summary;
CREATE TABLE #Summary
(
name NVARCHAR(MAX),
email NVARCHAR(MAX),
userID DECIMAL,
checkedOut DECIMAL,
privateState DECIMAL,
total DECIMAL,
oldestCheckoutDate DATETIME,
newestCheckoutDate DATETIME,
lastEmailDate DATETIME,
ID int
);

DROP TABLE IF EXISTS #Details;
CREATE TABLE #Details
(
userID INT,
lockDomain NVARCHAR(255),
[fileName] NVARCHAR(MAX),
folder NVARCHAR(MAX),
descr NVARCHAR(MAX),
privateState BIT,
daysCheckedOut INT,
tableID BIGINT
);


DROP TABLE IF EXISTS #IndividualDetails;
CREATE TABLE #IndividualDetails
(
[fileName] NVARCHAR(MAX),
folder NVARCHAR(MAX),
descr NVARCHAR(MAX),
privateState BIT,
daysCheckedOut NVARCHAR(MAX),
lockDomain NVARCHAR(255),
ID INT
);

INSERT INTO #Summary
SELECT
CASE WHEN CHARINDEX(‘,’, U.FullName) > 0 THEN SUBSTRING(U.FullName, CHARINDEX(‘,’, U.FullName)+2, LEN(U.FullName) - CHARINDEX(‘,’, U.FullName)-1)
ELSE SUBSTRING(U.FullName, 1, CHARINDEX(’ ', U.FullName)) END AS ‘Name’,
U.[Email],
U.[UserID],
Count([DocTypeID]) AS ‘Checked Out’,
Count() - Count([DocTypeID]) AS ‘Private State’,
Count() AS ‘Total’,
MIN(D.lockdate) AS OldestCheckOutDate,
MAX(D.lockdate) AS NewestCheckOutDate,
E.LastEmailDate,
ROW_NUMBER() OVER (ORDER BY U.UserID) as ID
FROM [Documents] AS D
JOIN [Users] AS U ON U.[UserID] = D.[UserID]
LEFT JOIN reporting.dbo.EmailsDelivered E ON E.UserID = D.UserID
WHERE D.[Deleted] = 0 and D.[UserID] <> 1 AND D.[ObjectTypeID] = 1 AND DATEDIFF(DAY, D.lockdate, GETDATE()) > @MinDays
GROUP BY U.Email, U.FullName, U.UserID, E.LastEmailDate;


– Get Detail Data
INSERT INTO #Details (userID, lockDomain, fileName, folder, descr, privateState, daysCheckedOut, tableID)
SELECT
Q.UserID,
Q.LockDomain,
Q.Filename,
P.Path AS ‘folder’,
VV2.ValueText AS ‘description’,
Q.PrivateState,
Q.DaysCheckedOut,
ROW_NUMBER() OVER (ORDER BY Q.UserID) AS tableID
FROM (
SELECT
D.DocumentID,
D.UserID,
D.Filename,
D.LockDomain,
CAST(COALESCE(DATEDIFF(DAY, D.LockDate, GETDATE()), 0) AS INT) AS ‘DaysCheckedOut’,
MAX(VV.RevisionNo) AS ‘MaxRev’,
CAST (
CASE
WHEN D.DocTypeID is null
THEN 1
ELSE 0
END AS BIT) AS ‘PrivateState’

FROM [Documents] AS D
LEFT OUTER JOIN [VariableValue] as VV
ON VV.DocumentID = D.DocumentID
AND VV.[VariableID] = @DescriptionVariableID
AND VV.[ConfigurationID] = @ConfigurationID

WHERE
D.[Deleted] = 0
AND D.ObjectTypeID = 1
–AND D.[UserID] = @UserID
AND DATEDIFF(DAY, D.LockDate, GETDATE()) > @MinDays
GROUP BY
D.DocumentID,
D.[Filename],
D.DocTypeID,
D.LockDate,
D.LockDate,
D.UserID,
D.LockDomain
) AS Q
INNER JOIN #Summary S on S.userID = Q.UserID
JOIN [DocumentsInProjects] AS DiP
ON DiP.DocumentID = Q.DocumentID
JOIN [Projects] AS P
ON P.ProjectID = DiP.ProjectID
LEFT OUTER JOIN [VariableValue] AS VV2
ON VV2.DocumentID = Q.DocumentID
AND VV2.ConfigurationID = @ConfigurationID
AND VV2.VariableID = @DescriptionVariableID
AND VV2.RevisionNo = Q.MaxRev;


– Temp for testing
–SELECT * FROM #Summary ORDER BY ID asc;
–SELECT * FROM #Details ORDER BY tableID asc;

DECLARE @SummaryRows INT = (SELECT COUNT(*) FROM #Summary), @i INT = 1, @DetailRows INT, @j INT = 1;

WHILE @i <= @SummaryRows
BEGIN
SELECT @Name=[name], @Email=email, @UserID=userID, @CO=checkedOut, @PS=privateState,
@T=total, @OldestCheckoutDate=oldestCheckoutDate, @NewestCheckoutDate=NewestCheckoutDate, @LastEmailDate=lastEmailDate
FROM #Summary
WHERE ID = @i;

– Check if we should send email or not
– Send email after 3 days then send another at 5 days; Send daily after 10 days
SET @SendEmail = 0;

IF coalesce(@LastEmailDate, ‘2000-01-01’) < @OldestCheckoutDate
BEGIN
SET @SendEmail = 1;
END;

– if files have been checked out >= 10 days then send email every day
IF DATEDIFF(DAY, @OldestCheckoutDate, GETDATE()) >= 10 AND @SendEmail = 0
BEGIN
SET @SendEmail = 1;
END;

– Otherwise if it has been 5 days since we sent the last email then send email
IF DATEDIFF(DAY, @LastEmailDate, GETDATE()) >= 5 AND @SendEmail = 0
BEGIN
SET @SendEmail = 1;
END;

IF @SendEmail = 1

BEGIN – Begin IF @SendEmail

– Summary operations

– Set email body
IF(@Description is null)
SET @Description = ‘-’;

SET @emailbody = '

8

I decided to employ this dispatch script going forward to reduce the files in the vault. When a file is added to the vault, it checks it in for your. I tried to get it to check it back out as well, but that caused a lot of issues when anything was open in SW.

As for the checked-out for forever files, the SQL solutions above look promising.
image.png

9

Have those who can’t be bothered have their entire access be managed by you and you only? If they need files, they ask you, you give them the file, they do their save and then give it back to you..?

After a few days of having to bother themselves to ask you for access to save files, they should most likely agree to take care of the file state management properly.

Edit: Geez, I’m late to the party :laughing:

10

I didn’t even consider going to this kind of extremes. We jumped from SE to SW and from NFS to PDM at the same time and the way it was done felt, to me at least, like replacing the outboard and transom in a boat. While it was in the water, not the marina, but like the middle of Lake Mich, with the usual 3 foot rollers. People already hate me for the mess, especially sales when every request started taking literally two to three times longer. I was in no position to hold up production on principle. Those kinds of rules, although they sound like fun, just weren’t practical. We couldn’t stop all progress until everyone understood how to use SW and PDM “properly”. Heck, back when I posted this, march '21, we were 16 months since go live and our SW & PDM task force was still trying to figure out our best practices.

It has just taken time, lots of time, for users to settle into the new system.

11

Perhaps then, identify times that they have been ‘bitten’ by the file being in the wrong state and show them the benefits to managing the states properly..?

Sounds to me like there are a lot of chances for things to get ‘lost’ from not getting saved because of improper file management, but I’ve never worked with PDM, so I could be wrong.

Do you have a graphic that explains the proper file management process so that people can understand it properly..?

12

Yep, we did this,. Some got it right away, with others it was similar to explaining why we don’t lean on the electric fence. But like explaining it to someone from the 15th century. The look was, “what is this electricity you speak of” == “What is a state? Is that like whether it’s checked in or not?”

Well, the file is saved to local disk it’s not easy to lose work. The problem has proved more to be in users not understanding what versions are than the state the file is in. Also, isn’t really a state, the file has been added to the vault but never checked in. I call it the twilight zone where the file is vaulted but it’s not; as far as user’s concerned it not in vault so no one else can access the file.

Oh yeah, screen shot of the workflow. The first workflow as originally set up turned out to be no good, WAY too complicated. Never should have tried to involve other departments. Since the actual part life process is not well defined the workflow of the file that represents the part became a rat’s nest. It became apparent that we were trying to model an undefined PLM process in a file management workflow and that was not going to work so we actually abandoned the first workflow and moved into a simplified one. All these on-the-fly changes in the implementation kept the users confused.

13

Sounds like fun, even though it is a lot of “problem solving”, I love that.

We’re in the process of standardizing our production, so we’re going from unique based project and turning it around to have products defined and reduce the project creation. We already had a section that was concidered “standard” for parts and sub-assemblies, so we’re also doing a little clean-up in there also and realigning a bunch of things such as item descriptions all while using existing projects to create Products that will then be used to “drive” future projects also(as a pack and go reference)

14

Thank you very much for sharing this. I am not very skilled at SQL and I was looking for something like this that would email individual users regarding checked out files. I am decent at augmenting scripts that have already been written though :wink:

I modified this to fit with my environment, ran it in my test vault and it worked GREAT.

Thank you, thank you, thank you. :smiley:

15