Discussions

 View Only
  • 1.  Running total - PTO Days remaining

    Posted 02-06-2023 16:00
    Employee has 20 days at beginning of PTO time period
    Employee has multiple time-off requests throughout the period.
    How do I create a running total field [REMAINING DAYS] off of [DAYS ALLOWED] field?
    thx

    ------------------------------
    BuildPro
    ------------------------------


  • 2.  RE: Running total - PTO Days remaining

    Posted 02-07-2023 10:53
    Edited by Don Larson 02-07-2023 11:26
    You need a formula field on the Employee table that will subtract the number of approved days that year from their Yearly PTO.

    So the components are 
    Number of days from the request
    Requests from this year
    Request Status is Approved

    One more factor is the Field Type.   Are you tracking Numbers or Durations? If you are going to use the info in other calculations, I would go with Durations. 





    ------------------------------
    Don Larson
    ------------------------------



  • 3.  RE: Running total - PTO Days remaining

    Posted 02-07-2023 11:15
    @BuildPro  do you mean a running total on a report?​

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 4.  RE: Running total - PTO Days remaining

    Posted 02-07-2023 11:29
    Edited by Brian Seymour 02-07-2023 11:30
    Don's schema seems pretty solid and scalable, but may be normalized a bit more than your needs, particularly regarding PTO Type and tracking history of Status changes? The many to many with a reverse relationship can be fairly challenging to wrap your head around.

    I suggest keeping it simple, at least initially, and create a Summary field on parent table (Employees) that sums the child (PTO Requests) time. Then, subtract that value from the where you are storing the 20 day value on the Employees table. Watch out for your formula data types (duration vs. number), QB will bark at you and guide you to what it is expecting.

    ------------------------------
    Brian
    ------------------------------


  • 5.  RE: Running total - PTO Days remaining

    Posted 02-07-2023 12:24
    Edited by Don Larson 02-07-2023 12:25
    Brian is right.  Throw out the PTO Type, it was not part of the question.

    The Status Change is only important if the Request is really a Request that has a workflow in QB where is can be approved or disapproved.   If they have some other approval process and QB just records time off, then throw out those tables as well.

    ------------------------------
    Don Larson
    ------------------------------