Our Knowledge Base covers a wide variety of technical topics, from the basic to the complex.
If I have a Projects table and a Tasks table where each Project record has many Task records how do I automatically indicate that the Project is completed when the "Status" field in all the related tasks are marked "Completed"?
Create two summary fields in the Projects table (To learn how, please see our Create a Summary Field help topic). The first summary field will be labled "Number of Tasks". It should be configured to count the number of Tasks. The second summary field should be labeled "Number of Completed Tasks". It should be configured to also count the number of related Tasks with a condition to include only the Task records where the "Status" field is "Completed". Then create a third field of type Checkbox - Formula in the Projects table. Call this field "Project Complete". The formula for this field would be:
[Number of Completed Tasks] = [Number of Tasks]
Now you have a checkbox field that automatically gets checked when there are no uncompleted tasks related to a Project.
If you don't want the "Project Complete" field to be checked when you create a Project without any related tasks then change the formula to the following:
if([Number of Tasks] <> 0, [Number of Completed Tasks] = [Number of Tasks])