Jump to content

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
Link to comment
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 ); ""

)

Link to comment
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 (

REPORTS Table::FinalDeadline ; REPORTS Table::FinalDeadline 

not REPORTS Table::FinalDateIn ; Deadline

)

Link to comment
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

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.