Calculated Field functions

Question

Hi,

I'm looking for help on Calculated Field Functions, I've looked through the knowledge base but the examples there don't help very much. Previous experience of creating nested If and Case statements in Excel along with building a database in FileMaker.

I have two date fields "Start" and "End", which may or may not be blank. I want to build a function expression that mirrors the statements below:

If "Start" = blank/empty/null etc., display "Start date to be confirmed"

If "Start" > Today, display "Inactive"

If "Start" <= Today AND "End" = blank/empty/null etc., display "Active"

If "Start" <= Today AND "End" < Today, display "Ended"

Any help with the above would be really appreciated, many thanks...

Brian.

Brian Curran · Accepted Answer

Hi Brian Curran , Off the cuff, I came up with the following formula. I used date_entered and date_modified because those were available date fields in the Accounts module in my instance. If you change the fields and try this, let us know how it works out: ifElse ( not ( greaterThan ( strlen ( toString ( $date_entered ) ) , 0 ) ) , "Start date to be confirmed" , ifElse ( greaterThan ( number ( timestamp ( $date_entered ) ) , number ( timestamp ( today ( ) ) ) ) , "Inactive" , ifElse ( or ( greaterThan ( number ( timestamp ( today ( ) ) ) , number ( timestamp ( $date_entered ) ) ) , equal ( timestamp ( today ( ) ) , timestamp ( $date_entered ) ) ) , ifElse ( not ( greaterThan ( strlen ( toString ( $date_modified ) ) , 0 ) ) , "Active" , ifElse ( greaterThan ( number ( timestamp ( today ( ) ) ) , number ( timestamp ( $date_entered ) ) ) , "Ended" , "" ) ) , "" ) ) )