I am facing issue in accessing template card variables in SQL query. I want to retrieve data from SQL database based on selection in one of the data members in the template card of EPDM.
Any help would be appreciated.
Unfortunately it's not possible to use PDM variable values dynamically in the SQL query. The closest I can get to a proper solution (without custom add-ins) is to create different SQL lists, one list with a query to list all project managers, and then create lists for each individual project manager which lists all of their projects. Then combine the two with the 'Controlled by...' option in the datacard.
This does take some manual labour and maintenance by a PDM admin whenever a new project manager starts. If you want to use API for doing all the work for you, you might want to check Knowledge Base solution S-044332 which describes the principle on how to do this.
Hi Sonali, I'm just giving my interpretation of your question here: You have a selection field on the (template) card, for instance a dropdown list. The value chosen in this list will define which SQL data is displayed for another field, is that correct? If so:
1. Create a dropdown list with possible choices for the first list, for instance:
2. Create the appropriate lists for the SQL queries
3. Create a second droplist on the datacard in which you select 'Controlled by variable', create the link to the corresponding SQL list there:
Hi Martien, I wanted the 'Choice' dropdown list to be dynamically retrieved from SQL database as well. That can be done by creating list and through SQL query but in that case, it wont be possible to associate rest of the fields in the template card to the database and retrieve values in the data card based on user selection through 'Controlled By Value'.
Hi Sonali, perhaps your code to retrieve the data from a SQL table is not quite correct, can you post it here? What is the list trying to retrieve?
Hi Prasad and Martien, I am attaching some screenshots to make my question more evident.
I am populating the Project Manager combobox-droplist from list retrieving data from SQL Server.
What I want is to get the values of Project Number as well as Date of creation from SQL database based on item selection in Project Manager. For instance, if we select BPK, it should populate the Project Number drop-list automatically with P_1 and P_3 from the database and depending on the selection in both the Project Manager and Project Number, it should populate the Date of Creation editbox in the template card from SQL database.
Hi Martien and Prasad, thanks a lot for your help. The API solution mentioned by you (S-044332) is exactly what I needed
Hi Martin, I have one more query. I am not able to populate data in Project Number (please refer to the screenshots I attached before) based on selection of Project Manager. I want Project Number to be a dropdown not an edit box so that it can display multiple values. For instance, if suppose we select 'BPK' as Project Manager, it should automatically populate Project Number with P_1 and P_3 from the database. This addin is working when we have a single dropdown and based on it we are populating the editboxes but I am stuck on populating the other dropdowns based on the first selection.
I'm afraid I don't have the knowledge to help you with this in detail. In the code there is a part which creates the SQL query to retrieve the project numbers, this part of the code should be edited to populate an array of the returned values. You can then use these in a GetControlVariableList method and use it in a droplist if I'm not mistaken.
Check the API Help for a starting point on the method here: http://help.solidworks.com/2018/english/api/epdmapi/epdm.interop.epdm~epdm.interop.epdm.iedmcardcontrol7~getcontrolvariablelist.html
Hope this helps!
Thanks Martien, I will try it out .
What Martien says is correct. You can only use a SQL list to return a single set of values from a table.
If you want dependent lists, it's best to set up separate lists in PDM and then use controlled by variable.
Retrieving data ...