Running Total with a Max

Question

Have a timesheets table and it helps with accruing Paid time off.

I need to implement a policy that PTO will max out at 160 hours in a running total field.

The running total field looks like this:

var text QONE = "{23.BF.'" & [Date] & "'}AND {56.EX.'"& [Related Resource] &"'}";
var text QTWO = "{23.EX.'" &[Date] & "'}AND{3.LTE.'" & [Record ID#] & "'}AND {56.EX.'"& [Related Resource] &"'}";

Min(160,Nz(SumValues(GetRecords($QONE),193)+Nz(SumValues(GetRecords($QTWO),193),0)))

THE PROBLEM: When I use the Min function it doesnt calculate against the 160 value in the formula. If the balance is 200 hours, any subsequent line entries build off the 200 value although the table above shows 160.

Here is the table without the Min function:

When I try to change the formula in the PTO in the something like

If([PTO BALANCE]>=160,0,[Hours worked]*[PTO Rate]

I get an error that I cannot write a formula for a field that is based on itself.

---------------------------------------------------------------------------

I also tried the pipeline approach but since PTO in is a calculated field I hit a road block as well.

Top Step · Accepted Answer

The Min() is working, it's just only capping what shows on that row. Each later row re-adds the raw history, so it still builds off 200.

A cap like this depends on every earlier capped value, and a formula query can't read its own result. That's also why you get the "based on itself" error.

What works better is to store the accrual instead of calculating it live. Add a plain numeric field like [PTO Accrued], and have a pipeline fill it when a timesheet is added: look up the person's current balance, then write the smaller of their normal accrual or 160 minus the balance, never below zero.

Your balance then becomes a simple sum of [PTO Accrued] minus PTO used, and it can't go past 160 because the extra hours were never credited.

If you have existing history, backfill [PTO Accrued] once in date order before turning the pipeline on.

MarkShnierYou · Answer

I'm not sure I completely understand why you need to queries to get to your totals, but maybe it has something to do with the placement of the brackets. Maybe try this

Min(

160,Nz(SumValues(GetRecords($QONE),193))+

Nz(SumValues(GetRecords($QTWO),193))

)

Sign up

Login to the community