Forum Discussion

GregSchroeder's avatar
GregSchroeder
Qrew Trainee
7 years ago

Need to add 40 to the a numeric field value after a certain number of days pasts in another date field. Can anyone please help! Thanks!

Hello,relatively new to quick base here. I'm trying to track my employees available vacation time in a field AND automatically add 40 days to that field after their start date reaches 2 years ago ( 730 days ). With a dynamic form rule I can select when Start date is equal to itself plus 730 days, but I can't figure out the action to simply add 40 days. I can select change, available vacation time, but from here can't figure a way to add 40 to the original number in the available vacation time field. Can anyone please help! Greatly appreciated!  Thanks!

19 Replies

  • Greg, this field is meant to calculate the vacation entitlement.  Typically there will be a child table on the Employee (EE) record to record each vacation instance.  Then a summary field back up to the EE record to calculate the total vacation taken during the year.

    That way everyone can know what the details were for the vacation taken.

    As for the formula not working, can you give me an example of a EE and their start date that you feel is not calculating correctly?


  • First really appreciate your help! 

    Next, good news is I realized the value that I should should be 120 should be 80... looked at the date again and it hasn't been 3 years. 

    Based on your response above this field we just setup should actually be the renewal vacation time field.   So I've renamed that field and created a new field called available vacation time.  
    How do I set that up so every year on their start date it adds the value on the renewal vacation time field? 

    Hope that makes sense. 
    Thanks again!






  • I have another field in the table where employees will enter requested vacation hours, then during the approval process the manager can subtract the hours from their available time... that much can be a manual process...

    However, I've been asked for their yearly vacation renewal allowance to automatically be added to their available vacation time field.Adding to what is already there and keeping the field editable so the manager through the year can subtract hours used.  Is that possible?  Thanks
  • Can I do something like this for the formula on "Available Vacation time" field. 
    This adds their anniversary vacation renewal balance every time their anniversary date comes around?   Then I noticed in the dynamic form rules I can make the "Available vacation time" field every time it's simply greater than 1.  This would allow the manager to subtract approved hours from their available balance throughout the year? 

    IF(
    Today() >= AdjustYear([Start Date],15), +[Anniversary Vacation Renewal],
    Today() >= AdjustYear([Start Date],14), +[Anniversary Vacation Renewal],
    Today() >= AdjustYear([Start Date],13), +[Anniversary Vacation Renewal],
    Today() >= AdjustYear([Start Date],12), +[Anniversary Vacation Renewal],
    Today() >= AdjustYear([Start Date],11), +[Anniversary Vacation Renewal],
    Today() >= AdjustYear([Start Date],10), +[Anniversary Vacation Renewal],
    Today() >= AdjustYear([Start Date],9), +[Anniversary Vacation Renewal],
    Today() >= AdjustYear([Start Date],8), +[Anniversary Vacation Renewal],
    Today() >= AdjustYear([Start Date],7), +[Anniversary Vacation Renewal],
    Today() >= AdjustYear([Start Date],6), +[Anniversary Vacation Renewal],
    Today() >= AdjustYear([Start Date],5), +[Anniversary Vacation Renewal],
    Today() >= AdjustYear([Start Date],4), +[Anniversary Vacation Renewal],
    Today() >= AdjustYear([Start Date],3), +[Anniversary Vacation Renewal],
    Today() >= AdjustYear([Start Date],2), +[Anniversary Vacation Renewal],
    Today() >= AdjustYear([Start Date],1), +[Anniversary Vacation Renewal])




  • This is getting confusing.

    Never use form rules to auto edit the data for data that needs to change due to the simple passage of time. That is just not how Quick Base works for data that needs to change through the passage of time.

    You may want to book some one on one help to get this all working.

    The typical setup is that there will be a formula to calculate the Vacation Entitlement based on years of service and typically allowing for a manual override in case one person gets a different deal that the rest.

    Manual overrides are done by having a new field for the manual override and then a formula field that will use the either the manual override if not null (be sure to check the checkbox to treat blank as null) for that override field.

    Then there will be a summary field for the vacation time used which is a summary of vacation taken subject to the filter date is "during the current year". 

    Then a new field to subtract to give the remaining vacation available to be taken this year.  There is no one needed to manually subtract the remaining vacation.  That happens automatically.



  • thanks again for your help... I'm getting there but not quite... what would the process be to book some one on one help?  thanks