How to lookup a particular field of group of records in table B from table A

[vc_teo]

I have a main table A, which consists the main list of objectives, say A1, A2 and so on. Each objective would have it’s completion percentage.

To each of these objectives, I have a number of tasks which are in table B. Multiple tasks in table B are mapped to one record in table A. So B1, B2 could be linked to A1, and B3, B4, B5 could be linked to A2. Each table B records would have it’s completion status.

I want to display the completion rate of each task under each objective. For example, if B1 and B2 are both completed, I want to show in table A record A1 completion percentage of 100%. If B3 and B4 are completed, but B5 is not. Then I want to show in table A record A2 completion percentage of 66%.

It seems like I’ll need to use Conditional Rollups for this. Conceptually I understand that to compute the completion percentage in table A, I’ll need to lookup to table B, find those records that are linked to A1 or A2, and then check the completion status field. If all shows completed, display 100%. Else, take the count of completed, divide by the total count of B records that corresponds to the A record, to compute the completion percentage for A.

I’ve been trying to construct the formulas for a while now but can’t seem to get it right.

Appreciate it if someone could help. Thank you!

[Jeremy_Oglesby]

In table B, you’ll need an additional field, say “# Completed” that generates the number “1” if the task is checked as complete, 0 otherwise:
IF(Conpleted=TRUE(), 1, 0)

In table A, you need a “Count” type field that counts the total of linked tasks from B. Then, you need a “Rollup” that looks at “Table B” —> “# Completed” —> SUM(values).

Now in Table A you have the values you need for dividing to get a % complete for the objective:
(Rollup*100)/Count
and format that as a %.

[vc_teo]

Brilliant, it works! Thank you.