Modify Rows to sum values, not replace them

I’m trying to create a table where staff can input the breakdown of their daily work as a percent by area. This table has a button that will submit that info to a master Daily Work table and add their name to a list of staff for the day. It’s working for a single day, but if there is data from other days, it adds all those values together in the new row. Any ideas?

RunActions(
ModifyRows(filter([V2 Daily Production],Date= Today()), [V2 Daily Production].Staff, ListCombine([V2 Daily Production].Staff,thisRow.Name).Filter(CurrentValue.IsNotBlank())),

ModifyRows(filter([V2 Daily Production],Date= Today()), [V2 Daily Production].[Production Staffing], sum([V2 Daily Production].[Production Staffing].filter(currentValue),thisRow.Production)),

ModifyRows(filter([V2 Daily Production],Date= Today()), [V2 Daily Production].[Shipping Staffing], sum([V2 Daily Production].[Shipping Staffing].filter(currentValue),thisRow.Shipping)),

ModifyRows(filter([V2 Daily Production],Date= Today()), [V2 Daily Production].[Waterspider Staffing], sum([V2 Daily Production].[Waterspider Staffing].filter(currentValue),thisRow.Waterspider )),

ModifyRows(filter([V2 Daily Production],Date= Today()), [V2 Daily Production].[Non-Standard Work Staffing], sum([V2 Daily Production].[Non-Standard Work Staffing].filter(currentValue),thisRow.[Non-Standard Work] )),

ModifyRows(thisRow,thisRow.[Staffing Updated?],true))


2 Likes

Ashley here with the Keap team - I’m adding our solution in case it’s helpful to anyone who stumbles upon this while looking for help with their own problems.

After some trial and error, we solved this by creating a view of V2 Daily Production called Today’s Work that filters the date for today only - this eliminated the need to filter for the date in my formula. The ModifyRows formulas then reference that new table view anytime the formula calls for a row reference, and they reference the original table anytime the formula calls for a column reference. Not entirely sure why the columns have to reference the original table and not the table view, but that’s the only way I could get it to work. I also cleaned up the formula by putting each column in the same ModifyRows formula using commas rather than running a separate ModifyRows for each column I wanted to update.

RunActions(
ModifyRows([Today’s Work],[V2 Daily Production].Staff, ListCombine([Today’s Work].Staff ,thisRow.Name).Filter(CurrentValue.IsNotBlank()),[V2 Daily Production].[Production Staffing],sum([Today’s Work].[Production Staffing],thisRow.Production ),[V2 Daily Production].[Shipping Staffing],sum([Today’s Work].[Shipping Staffing],thisRow.Shipping),[V2 Daily Production].[Waterspider Staffing],sum([Today’s Work].[Waterspider Staffing],thisRow.Waterspider),[V2 Daily Production].[Non-Standard Work Staffing],sum([Today’s Work].[Non-Standard Work Staffing],thisRow.[Non-Standard Work]) ),
ModifyRows(thisRow,thisRow.[Staffing Updated?],true)
)