Field Calculation based on contents of 3 other fields

[Jesse Wright]

What I need is for a calculated field to display a "DueDate" based on the contents of three other fields. So, if both other criteria are empty then display Field A, if Field B has data display it, and if Field C has data but not Field B, then clear the Field A data, and leave the field blank.

Too convoluted?

So what I need is an IF, AND, THEN(else?) function, but am not savvy enough yet to put it together.

I need it to evaluate the following for my "[color:red]DueDate" field:

The fields involved are: [color:red]Deadline, [color:red]FinalDeadline, and [color:red]FinalDateIn.

What I need is the "[color:red]DueDate" Field to display "[color:red]Deadline" when there is no data in "[color:red]FinalDeadline" but only when there is no data in the "[color:red]FinalDateIn" field. If there is data in "[color:red]FinalDateIn" then leave the "[color:red]DueDate" field blank.

[color:blue]My attempt at figuring out how to code it:

If ( IsEmpty ( REPORTS Table::FinalDeadline ) and If ( IsEmpty ( REPORTS Table::FinalDateIn )

GetField ( REPORTS Table::Deadline )

else If ( IsNotEmpty ( REPORTS Table::FinalDeadline )

GetField ( REPORTS Table::FinalDeadline )

else If ( IsNotEmpty ( REPORTS Table::FinalDateIn )

DueDate = 0 (0 = whatever command leaves the field blank)

Thanks for your help!

EDIT:

If ( IsEmpty ( REPORTS Table::FinalDeadline ) and If ( IsEmpty ( REPORTS Table::FinalDateIn ) = GetField ( REPORTS Table::Deadline )

or If ( IsValid ( REPORTS Table::FinalDeadline ) = GetField ( REPORTS Table::FinalDeadline )

or If ( IsValid ( REPORTS Table::FinalDateIn )....dunno

Edited November 1, 2007 by Guest

[Søren Dyhr]

Read this:

http://edoshin.skeletonkey.com/2006/06/linked_fields.html#more

--sd

[Jesse Wright]

Thanks for the reference Søren, I was just given the solution on another forum and my next task was to go and learn how to do it myself.

Cheers!

FYI

Solution (Thanks to [color:blue]doughemi on FileMaker Cafe)

Case(

IsEmpty ( REPORTS Table::FinalDeadline ) and IsEmpty ( REPORTS Table::FinalDateIn ); Deadline;

not IsEmpty ( REPORTS Table::FinalDeadline ); Table::REPORTS Table::FinalDeadline;

not IsEmpty ( REPORTS Table::FinalDateIn ); ""

)

[Søren Dyhr]

This can be simplified:

IsEmpty ( REPORTS Table::FinalDeadline ) and IsEmpty ( REPORTS Table::FinalDateIn )

...into:

not Count(REPORTS Table::FinalDeadline;REPORTS Table::FinalDateIn)

--sd

[Jesse Wright]

Very cool Søren, thank you!

[comment]

LOL, wouldn't:

IsEmpty ( REPORTS Table::FinalDeadline & REPORTS Table::FinalDateIn ) 

 be simpler?





Speaking of simplifications, I believe the following should produce the same results:

Case ( 

not IsEmpty ( REPORTS Table::FinalDeadline ) ; REPORTS Table::FinalDeadline ;

IsEmpty ( REPORTS Table::FinalDateIn ) ; Deadline 

)

 

Or, if the fields are (as it seems) date fields:

Case (

REPORTS Table::FinalDeadline ; REPORTS Table::FinalDeadline 

not REPORTS Table::FinalDateIn ; Deadline

)

[Søren Dyhr]

Indeed!!!

--sd