Tim's Blog

SQL can do what???

Written by Tim Webb | Nov 12, 2021 5:00:00 PM

Hey friends!

This week's blog has a lot to do with SQL server. I'm sure you've always heard to be an SQL DBA, uber .NET programmer, Microsoft MVP, and lots of other credentials to be the best PDM guru. 

Forget all that. It's a barrier to being the best PDM guru believe it or not. Hear me out, I'm not saying those qualifications and credentials are not valuable, not in the least. What I'm getting at is they are valuable but to be the best PDM guru, you need 2 things. That's right, only 2...and then a bit more. But it always begins with your 2 ears. That's right! Listening to your stakeholders and team. This doesn't mean you have to always "DO" what they say or recommend, but this provides you the scope of what the solution needs to be. This is how I roll every day of the week and have found this approach to be very fruitful.

When defining a new solution in the PDM environment, there are limitations and roadblocks you will encounter so be prepared but most of what you will need to do daily, is right at your fingertips.

We had a customer ask Tim about how to quickly list all the variables on a huge datacard because there were fields being referred to during a transition that weren't getting set but the card was so large, they couldn't find it. Keep in mind, the magic number of datacard controls should be less than 50 or your PDM vault performance can suffer.

When deciding to implement your PDM system, your admin installed variables in the datacards, folder cards, search cards, and template cards that should be very valuable to your company and your data strategy. Each variable stores what is called metadata about each file in your vault. Some datacards can grow to be very complex and contain multiple tabs with buried controls so listing all the variables on your datacard is not easy.

That is, unless you have the query to accomplish it!

This script lists all the variables in use on the datacard called 'eQ CAD Card' in our PDM vault.

select v.VariableName
from cardcontrols cc
inner join cards c on c.CardID = cc.CardID
inner join Variable v on v.VariableID=cc.VariableID
where c.CardID=
(
select c.cardid
from cards c
inner join projects p on p.ProjectID = c.ProjectID
where c.CardName='eQ CAD Card'
)

Change the last line where it shows eQ CAD Card to the name of your data card enclosed with single quotes. You can then copy that list of variables into Excel to remove duplicates and use the list however you need.

We just published a Q! Tip on how to do this so take a look at that video when you have 12 minutes. You will be glad you did. Our use case for this SQL Script is unique and our customers find it pretty useful.

Stay tuned for future Q! Tip wrap ups.

As always, reach out with any questions, we are happy to help! Schedule a meeting with Tim, or shoot us an email.

Believe in The Q!