Formula Checkbox
I am very new to this so let me see if I can explain what I'm trying to accomplish. I have built multiple tables, the main table will be a work order with the customers info and all that. I have multi...
Forum Discussion
If you had a relationship between two tables, work order and task, where a work order can be linked to multiple tasks, you would usually add a summary field to the relationship between the tables to count the number of tasks that are not complete and then create a formula checkbox field on the work order table that checks whether the number of linked tasks that are not complete is zero.
As an aside, using this approach, a work order would be regarded as complete if there are no linked tasks; it might be appropriate to add a summary field to count the total number of tasks linked to a worked order and have the formula in the 'work order complete' field check that the total number of tasks is greater than zero and the number of incomplete tasks equal to zero.
In your question you've mentioned you have multiple other tables to hold tasks. If that's the case you might want to use a formula query field to count the number of incomplete tasks in all of the associated tables. It might look something like this:
Size(GetRecords("{'9'.EX.'"&[Record ID#]&"'} AND {10.EX.'Incomplete'}", [_DBID_TASKS1]))
+ Size(GetRecords("{'9'.EX.'"&[Record ID#]&"'} AND {10.EX.'Incomplete'}", [_DBID_TASKS2]))
+ Size(GetRecords("{'9'.EX.'"&[Record ID#]&"'} AND {10.EX.'Incomplete'}", [_DBID_TASKS3])) etc.
Here you would get records where the related work order field in each of your task tables (say field with ID 9) equals the record ID# in the work order record (i.e. all tasks for the work order) and where the task is incomplete (checking some other field in the task table). You would need to modify this formula to meet your needs, but hopefully that gives you enough to make a start.
------------------------------
Jeremy Anson
------------------------------
As an aside, using this approach, a work order would be regarded as complete if there are no linked tasks; it might be appropriate to add a summary field to count the total number of tasks linked to a worked order and have the formula in the 'work order complete' field check that the total number of tasks is greater than zero and the number of incomplete tasks equal to zero.
In your question you've mentioned you have multiple other tables to hold tasks. If that's the case you might want to use a formula query field to count the number of incomplete tasks in all of the associated tables. It might look something like this:
Size(GetRecords("{'9'.EX.'"&[Record ID#]&"'} AND {10.EX.'Incomplete'}", [_DBID_TASKS1]))
+ Size(GetRecords("{'9'.EX.'"&[Record ID#]&"'} AND {10.EX.'Incomplete'}", [_DBID_TASKS2]))
+ Size(GetRecords("{'9'.EX.'"&[Record ID#]&"'} AND {10.EX.'Incomplete'}", [_DBID_TASKS3])) etc.
Here you would get records where the related work order field in each of your task tables (say field with ID 9) equals the record ID# in the work order record (i.e. all tasks for the work order) and where the task is incomplete (checking some other field in the task table). You would need to modify this formula to meet your needs, but hopefully that gives you enough to make a start.
------------------------------
Jeremy Anson
------------------------------
