# Field Calculation based on contents of 3 other fields

This topic is 6075 days old. Please don't post here. Open a new topic instead.

## Recommended Posts

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 by Guest

--sd

##### Share on other sites

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 ); ""

)

##### Share on other sites

This can be simplified:

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

...into:

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

--sd

##### Share on other sites

Very cool Søren, thank you!

##### Share on other sites

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 (

not REPORTS Table::FinalDateIn ; Deadline

)

```

Indeed!!!

--sd

##### Share on other sites

This topic is 6075 days old. Please don't post here. Open a new topic instead.

## Create an account or sign in to comment

You need to be a member in order to leave a comment

## Create an account

Sign up for a new account in our community. It's easy!

Register a new account