InfoPath Dev

Hi

I need to setup a form with a traffic light indicator. I am using MS IP 2003.

one date would be from a date picker and compared with todays date (in a repeating table)

<= 7 ( days) GREEN

8 to 30 (days) AMBER

>30 (days) RED

I have managed it in excel 2003 using conditional formatting, but I can't seem to find a way in MS IP, I think this may require some code.

can someone help ?

Something like this should work:

Condition

translate(field, ":-T", "") <= translate(xdDate:AddDays(xdDate:Today(), 7), ":-T", "")

Shade green

Condition

translate(field, ":-T", "") > translate(xdDate:AddDays(xdDate:Today(), 7), ":-T", "")  AND  translate(field, ":-T", "") <= translate(xdDate:AddDays(xdDate:Today(),30), ":-T", "")

Shade amber

Condition

translate(field, ":-T", "") > translate(xdDate:AddDays(xdDate:Today(), 30), ":-T", "")

Shade red

Hi Jimmy

many thanks for the quick responce, I've tried it out and it works. But I've tried to put in a date in the past and it stays green !

What I'm trying to do is to have the traffic lights compare what ever date you put in with today and it will conform to the traffic lights.I need it to update every time you open the form. Also I need to have another field with a " YES / NO " dropdown list and if it's "NO"  it works on the traffic lights , but if it's "YES" the cell stays white.

can this be done in infopath ?

many thanks

Kevan

What color do you want it to be if it's in the past?

Hi Jimmy

I need it to conform to the same rules, ie if <7 days green / 7 to 30 days amber and >30 days red. Even if someone puts the data into the form with a date in the past. This will be used for a overview of live set of information. I could supply my original spreadsheet with the conditioal formating or I could post the rules for you to look at. I think the addition of the drop down selection " YES / NO " sould only be an additional rule with and "AND" condition. ?

I've just tried this on work system and as they only supply me with MS IP 2003  "AddDays" is not supported ! AHHHHHH
Is there any way around this ? sorry to be a right royal pain !

The only way around the lack of the AddDays function is to write code that does something similar.  It would be some fairly simple code that calculates the number of days between today and the specified date, place that value in a field somewhere, and then use that number for your conditional formatting.  It's our general policy not to write code for people on this forum, but if you're willing to avail yourself to Qdabra's consulting services, we can work up a solution for you at a reasonable price.

Hi Jimmy,

many thanks for your help, I have found a way around this from another entry on this site. It calculates the difference between 2 dates and out puts this into another field I then take this value and use some conditional formatting to get my traffic light to work.