Expiration date in colour

fhancko
3 yrs ago
19replies

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.

- Alain_Fontaine
- 3 yrs ago
- Reported - view
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.
- Mel_Charles
- 3 yrs ago
- Reported - view
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")
end

this 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
- CLD
- Valerio
- 3 yrs ago
- Reported - 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!
- CLD
- Valerio
- 3 yrs ago
- Reported - view
i mean i know how to do this via conditional styling but what is the formula for the days?
- CLD
- Valerio
- 3 yrs ago
- Reported - view
by the way... don't undertand why it indicates the period in days + 1:00
- Ninox partner
- RoSoft_Steven.1
- 3 yrs ago
- Reported - view
Try

days('Scadenza Fattura',today())

Steven
- CLD
- Valerio
- 3 yrs ago
- Reported - view
Great Steven thanks! Any idea of how to solve the conditional styling i wrote about ontop?
- CLD
- Valerio
- 3 yrs ago
- Reported - view
Solved! thanks!
- Ninox partner
- RoSoft_Steven.1
- 3 yrs ago
- Reported - view
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 if-then loop with the smallest number to work.
- Alain_Fontaine
- 3 yrs ago
- Reported - view
For more in this subject, see:

https://ninox.com/en/forum/technical-help-5ab8fe445fe2b42b7dd39ee7/remaining-to-a-date-and-style-5f68df84468c96426db36c70?post=5f6b718af6b8c07e9cae3def&page=2
- CLD
- Valerio
- 3 yrs ago
- Reported - view
Thanks Alain!
- saffron_car
- 3 yrs ago
- Reported - view
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
end

it doesn't works, I've tried also switch do with no results.

Any ideas where i'm wrong ?

Thanks a lot
- Fred
- 3 yrs ago
- Reported - view
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.
- saffron_car
- 3 yrs ago
- Reported - view
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 ?
- saffron_car
- 3 yrs ago
- Reported - view
Sorry and thanks for your help. Fixed it.
- NetSol Co., Ltd.
- Bernd_Krell
- 1 yr ago
- Reported - view
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)
- Fred
  
  1 yr ago
  
  Reported - view
  Bernd Krell I'm not sure how the code you posted is related to your question.
  
  You can try something like:
  
  let newText := if YesNO then "9999" else "Other" end; let bgColor := if YesNO then "Green" else "" end; styled(newText, bgColor)
  
  In Ninox you can use just the name of the Yes/No field and Ninox will assume you want to evaluate it as true/yes/1. You can use put:
  
  if YesNO = "Yes" or if YesNO = "true" or if YesNO = 1
  
  But that takes more typing. :)
- NetSol Co., Ltd.
- Bernd_Krell
- 1 yr ago
- Reported - view
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.
- Fred
  
  1 yr ago
  
  Reported - view
  Bernd Krell You can try this:
  
  let Countdown := if 'Perpetual License' then "9999" else days(today(),'End Date') end; let bgColor := if 'Perpetual License' then "Green" else switch true do case Countdown <= 7: "Red" case Countdown <= 30: "Orange" end end; styled(Countdown, bgColor)
  
  I think you just didn't put the code in the proper else places. I haven't tried out the code so I may be missing something, but you should get the idea.
  
  Lines 1 - 5 creates a variable called Countdown with an if statement. If Perpetual License is true then the formula equal 9999. If it is not true then it will figure out the date using the days command. The days command takes two dates and finds the difference between them and returns a simple numerical value.
  
  Lines 6 - 15 creates a variable called bgColor with an if statement. If Perpetual License is true then the variable is set to "Green". If it is not true then it uses a switch statement to figure out if Countdown is less than or equal to 7 it will set the variable to "Red" or if Countdown is less than for equal to 30 then the variable is "Orange".
  
  Line 16 then uses the style command to modify the variable Countdown based on the color set in bgColor.

Content aside

1 yr agoLast active
19Replies
1596Views
2 Following

Expiration date in colour

19 replies

Content aside

Overview