maybe FilterList ( List ( relatedfield ) ; "Open" )?
1 of 1 people found this helpful
I was unclear if ToDo was separate table from Tasks. How about:
Case ( not IsEmpty ( FilterValues ( List ( Tasks::Status ) ; "Open" ) ) ; "Open" )
Hi Bev! We both responded in the same exact second, LOL!
Oh! I keep forgetting about ExecuteSQL() for this if version 12 ... seeing you just reminded me! Great article http://www.filemakerhacks.com/?p=6406 by the way!
Another option is if you can sort your child relationship in descending order and the Status field only contains Open, Closed or empty. Then your first related child record (based upon that ToDo ID) will be Open. Then in calculation or script it would be simple:
Case ( Tasks::Status = "Open" ; "Open" )
or if only for display, just place the Tasks Status field directly onto the ToDo layout. It will display if any are open. Note it also will display Closed even if some Task children have empty Status so if Status can be empty then that might not be feasible. But for 2-value situations, it saves from a calculation.
You can also create a calculaton in ToDo which uses GetSummary ( Tasks::anySummaryField ; Tasks::Status ) with sorted relationship as well. (added ... and again, the first record will be the only 'record' which needs to be checked) ooops this won't work in this context
ToDo Staus and Task Status are in the same table. Thanks you for the response. I'm going to try this. I'll get back to you.
"ToDo Staus and Task Status are in the same table."
Then unless using ExecuteSQL(), you will need to create a self-join of ToDo (only way to see all other records in same table). You can join using cartesian product (the X final selection in relational dialog). Then reference that occurrence's Task Status in the calc or sort that occurrence as indicated above.
ANY tasks within my to do list are open, the to do list has a status of open.
It's not clear what exactly is the "to do list". If you only have a table of tasks, the "list" could be the current found set of tasks, all the records in the Tasks table, or something else.
If the "list" means the found set in the Tasks table, I would use a summary field to determine the status of the "list". For this, I would change the TaskStatus field's type to Number, with 0 being Open and 1 Closed (or vice versa). Or, even better, replace the TaskStatus field with a DateClosed field.
"Case ( not IsEmpty ( FilterValues ( List ( Tasks::Status ) ; "Open" ) ) ; "Open" )"
This script works great. Furthermore, I would like the calculation to return "Open" if any of the tasks don't have a status (is empty). How would I add this feature to the caluculation
As Michael says, your reference to 'list' could be interpreted a few ways. If the ToDo 'list' is a found set then summary would work but it seemed the answer required all records (if ANY task was not closed then the calculated 'ToDo Status' would show Open) so I went with full table concept (requiring join). There are many ways to go. Please clarify if this is based upon found set. If not, and if you set a ClosedDate, you could use (without sorting the relationship):
Count ( ToDo 2::ClosedDate ) < Get ( TotalRecordCount )
You can use 1 or 0 as suggested by Michael - numbers are much faster to sum, count and use within calculations than text and you can display Open or Closed on the layout using Inspector > Data tab > Data Formatting with boolean. So you can use 1 for Closed and 0 or empty for Open. Then either sort ToDo 2 ascending on Task Status and the first related ToDo 2::TaskStatus is your answer or:
Sum ( ToDo 2::TaskStatus ) < Get ( TotalRecordCount ) ... returns boolean true if there are open records (without sorting relationship) or if you prefer the actual text result:
Case ( not ToDo 2::TaskStatus ; "Open" ) ... if sorting relationship ascending.
If you set a ClosedDate, you can also sort ascending on it and Case ( not ToDo 2::TaskStatus ; "Open" ) works as well. But if you want Open, Closed and blank, you cannot use the relational sort to easily identify the first (or last) related record.