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!

  • 0
  • 1
  • Question
  • Updated 6 months ago
  • Acknowledged
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!
Photo of Greg Schroeder

Greg Schroeder

  • 130 Points 100 badge 2x thumb

Posted 6 months ago

  • 0
  • 1
This should not be done with Dynamic form rules as they only fire when a record is being editing by a human, and not via the passage of time.

You should use a formula which calculates the vacation time based off the start date of the employee.

Can you state in words what the vacation policy calculation is?



Photo of Greg Schroeder

Greg Schroeder

  • 130 Points 100 badge 2x thumb
Year 1-2 10 days (80 hours)
Year 3-5 15 days (120 hours)
Year 6+ 20 days (160 hours)
Photo of Greg Schroeder

Greg Schroeder

  • 130 Points 100 badge 2x thumb
up to 3 years from start date = starts with 80 hours
after 3 years  from start date = ( add 40 hours ) 
after 6 years  from start date = ( add 40 hours ) 
Photo of Greg Schroeder

Greg Schroeder

  • 130 Points 100 badge 2x thumb
The names of the two fields I'm working with are "Start Date" and "Available Vacation Time"
Any help is greatly appreciated!
Not tested but try this

IF(
Today() >= AdjustYear([Start Date],6), 160,
Today() >= AdjustYear([Start Date],3), 120, 80)
Photo of Greg Schroeder

Greg Schroeder

  • 130 Points 100 badge 2x thumb
Here?


Photo of Greg Schroeder

Greg Schroeder

  • 130 Points 100 badge 2x thumb
I think it worked... I can no longer edit the available vacation field and it looks like it has the correct values for my employees... Thanks!
Photo of Greg Schroeder

Greg Schroeder

  • 130 Points 100 badge 2x thumb
wait, the employees with between 3-5 years isn't displaying as 120... they are showing as 80 hours like the ones between 1-3 years
Photo of Greg Schroeder

Greg Schroeder

  • 130 Points 100 badge 2x thumb
shoot this won't work anyway... sorry because after they take time off we need to be able to subtract hours from this fiend and it's not editable right now.   I'm sorry like I said, learning
Photo of Greg Schroeder

Greg Schroeder

  • 130 Points 100 badge 2x thumb
Anyway just on the 3 and 6 anniversary dates 40 can be added to those fields ( regardless of what's in them at the time ) and keep the field editable so they can subtract time as it's used? 
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?


Photo of Greg Schroeder

Greg Schroeder

  • 130 Points 100 badge 2x thumb
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!






Photo of Greg Schroeder

Greg Schroeder

  • 130 Points 100 badge 2x thumb
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
Photo of Greg Schroeder

Greg Schroeder

  • 130 Points 100 badge 2x thumb
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.



Photo of Greg Schroeder

Greg Schroeder

  • 130 Points 100 badge 2x thumb
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
Feel free to contact me at QuickBaseCoach.com
Photo of Greg Schroeder

Greg Schroeder

  • 130 Points 100 badge 2x thumb
message sent... thanks!