I know Automation is a massive topic. I am working on a project that requires a simple “Snapshot” of a value in a lookup field during record creation.
The idea seems simple to me as non-programmer
Select the field type, something like “Field Snapshot”.
Configure the field by pointing to a Table and Field.
Whenever a new row is created, this field would take a “snapshot” and record the value in the field it is pointed at.
This feature would allow me to use the Rollup Function to sum values in fields that need to be static vs a dynamic lookup field.
My Example:
Workplan hours recorded and summed for a project based on the Project Phase. So as the Project progresses through project phases, Work hours are recorded based on the current phase of the project (without having to manually select the project phase when work plans are submitted). The Rollup function would sum work hours for each phase of the project…
I am not 100% sure if I understand the use case, but it sounds like you want to only summerize certain records from a table. This can be achieved by combining the filter() and sum() functions.
For example: you have a linked field to a table named Performances. This Performances table holds a status about the phase and a number that holds the cost). The formula would be
Thanks Frederik…Here are more details about the use case.
Project Table -All activities are tied to a project.
Deliverables Table - Each Row is the name of a deliverable (Project Phases) for project along with the deliverable Deadlines.
“Work Planning Table” - Each Row is a specific Workplan for a given project + Week, Resource + Deliverable Phase + Work Hours.
I need to keep a running total of Workplan Hours for a given project and Project Phase.
So for example, if a Project has 4 deliverable phases, I need to track and sum workplan hours for each phase.
The problem I am having is that Each Work plan in the Work Planning Table is connected to the Deliverables Table so when the active Phase changes in the Deliverable table, it also changes in All of the Work Planning Table Records.
As shown below, the Deliverables Table needs to track Workplan Hours for each Deliverable phase based on which phase is Active (Bolean = True)
Unless I am missing something, here’s why this will not work (rewritten by my buddy Chatgpt)
The key challenge here is that a project can only be in one phase at a time, even though multiple phases are listed in the Deliverables table. Only one phase is active at any given moment, as designated by a status field (e.g., Status = True).
Goal:
The goal is to have all workplan hours that are submitted during a specific phase period summed and displayed next to that phase in the Deliverables Table.
Issues with the Suggested Solution:
Real-Time Rollup Functionality:
The Rollup function is designed to provide real-time updates as the data it is aggregating changes. This means that any changes in Workplan Hours will be immediately reflected in the Rollup sum.
Dynamic Project Status:
The Project Status, which indicates which phase is currently active, is set dynamically by the Operations Department. Because this status is a dynamic lookup and not a static value, it changes over time as the project progresses through its phases.
The Rollup function, however, is not inherently aware of which Project Phase is currently “Active.” It simply sums the Workplan Hours across all relevant records without considering the current phase status.
Rollup Function Limitation:
Since the Rollup function lacks awareness of the active phase, it will continue to aggregate all Workplan Hours for a project without distinguishing which phase those hours were associated with. This leads to an incorrect or misleading total in the Deliverables Table.
The only way to correctly sum the Workplan Hours for each phase is to have the Resource manually set a static Project Phase value at the time they create the workplan. However, this manual step is not ideal and introduces potential for user error or inconsistency.
Conclusion:
The limitation of the Rollup function in this context is that it cannot dynamically filter and sum Workplan Hours based on the active phase without additional manual input. This makes it less than ideal for the scenario where the phase is dynamically set by a department and can change over time. To achieve the goal of accurately summing hours for each phase, a different approach may be required, possibly involving automation or custom scripting to capture the active phase at the time each workplan is created.
