I have a table of Services which has a date field called ServiceEndDate.  This date is only filled in if a service contract is temporary.  I want to make a calculation which says "Your service will expire on " & ServiceEndDate.  Here is what I think

 

If no service start date then it means the service was one-time custom service.  If the service has no end date but a start date, then it is ongoing service.  So my try is this

 

Case ( 

IsEmpty ( ServiceEndDate ) ; "Service Contract in place" ;

not IsEmpty ( ServiceEndDate ) ; "Your Service will expire on " & ServiceEndDate ;

"Custom service"

 

)

 

Problem is that if both dates are empty it says "Service Contract in place" instead of Custom Service.  I tried checking 'do not store' and also made sure  'do not evaluate if fields are empty' was still checked.  I can not see what I am missing here.  Is it some sort of corruption or is it me?

It's you. There is nothing in your calculation that references the start date.

Is it some sort of corruption or is it me?

 

What sort of corruption should that be?

 

Your ServiceEndDate field is empty or it's not – which means that your calculation returns either the first or the second result, but will never reach the default result, since – as Bruce noted – you don't consider the start date. Try

Case ( 
  IsEmpty ( ServiceStartDate ) ; "Custom service"
  IsEmpty ( ServiceEndDate ) ; "Service Contract in place" ;
  "Your Service will expire on " & ServiceEndDate 
)

What if the ServiceEndDate has already passed?  Your calculation will say, "Your service will expire on 11/15/2014" for example, which will look like it does not update.  You can adjust your calculation to address this (be sure and check 'do not store calculation results) and use the current date for comparison to see if it has expired.  Also be sure to uncheck 'do not evaluate if all referenced fields are empty':

Case (
// both dates must be boolean true. Service has or will expire
ServiceStartDate and ServiceEndDate ; 
"Your service expire" & 
Case ( 
ServiceEndDate < Get ( CurrentDate ) ; "d" ; "s" ) & " on " & ServiceEndDate  ;

// only a start date means ongoing contract
ServiceStartDate ; "Service Contract" ;

// default result of custom
"Custom Service"  
) 

The first section then will produce either:

Your service expires on 12/15/2014

Your service expired on 11/15/2014

 

When creating a calculation which short-circuits such as If() or Case(), it is good to consider which value the majority of the records represent and list it first if possible so the calculation will not need to evaluate further.  I would assume the majority of records have a Service contract instead of custom – adjust if needed.

 

Also note that all dates are boolean true unless they are empty so you do not need to wrap with IsEmpty(), making it a bit simpler.

BTW, if this is simply for display, I'd skip the calculation entirely and use conditional formatting to have it calculate on the layout only when viewed.  Just another idea.

Thanks guys.

 

That is why I had no result in one of my attempts.  I did not know what that checkbox really did.  Your calculation is perfect LaRetta.  I had not thought about passed dates.  This calculation will be used to display on the Service Maintenance as a reminder.  I do not know how to make it display with conditional format.  I searched but it only shows changing colors and not calculation.  Is there a link you can point me to or give me ideas on how to do it?