SQL Mashups in PDM data cards

Using SQL to leverage combinations of data in PDM Drop Lists

Using SQL to dynamically populate data card lists is a valuable tool when configuring a PDM vault. In a previous blog post, we highlighted the immediate benefits of this approach. However, there are situations where the data retrieved for the list might not fully meet the user's needs. This limitation arises because the SQL code typically retrieves a single variable value. By employing more advanced SQL techniques, it becomes possible to display data from multiple variables in the card list, enhancing data clarity.

For instance, consider a scenario where you need to include both a Specification Number and its corresponding description in a dropdown list. This is essential because some users may recall the numbers, while others prefer the descriptions. Combining both variable values in the list can be easily achieved using SQL. Below is an example of how to accomplish this:

Select
CONCAT(LEFT(Documents.Filename, CHARINDEX('.', REVERSE(Documents.Filename)) - 1), ' / ', VariableValue.ValueText) AS CombinedValue FROM Documents
INNER JOIN
Status ON Documents.CurrentStatusID = Status.StatusID
INNER JOIN
VariableValue ON Documents.DocumentID = VariableValue.DocumentID
INNER JOIN
Variable ON VariableValue.VariableID = Variable.VariableID
WHERE
Status.Name = 'Product Specification - (Approved)'
AND Variable.VariableName = 'Description';

Here is another way in SQL to achieve the same results.

WITH CTE AS ( SELECT LEFT(D.Filename, CHARINDEX('.', REVERSE(D.Filename)) - 1) AS FileNameWithoutExtension, VV.ValueText AS VariableValue FROM Documents AS D
INNER JOIN Status AS S ON D.CurrentStatusID = S.StatusID
INNER JOIN VariableValue AS VV ON D.DocumentID = VV.DocumentID
INNER JOIN Variable AS V ON VV.VariableID = V.VariableID
WHERE S.Name = 'Product Specification - (Approved)' AND V.VariableName = 'Description' )
SELECT CONCAT(CTE.FileNameWithoutExtension, ' / ', CTE.VariableValue) AS CombinedValue FROM CTE;

Below is the result that is displayed in the list.

Results of a two variables being returned to a card list