I have this button (top left of the screenshot), and under him you can see his formula.
Notice: The formula works flawlessly.
The problem is that don’t understand how it works. To be exact, I don’t understand this part:
[Tasks].Filter([_Projects]=[CurrentValue])
In the example of the first row (Task - Learn and Help), “Education” should be equal to the row it’s in. Right? If true, then I don’t even understand what that means. And then if it is equal, the row is used as a value under “Tasks (Stamped)” for the Education row in the Projects table.
When you use a lookup field, the value(s) in that field are row references. The values in [Tasks].[_Projects] field are NOT the value in [Projects].[Project] field, but the row references of [Projects].
The only reason you see there the corresponding value of [Project] is because, instead of showing you some ugly row ID, when you refer a row coda tags it with the value of its Display column (field), which you can easily tell by the flagged icon in its column name.
So, when you refer a row from Projects, like you are doing in _Projects, that row will be tagged with its Project value.
Hence, the value in your first Task, ‘Education’, does not refer to the Project value of your first row in projects, but to the row itself.
If you want to check the difference, you can add a text column in [Tasks], say [Tasks].[Project names], and you can add a formula there =[_Projects].[Project]. Its value should be also ‘Education’, but in that column you are truly referring the value of corresponding [Project] field, instead of row.
Disclaimer: the example won’t work well in your real scenario with more than one project, i am simplifying it and applies only to this single selected project scenario.
If im not wrong, current value returns the current value of the display column that the lookup is finding. Similar to thisrow i think. If im wrong, someone please correct me.
Sorry, I was in the middle of something and just remebered I was trying to help here.
Your doc is fine for your use. What I meant is that if you want to use Project name text field, then when you have more than one you will have to handle the logic you want in order to concatenate them. Otherwise, they will simply concatenate by ", " delimiter. I have added a column with a “Project name” text field, which takes your list of related _Projects, gets each project name (Project field), and concatentates with line break.
I have added to your doc two more examples of how you can use row references in your table. Bear in mind, though, that in your example, it would make more sense that your Stamp button also calculated my Total tasks time column, rather than using a formula. Its only for demonstration purposes, casue makes little sense using a formula that will be recalculated on each table change, when the only way its value should change is when you press Stamp button.
