There are more activities within a project and I want to collect the same information about each activities within a project (number of participants etc.)
So I have a table with the project details and a lookup column where I display the "Activities’ table as a table and I want to refer back to the Project table harvesting the “Project number” so in the Activities table I can see e.g. how many participants we had in total.
For some reason when I want to pull the project number I can refer to the one selected, it will always add the new project number so it will be like, 100, 101, 102 etc.
I use this formula which is not working (in the Activities table to pull the Project number)
[Project reports 2].[Project number].Filter(CurrentValue.[Project number])
Can you help me understand the different types of records and how they relate to each other? I think you may need to restructure things a bit. For example, let me know if this outline is correct:
Projects (with numbers such as 100, 101, etc)
Each Project has multiple Activities associated with it
Each Activity has
a Code associated with it, which is selected from a pre-set list of valid codes [true?]
a number of participants
And you would like to know, for each project, the total number of participants across all activities associated with that project?
Thank you very much for your effort. You got it right. Except that the activities are not pre-set it is a simple text column where users can name their activities in a way they want.
I just can’t figure what would be the way to refer to the project number specifically what I select in the beginning and that Coda does not accumulate the project numbers from the second entry… Should be filter or current value?
When someone makes an activity, and wants to connect it to a project, how do they do that?
Currently, it seems to me that Activities table → Project Number column should have no formula, and should instead just be a Lookup column where the user can select which Project this Activity relates to.
Do I understand this right, or has the user already specified which project relates to which activity somewhere else?
Well yes actually you are correct. When I add activities to the Project 100 A1, A2, A3 it does show only the projects 100 Activities even after I add the activities to 101.
On the other hand somehow I feel safer if in the Activities table I can somehow pull the Project Number with a formula so the users cannot make a mistake. As they have already selected the Project Number in the Project reports 2 table!
For some reason I can only see the toggle to display a Lookup table as Table or not when I select “Allow multiple selections”. Maybe that disrupts to be able to pull the Project Number only once?
Aahh!! I think I know. For the activities I should use the “Referenced by” column type. but for some reason it does not appear. However if I re-create another table it appears… But it is a lot of time to rebuild all the tables!
I made it! So the solution is the referencing! I just wanted to reference on the wrong column. I think now with this I understood how to connect tables with the Referenced by option!
Above in the shared doc it is now in a correct way.
