FactoryPMI’s Table component is a popular way to efficiently view all sorts of data. The Table component’s columns are easilly configured do display data in a variety of ways, including mapped row background colors, images, progress bars, etc. However, sometimes a user will want to customize the look of a column that isn’t built into the Table component’s customizer.
In almost all cases, these customizations can be achieved through a combination of advanced SQL queries, and the table cell’s natural ability to display HTML. For this tutorial, we will walk you through setting a table column to display a number [color=red]red[/color] if it is over some value, and [color=green]green[/color] otherwise. For this example, suppose we have following data table in the database:
To display this data in a FactoryPMI Table component, we would bind the data property of the table to the SQL query:
SELECT LotNum, Weight FROM LotTableThis query would cause the table to display the data much like it is shown above.
Now we want to display the weight column red if the weight is over 60, and green if it is under 60. In order to change the font color of the cell, we want our SQL query to return HTML text instead of a number. For example, instead of 35.83929 being sent to the table, we would want: 35.83929
[color=olive]NOTE: Readers familiar with HTML may by wondering: Hey, where are the closing and tags?. The answer is: you don’t need them, and we omit them here because they just add verboseness. FactoryPMI is very forgiving in its HTML syntax.[/color]
Lets start by modifying our query to return HTML that turns all weights green. To do this, we need to concatenate the string '’ with our numeric column ‘Weight’. For various databases, concatenation syntax is different. MySQL and PostgreSQL syntax are given here. Please see the fantastic SQL reference website SQLZoo for syntax for your database. The queries would be:
PostgreSQL:
SELECT LotNum, '<HTML><FONT COLOR=green>' || Weight AS FormattedWeight FROM LotTableMySQL:
SELECT LotNum, CONCAT('<HTML><FONT COLOR=green>', Weight) AS FormattedWeight FROM LotTableThis query will return the weight column with the text on the front. The following show the data that the Table component recieves from the database, and an approximation of the table that will be displayed:
FactoryPMI recieves:
FactoryPMI displays:
You should notice that two things have changed from the original table. The numbers are green, and they are aligned left. They are aligned left because that is the default alignment for text, which is now the type of the column (instead of a numeric type). You can use the built-in column customization to change the horizontal alignment of the FormattedWeight column to align right.
We are now ready to insert the logic for the color changing feature. What we want is for the query to prepend the text of the Weight column is over 60, and otherwise. To do this, we use the SQL CASE statement. The queries are:
PostgreSQL:
SELECT LotNum,
CASE WHEN Weight > 60
THEN '<HTML><FONT COLOR=red>'
ELSE '<HTML><FONT COLOR=green>'
END || Weight AS FormattedWeight FROM LotTable
MySQL:
SELECT LotNum,
CONCAT(CASE WHEN Weight > 60
THEN '<HTML><FONT COLOR=red>'
ELSE '<HTML><FONT COLOR=green>'
END, Weight) AS FormattedWeight FROM LotTable
These queries give you the following result (assuming you aligned the FormattedWeight column to the right):
FactoryPMI recieves:
FactoryPMI displays:
Now, you may be thinking: Thats great, but I don’t want to see all of those decimal places!. Unfortunately, because FactoryPMI’s table component is now recieving text instead of a number, it’s built-in number formatting doesn’t work. Luckilly, all databases have number formatting functions! Suppose you wanted to see exactly two decimal places. The queries would become:
PostgreSQL:
SELECT LotNum,
CASE WHEN Weight > 60
THEN '<HTML><FONT COLOR=red>'
ELSE '<HTML><FONT COLOR=green>'
END || ROUND(Weight,2) AS FormattedWeight FROM LotTable
MySQL:
SELECT LotNum,
CONCAT(CASE WHEN Weight > 60
THEN '<HTML><FONT COLOR=red>'
ELSE '<HTML><FONT COLOR=green>'
END, FORMAT(Weight,2)) AS FormattedWeight FROM LotTable
This will result in the following:
FactoryPMI recieves:
FactoryPMI displays:
Tada! We’ve successfully used SQL and HTML to achieve row-specific formatting in our table. Note that you could use this same technique to make text bold, underlined, italic,
larger,smaller, etc.