Hi all! I’m creating a Service planner where each line item contains my Customer details. I’m including 3 sub-items under each as Service Call 1, 2, and 3. These sub-items have due dates, and Status as columns.
I wish to gain a consolidated view for each of my customer on the item (parent) level, where it counts the “done” from my subitems. Could someone please help me out as to what formula I can use??
Welcome to the community! On the column menu (3-dots) of your subitem status you can select “Show Summary on Parent Item” this gives you a battery widget on the parent item that can be changed in the settings to only include “Done” items. If you need a real number (count) for a specific label (Done) you can also use the app Rollup Subitems from the marketplace.
2 Likes
Hi Homesh,
If you need the actual count in a separate column (or any other info from the subitems) besides the summary column, you can use a 3rd-party app called the Advanced Formula Booster.
With this app, you have access not only to the values of the current item but also those of the Next, Previous, Parent (if any) and Sub-items (if any). In this case, we are talking about the subitems.
Let’s say you add a Done Count Numbers column to your board (at the item level). Here is how the formula would look like
The first line is for illustration purposes. It assigns all the subitems statuses to a temporary variable called [SubStatuses]. The point is to show that sub-item statuses are returned in one string, each separated by a |. As you can see on the right, the item I used for the test has 3 sub-items among which 2 have a Done status and 1 no status at all.
To count the number of “Done” statuses, we simply filter this “list” on the 2nd line and assign the filtered list to another temporary variable called [OnlyDone] that we can then use on the 3rd line.
On this 3rd line, we split [OnlyDone] around the | character and count how many we get. This count is written to the Numbers column you previously added to your board ({DoneCount}=…) .
Hope it helps.