10 Replies Latest reply on Oct 25, 2012 3:12 PM by Tim Webb

    Using the value of a variable from PDM in an SQL query

    Chris Bassett

      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?

        • Re: Using the value of a variable from PDM in an SQL query
          Raghavendra Bhagwan

          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.

          • Re: Using the value of a variable from PDM in an SQL query
            Tim Webb

            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

              • Re: Using the value of a variable from PDM in an SQL query
                Chris Bassett

                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).

                 

                Untitled.png

                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.)

                • Re: Using the value of a variable from PDM in an SQL query
                  Chris Bassett

                  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... 

                    • Re: Using the value of a variable from PDM in an SQL query
                      Tim Webb

                      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