Struggling with date format in table transformer

Andy Knott

I understand that table transformer can be used to format colours of rows when an expression is evaluated. and I'm successfully understanding how to do that with string data - but I'm struggling to understand te approach when the field being formatted contains a date element.

So the problem is to evaluate a field, my intent is to check the data field and then highlight those rows where the date has been missed 

When  I run this code, what happens is the date field is populated with a 13 digit integer rathe than the original date value that was in the cell - I have confirmed that the table transformer and the table have the same date structure, ie d m yy.

Any pointers will be appreciated as I'm finding this rather frustrating. 

 

FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Owner' = "AA" THEN "#FFA07A"
WHEN T1.'Owner' = "Test" THEN "#ADD8E6"
ELSE "#D3D3D3"
END
+ "}" + T1.'Task' + "{cell}")
AS 'Task',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Owner' = "AA" THEN "#FFA07A"
WHEN T1.'Owner' = "Test" THEN "#ADD8E6"
ELSE "#D3D3D3"
END
+ "}" + T1.'Dates' + "{cell}")
AS 'Dates',
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Owner' = "AA" THEN "#FFA07A"
WHEN T1.'Owner' = "Test" THEN "#ADD8E6"
ELSE "#D3D3D3"
END
+ "}" + T1.'Owner' + "{cell}")
AS 'Owner'
FROM T1

Stiltsoft support

Hi @Andy Knott ,

Please add the FORMATDATE function before calling the 'Dates' column:

...
FORMATWIKI("{cell:bgColor=" +
CASE WHEN T1.'Owner' = "AA" THEN "#FFA07A"
WHEN T1.'Owner' = "Test" THEN "#ADD8E6"
ELSE "#D3D3D3"
END
+ "}" + FORMATDATE(T1.'Dates') + "{cell}")
AS 'Dates'
...

This query should be working fine.