I am looking for a formula that will provide me with a total number of allocated hours for projects for each staff member. The Work Planning Table will have multiple rows for each staff member, which need to be summed.
For the Work Planning Table below, Michael has a total of 14 hours planned.
The formula I am using:
sum(filter(lookup(‘Work Planning’,‘Hours Planned’), lookup(‘Work Planning’, ‘resources_id’) = field(‘Resource Name’)))
It is only giving me one row for a total of 9 hours.
‘Work Planning’ table shown below is used to plan the hours that each staff member will spend on various projects for a given week.
‘Resource Time Allocation’ table is used to track the total time allocated for each staff member for both projects and tasks. The goal is to have each staff member listed showing the total time planned for projects and the total time planned for tasks. It is linked back to the Work Planning and Tasks Tables.
For each row in the Resource Time Allocation Table the formula must compare the ‘Resource Name’ to the ‘resources_id’ in the Work Planning table and add up the “Hours Planned” field for each “match”. The same for the Tasks table.
Work Planning:
Resource Allocation Table:
Hi,
You probably have a table with Resources (that contains records like Michael, Mark,…) This table should have a linked field to the Work Planning table (since the Work Planning table links to the Resources table).
This linked field should contain all the records from the Work Planning table that are assigned to the resource. You can add a Rollup field to the Resources table that takes the sum of the Hours Planned field.
1 Like
This worked perfectly! Thank you Frederik!
Is there a way to do “in-line” editing in the application builder?
I watched your videos on adding new rows and editing rows from within the application builder using forms, but in my case, the workflow for updating projects seems to tedius without the ability to search/filter to locate a row and then edit a row.
Embedding a table view inside an iframe would work if it allowed you to edit fields.
Do you have any suggestions?
Not yet, but the (of one the) next release(s) will offer support for forms inside the repeat element. This will allow you to show a number of records and edit them.
for updating projects seems to tedius without the ability to search/filter to locate a row and then edit a row
Filter and search on collection elements is also on the roadmap. You can create a workaround for the moment, but you need to separate the search page from the result page.
- Search page: contains a form with one or multiple textboxes to enter your search parameters. Clicking the submit button sends you to the result page together with the parameters that are entered in the form.
- Results page: has a data source that filters based on the parameters passed to that page and shows the results.
1 Like
Thank you Frederik…That will have to do for now!
