On a FPMI trial install; I have displayed a table – Line number, Item code, case count – I am using FSQL to update the SQLrows and include the timestamp. I would like to add a green check or red X (in the table) to indicate if the data is up to date. Using the timestamp, how can I make this work? I have a picture of an x or check being displayed on the table - I tied it to the quality_code - but I want to tie it to the timestamp.
To do this, you would use a CASE statement in your SQL query to convert the timestamp to a number (0 or 1, representing ‘up to date’ or not), which can then be represented as an image using the Table component’s column customizer.
I’m a little fuzzy on the logic that you want to use to determine if data is ‘up to date’ or not. For sake of example, lets suppose that if the timestamp is older than 5 minutes, you consider the row to be not up to date.
The syntax of the CASE statement depends on what kind of database you’re using. The following example query would work on a Microsoft SQL Server database:
SELECT LineNum, ItemCode, CaseCount, CASE WHEN DATEDIFF(MINUTE, t_stamp, GETDATE()) < 5 THEN 1 ELSE 0 END as UpToDate FROM MyTable
This would give you a column called UpToDate that would be 1 if the value t_stamp was less than 5 minutes old. You would then translate the values of this column into images using the Table Customizer.
Hope this helps,
That’s exactly what I was looking for!