Question:
Can a Value Set, available on a Formula’s Values tab, be displayed on a table? The E3Browser will display the items in the following format:
And the Formula’s Values tab will be set up as follows:
Solution:
When a Formula object is added to Elipse E3, four new tables are automatically created to store its settings: TableName_Units, TableName_UnitData, TableName_Values, and TableName_ValueData.
To display the name of the value set as well as its value, you will need to join TableName_Values and TableName_ValueData. Additionally, you will need to transpose the data, and to do so we’ll use the command PIVOT.
First, let’s join the tables:
FROM Formula_Values
JOIN Formula_ValueData
ON Formula_Values.ID = Formula_ValueData.ValueID
After that, the data from TempID field will be converted into two columns:
FROM (
SELECT Formula_Values.Name as Formulas, Formula_ValueData.Value as Valores, Formula_ValueData.TemplateID as TempID
FROM Formula_Values
JOIN Formula_ValueData
ON Formula_Values.ID = Formula_ValueData.ValueID
)AS S
PIVOT (
MIN (S.VALORES)
FOR S.TempID IN ([1], [2])
) AS PVT
Therefore, the table is transposed with the name and values from the value set.
NOTE: In this example, we used SQL Server 2014 database.
