4 Replies Latest reply on Oct 8, 2016 6:33 PM by keywords

# Data Calculations in Date Fields

Hi All,

Thanks to Andy Pie Man with his instructions below I have managed to resolve the problem of the missing data in the date of referral field.

If ( IsEmpty 'date of referral' ; "" ; 'interim hearing - date of referral' )

It has also worked well as I get the time of referral to interim hearing in days, which gives precision with urgent cases.

Some people are referred and they never have an interim hearing. They go straight on from referral to final hearing. I do have a field for final hearings.

This affects the field of referral to interim hearing with it put in days from 1900.

I have tried  or If (Is Empty (interim hearing); " " ;

The Filemaker seems to only like a certain amount of commands. Where I have got it through it returns 0 or 1 which is not much good either.

Many thanks

Dominic

• ###### 1. Re: Data Calculations in Date Fields

Hi,

The Case statement handles multiple conditions (as opposed to If).

you can probably formulate this other ways, but this looks like what you describe:

Case(

not isempty(dateReferall) and not isempty(dateFinal), dateFinal - dateReferral;

not isempty(dateReferall) and not isempty(dateInterim); dateInterim - dateReferral;

getasnumber("")   // or just 0

)

• ###### 2. Re: Data Calculations in Date Fields

One of the benefits of doing calculations within the Let ( ) function is that you can avoid multiple calls on the same field.  In David's Case calc you make up to 4 calls on one field and 2 each on the others. Using Let() enables you to make just one call on each by setting up each field as an internal variable:

Let (

[

dRef = dateReferal ;

dFin = dateFinal ;

dInt = dateInterim ;

result = Case(

not is empty ( dRef ) and not is empty ( dFin ) , dFin - dRef ;

not is empty ( dRef ) and not is empty ( dInt ) ; dInt - dRef ;

getasnumber ( "" )   // or just 0

] ;

result

)

• ###### 3. Re: Data Calculations in Date Fields

I'm a big fan of Let (except in BASIC).

• ###### 4. Re: Data Calculations in Date Fields

BASIC … bloody hell! That's going back a bit.