0

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.

19replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • 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.

    Like
  • 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

    Like
    • Valerio
    • CLD
    • Valerio
    • 1 yr 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!

    Like
    • Valerio
    • CLD
    • Valerio
    • 1 yr ago
    • Reported - view

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

    Like
    • Valerio
    • CLD
    • Valerio
    • 1 yr ago
    • Reported - view

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

    Like
  • Try

    days('Scadenza Fattura',today())

    Steven

    Like
    • Valerio
    • CLD
    • Valerio
    • 1 yr ago
    • Reported - view

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

    Like
    • Valerio
    • CLD
    • Valerio
    • 1 yr ago
    • Reported - view

    Solved! thanks!

    Like
  • 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.

    Like
    • Valerio
    • CLD
    • Valerio
    • 1 yr ago
    • Reported - view

    Thanks Alain!

    Like
    • MP
    • saffron_car
    • 1 yr 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

    Like
    • Fred
    • Fred
    • 1 yr 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.

    Like
    • MP
    • saffron_car
    • 1 yr 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 ?

    Like
    • MP
    • saffron_car
    • 1 yr ago
    • Reported - view

    Sorry and thanks for your help. Fixed it.

    Like
    • Bernd Krell
    • NetSol Co., Ltd.
    • Bernd_Krell
    • 1 mth 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)

    Like
      • Fred
      • Fred
      • 1 mth 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. :)

      Like
    • Bernd Krell
    • NetSol Co., Ltd.
    • Bernd_Krell
    • 1 mth 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.

    Like
      • Fred
      • Fred
      • 1 mth 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.

      Like
Like Follow
  • 1 mth agoLast active
  • 19Replies
  • 1396Views
  • 2 Following