Okay, I got the month autofill renewal date working:
Renewal Date = If ( renewalinmonths="" ; "" ; Date (Month (Enddate) + (renewalinmonths); Day (Enddate); Year (Enddate)) )
still not there with coloring the text it red a month before renewal date...
You could use conditional formatting on the field with the calculation instead of a hard coded value. Then just apply the formatting from there.
Your conditional format expression might be:
Let ( T = Get ( CurrentDate ) ;
Date ( Month ( T ) - 1 ; Day ( T ) ; Year ( T ) ) < YourTable::Renewal Date
) // Let
I did it hard coded mainly because I show this info on many different parts of the database...I assumed it was best to hard code it since that field should always be calculating this date....I could be wrong.
As for the color coding, I DID use conditional formatting because I don't want it to color code everywhere.
( renewdate - 30 ) < Get ( CurrentDate) (format red)
( renewdate ) ≤ Get ( CurrentDate) (format red and underline)