Is there a way that I can get the value from a variable within Enterprise PDM (2012) into a query?
So, if I had a variable named CAD_PartNumber and wanted to use the value of that variable in a query, such as :
SELECT '' FROM Parts WHERE PartNum = CAD_PartNumber;
How (and can) this be done?
One thing I should note is that the values are being stored in an external database, so can this still be done?
Hi Chris -
I think you need to provide a full picture of exactly what you are trying to do, where is the data, what are the steps you want to perform and what result is expected...etc..
Joy
Hi Chris,
You can use the following query to populate a card list with available previously used variables.
Select Distinct ValueCache
From VariableValue
Where VariableID = (Select VariableID
from Variable
where Variablename like 'CAD_PartNumber')
And ValueCache != ''
order by ValueCache ASC
I added the variable name you mentioned.
Hope this helps.
Tim
Can this be modified to access the data from another database?
I'm attaching a picture to illustrate what I'm trying to accomplish. The screenshot is of a Folder template, where the user will select a Publisher, and then select a publication that is published by that Publisher. Both the Publisher and Publication information is stored in another database (outside of the database that was created with Enterprise PDM and the vault were installed).
So basically what I want to have happen, is when a value from the Publisher dropdown is selected, only Publications that are from that Publisher are shown in the Publications drop down.
I can do this with Card Lists, but at the rate the list grows and changes, I can't really edit myself (so I want to store it into a MS SQL database and have the PDM access that database--partially so that I can delegate the modification of the list to another person via a utilty that I write that they can run to edit the lists and only those lists).
Anyway, I have my database set up that there is a primary key in both tables (Publishers and Publications) called Publisher_ID. What I would like to see happen is that when a Publisher is selected, the PDM or SQL can get the Publisher_ID and then go through the Publication's table and find any publications with the same Publisher_ID, and return those names to the dropdown list in the PDM. I'm having trouble since these tables are not part of the PDM database and since the PDM can only return one column worth of results.
(By the way, ignore the notice at the bottom of the box... it's not crucial to what I'm trying to accomplish... this is my "rough draft" of the folder template for now... but the goal is still the same, having one list populate based on the values from another list.)
Hi Chris -
I think an add-in would work well for this case. Look at this post to see a sample posted in my reply.
https://forum.solidworks.com/message/145368#145368
Regards,
Joy
Tim: I found the solution you provided in another post somewhere by doing a google search, so I'm trying to modify it to my needs, to see if I can get it to do what I want done...
Hi Chris,
Yes, I borrowed this code to dynamically populate card lists but there does seem to be a bug because the SQL card list doesn't automatically update based on the schedule I've used.
Basically it sounds like you are trying to dynamically populate one list based on the selection of another. I've used Joy's code in a different add-in in the past. It is a great way to handle what you are trying to do.
Question: based on your comment about " but at the rate the list grows and changes, I can't really edit myself" one thing I am left curious about is "how does your data get put INTO your other sql tables?"
Tim
The data in the tables were manually entered so far. But eventually I'd like to create an add-in or application that can allow someone else without SQL experience to update the tables. At this point, it doesn't take up a significant amount of time, but if I happen to be gone for example, then the updates can't occur as we're doing them now.
I eventually want the Marketing people to be able to manipulate the tables related to their department without the risk of distrupting or damaging the vault database and that why I'm thinking either an add-in or stand alone application.
Ah, I see what you are after. A two way street.
Nice. Good luck and keep us updated on how this solution works and your progress on the updating SQL.
Hello Chris,
SELECT Variable.VariableName, VariableValue.ValueText, Variable.VariableID, VariableValue.DocumentID
FROM Variable INNER JOIN
VariableValue ON Variable.VariableID = VariableValue.VariableID
I would suggest using the using the query editor to add all the tables as its intutive and easy.
If the variable value is text the above query is good but you may have to add the othe variable values like text,date. yes or no.
Hope this helps !
Raghav.