This article will show how to set up an SQL Card List on a Data Card that automatically updates data from an SQL source.
SOLIDWORKS PDM Professional card lists on data cards allow control of data to ensure it is consistent and searchable. SQL-based Dynamically Controlled card lists allow the choices on one list to be determined by the selection on another list while updating the list automatically from any database.
This article explains how to set up SQL-based card lists and use them in Dynamically Controlled Data Card controls. The example we will use is accessing the PDM Vault’s Groups and Users so we can select a Group and then have a list of Users within that Group.
Note: Additional information can be found in PDM Online Help under “Card Lists | Creating Dynamically-controlled Card Lists.”
Determine The Tables to Use
The first step will be determining which tables we need to get data from within the vault database.
Note: If you are unfamiliar with a PDM Vault Database schema, please contact CADimensions to discuss what data you need to access so we can ensure you access the correct tables and make the correct connections between them if needed.
As shown in the diagram below, we need the GroupMembers, Groups, and Users tables.
From this diagram, we can extrapolate the following SQL script to get the data we need.
SELECT Users.FullName
FROM ((GroupMembers
JOIN Groups ON GroupMembers.GroupID = Groups.GroupID)
JOIN Users ON GroupMembers.UserID = Users.UserID)
WHERE Groups.Groupname = 'Administrators';
In this script, we get the FullName from the Users table while performing a JOIN between the three tables linking the GroupID and UserID fields. Then, we specify the Groupname we are looking for with the WHERE function. We will change this string for each group’s card list.
Implementing our new SQL script in a PDM Card List
The first step will be to open the PDM Administration tool and log into the vault.
Once logged in, navigate to the “Lists” category, expand it, right-click on “Card Lists,” and select “New List.”
After the new card list opens, the fields must be filled out. The list below explains each entry from the image below.
- Name of the list you are creating.
- Select “From SQL Database.”
- Enter the SQL command in this window. TIP: Use SQL Management Studio to test this script if possible.
- SQL Server Name.
- Database Name.
- SQL Login must be an account with “Public” permissions to that database.
- SQL Login Password
- Click “Test” to ensure the script works correctly and values are shown.
- Refresh type, leave on “Periodic refresh every.”
- Refresh interval in minutes.
After this first list is created, the same steps must be taken for each group in PDM.
Create the variables and card controls and set control options
To get started, create a variable for each of the controls, as shown below.
Now that the variables have been created, a new or existing card must have two Labels and Droplists added. Note: The card in this example is linked to text (txt) files.
The labels are given values according to the information shown. The Droplists must be attached to the correct variables for each.
The Group Droplist control will be configured to connect to the “Group” variable and use a “Special value:” called “Group list.” This will list all the groups that exist within this vault.
The Username Droplist control will be configured to connect to the “Username” variable and use the “Controlled by variable” Items option.
Next, click the Details button under the option.
Within the “Controlled by variable” dialog, we need to configure the three sections.
The “Variable” (1) will be the same variable used in the first Droplist we created on the card. The “Variable Value” (2) will be the names of the groups in the vault. The “Cardlist” (3) will be the card list that we created previously that matches the Variable Value.
Once we make all the selections and enter values, click OK.
Finally, click the Save icon in the top left menu and close the Card Editor.
Testing functionality
Open and log into the local view (PDM in Explorer) to test the functionality. Select a text (.txt) file and the new data card will show.
Text the lists by selecting multiple combinations between the two.
Once you have tested the lists, select from each and click Save.
If you opted to link the variables to custom properties, open the document and ensure that these were written to the file.