Expiration date in colour
Hello. I am at loss. Been trying many configurations but can't get the fields work. I have a table with one field called expiration date. I would like the field expiration date to be displayed in RED colour if it is after the current day
For instance if the expiration date field contains 01.01.2020 and we are the 02.01.2020, the field would be in RED colour. How do we do that, please :) Many thanks.
19 replies

As far as I understand the matter, it is not possible to set the colo(u)r of a data field. it is only possibke to set the style of a function field, because the formatting is done by including the function styled() in the definition of the field.
So, if the field you want to highlight is already the result of a computation, setting its style is easy. If it is a data field, probably in this case a "Date" field, the only thing you can do is to define an other, "Function" field, near to it, to contain, for example, a countdown of days remaining, with a change of colo(u)r if some condition is met.
If someone knows better, I would be pleased to learn something.

However Alain is correct you can't set color for an edit/data cell (apart from calendar colors!)
I have have a sim function when looking at a shipping date
so as a work around what i did was
Create a formula field (called ShipDate2) which has a formula attached to it to compare to 'Ship Date'
formula is :
if 'Ship Date' < today() then color("red")
endthis will color the forumla field (shipDate2) to give you cell in solid red fill
Not Ideal but works for me !
Today no RED
Yesterday  RED!
also shows up in table view

If i'm using the conditional styling for the same thing but i want to have more than 1 condition lets say:
1) If Due Date (formula field set counting the difference of days from the due date and today) is less than 10 day color orange
2) If Due date is less than 7 days color yellow
3) If due date id less or equal to 3 days color red
how can i do this??
Thanks!

i mean i know how to do this via conditional styling but what is the formula for the days?

by the way... don't undertand why it indicates the period in days + 1:00

Try
days('Scadenza Fattura',today())
Steven

Great Steven thanks! Any idea of how to solve the conditional styling i wrote about ontop?

Solved! thanks!

Something like this i guess:
let due:= days('Scadenza Fattura',today());
if due<=3 then "red" else if due<=7 then "yellow" else if due<=10 then "orange" end
this might need some tweaking (no time to try it myself now)but i think you must start youre ifthen loop with the smallest number to work.

Thanks Alain!

Hi everyone, I'm lost in a "glass of water"; please would you mind to give me some help regarding a similar case?
I have a table with a text field (DRUGS), a date field (EXPIRATION DATE) and a formula field where I'd like to create a formula that, based on the closeness of the exp.date, will contain a text: "orange" if the exp.date is close to the date or 30 days before. "red" if expired. "ok" if the exp date is more than 31 days far.
Ex:
let xscad := 'EXPIRATION DATE'  today();
xscad + if xscad <= 30 then
"orange"
else
if xscad >= 0 then "red" else "ok" end
endit doesn't works, I've tried also switch do with no results.
Any ideas where i'm wrong ?
Thanks a lot

Hi MP 
go back a page and see Steven's response. Your use of (date field  today) gives you a number counted in miliseconds since Jan 1, 1970. if you use the days(start,end) function then you will get human readable numbers.

HI Fred, thanks for your reply.
My problem is that the code doesn't work to show the "color" i want. The numbers of the days are correct.
My code gives me back correctly the "red" but always the "orange" for the rest. Never the "ok".
How can i write in a right way that if a date is included in a date range ?

Sorry and thanks for your help. Fixed it.

Hello. I have used the code below.
But now I have an additional requirement which I cannot get working.I have a yes/no field and if "yes" is selected, I do not want the end date to be calculated but just fill in 9999 and set to green.
I have tried various ways with if/then but not working.
If anyone know it would be awesome if you could give me a hint.
let Countdown := round((today()  'End Date') / 86400000);
styled(text(Countdown) + " days", switch true do
case Countdown <= 7:
"Red"
case Countdown <= 30:
"Orange"
default:
"Green"
end) 
Thanks a lot.
I added your code to mine and it looks now like this.
let newText := if 'Perpetual License' then "9999" else "Other" end;
let bgColor := if 'Perpetual License' then "Green" else "" end;
styled(newText, bgColor)
let Countdown := round((today()  'End Date') / 86400000);
styled(text(Countdown) + " days", switch true do
case Countdown <= 7:
"Red"
case Countdown <= 30:
"Orange"
default:
"Green"
end)The error I get is
End expected: let at line 4, column 3
Anyway it should an If/Then statement, shouldn't it?
If YesNo (Perpetual License) is yes then enter text "9999"
else calculate the days to today.
Content aside
 2 yrs agoLast active
 19Replies
 1712Views

2
Following