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 multiple other tables that will be separate task that make up the entire work order. I have included a check box on each task table to indicate that task of the work order is done. What I need to figure out is once all the task are checked complete I need a check box to be ticked on my main table that the entire order is complete. I don't know how to write a formula for it.
------------------------------
preston mcdonald
------------------------------
Question
Formula Checkbox
This topic has been closed for replies.
+7If 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
------------------------------
Sign up
Already have an account? Login
Login to the community
Login with Quickbase US – quickbase.com Login with Quickbase EU – quickbase.eu
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.