I have a date field which is a projected project end date. I have a calculation which calculated weeks to that end date. I have a 2nd calculation which highlights the row a color depending on how many weeks are left. My problem is that we allow projects to not have a projected end date. Both the records that have "0" weeks left (Basically this only applies to records with projected end date equalling current date) and no date are showing the same status. I would like the records that have no date to not have any row coloring.

Is there a way I can differentiate "empty" fields vs. "0" fields.

Cheers.

Michael

FileMaker Version: 6

Platform: Mac OS X Panther

Without seeing your specific calcs, all I can tell you is use the IsEmpty() function along with a Case or If statement.

Here is how things are setup:

Sign off date - date field

RowHighlightGlobal - Container, 2 repetitions

Weeks till sign off - If(Sign off date="","", (Sign off date-Status(CurrentDate))/7)

Row Shading -Case(Sign off date< 2 and Sign off date >=0, GetRepetition(RowHighlightGlobal,2), WeeksToSignOff < 0,GetRepetition(RowHighlightGlobal,1),"")

In this example, records with no date are being shaded with the 2nd repetition of RowHighlightGlobal. Since it is blank and not "0", I would like it not to show any color. I have worked around this by setting the calculation above to "Sign off date>0". Doing this only creates a problem when Sign off date is equal to the current date.

Thanks.

Michael Allison

Just make it check for an empty Sign off date first: Case(IsEmpty(Sign off date), "", etc. etc.

If you don't want there to be any result in a particular situation, it's a little cleaner to use the 'not' test instead. Case(not IsEmpty(Sign off date), Sign off date-Status(CurrentDate))/7). Note also that 'not IsEmpty( )' could be replaced by 'Length( )'.

Got it. Thanks for your help.

Michael

Edit: While looking for something else, I stubbled across this on FM's support site. Sometimes you have to go to the source.

Calculating a Blank instead of a Zero or ?

http://filemaker.com/ti/101276.html