Hey all,
Apologies if this is rather obvious, but I’m having some trouble getting my head around the filter formula.
I’m attempting to make a simple summary table from a larger detail table - and pull in some specific data.
See this screenshot :
What I am trying to do is add up the “Task Est Duraction” column in the Workflow Master Table, but only add up rows where the Project stage is the one listed in the summary table.
In this instance, I’m trying to come up with a formula that adds up 2,3,4,5 & 6 inside the master table.
Eventually, I need to extend it to check the first column as well (SN101) - as each episode will have its own list of tasks and times. ie, my summary table will look something like this 
To put it another way : I want it to sum only a subset of the column called “Task Est Duration” - using only rows from the master table where the data in the episode and stage columns match those in the summary table.
I have spent ages in the help docs, and tied so many different iterations of the formula. (I feel like formats have changed since some of the help docs were written)
Any help is as always much appreciated. Thanks!
Hey!
Best to approach it like this:
- Pull relevant rows from Workflow Master Table into the Summary Table first, into a dedicated column. Make a column of type Lookup — Workflow Master Table, enable multiple items, and write a column formula:
[Workflow Master Table].Filter(CurrentValue.[Project Stage] = thisRow.[Project Stage] AND CurrentValue.Episode = thisRow.Episode)
Call it e.g Tasks.
- Now in your formula for duration, use the linked rows:
thisRow.Tasks.[Task Est Duration].Sum()
You can combine this in a single formula, but it’d be the best practice to do it in two steps like this.
If you don’t need to filter on episode yet, drop the AND CurrentValue.Episode = thisRow.Episode part.
Please let me know what about filters is confusing for you. I’ll try to explain.
2 Likes
Thanks. It can be hard sometimes going from one system to another… one syntex to another - and figuring out all the nuances which make everything tick!
! I’ve managed to see thru my errors / approach and have it working - and ALMOST understanding whats going on. I think I just fundamentally didn’t understand filter
(I also was attempting to do it all in one column - and I can see how it works, but also how thats not great practice especially when looking back trying to figure out WHY something is or isn’t working. The extra column really helps me understand whats going on!)
I have some related questions / followups - but I’ll attempt to work thru them myself for a while before asking more questions. Sometimes it helps to be patient and figure it out, especially when it feels like I’m ALMOST there!
EDIT :
So I do have a situation I can’t get my head around - which hopefully is nice and easy.
I was able to figure out SOME of the reason that I was having trouble. It was to do with sometimes trying to reference a column in a row that was text with a column in another table that was referencing the original column…
The formula wouldn’t work in that case. The dirty easy solve is to just make a second column referencing the first, and the filter matches.
However, I’m gathering there is another way. (Maybe?)
See below how I’ve essentially duplicated the data to make it work :
And I’m using this formula to get it to work :
Tasks.filter(CurrentValue.[Project Stages]=thisRow.[Project Stage Referred])
I had been trying something like this, which doesn’t work (i’m guessing due to format differences!)
Tasks.filter(CurrentValue.[Project Stages]=thisRow.[Project Stage])
Is there a simple way of dealing with this outside of making the extra column? Cheers again for your help!
Hey, can you maybe share your doc please? Publicly or just with me: actinarium@gmail.com
1 Like
Hey @Paul_Danyliuk Paul - thank you SO much for your input all over this forum… reading your notes / posts have helped a great deal.
As it happens, I’ve managed to re-jig a bunch of this project so my “hack” wasn’t necessary - so its no longer a part of my doc.
I’m getting relatively close to being finished, but once its done I’ll try put the time in to demonstrate the issue I was having properly and then see if we can come up with a solution, as its one of those little things that I’m sure others will come up against. It could be (probably will be) something incredibly simple!)
As far as my understanding of the filter formula goes - I think I’m there - I’ve certainly been able to use it a bunch of times and get the result I’m after! So cheers.
