I am working on an HR app and there’s a requirement to create new annual leave records each year (every 1st of Jan, we will add 25 days of PTO for each user). However, last year’s remaining days should also be added to the new record (for example if the user had 10 days left last year, the new total of this year’s PTO will be 35).
I was able to create new PTO records but I can’t figure out how to fetch the “remaining days” from last year.
I created a query property to connect last year’s record to this year’s and a lookup to fetch the remaining days but I don’t know how to structure the API call to actually retrieve the remaining days.
The math function doesn’t work because it’s basically looping through records infinitely and Glide is preventing it.
What you probably need is a Query column to look at the last year records for the user. Then you probably need a Rollup column against the Query to get the total number of remaining days. That’s a bit of rough advice. If you are still struggling, it would help to see screenshots of your data to better understand how you have it all set up.
Thanks for your insight. I have configured this prior to posting here, here’s my configuration. Now what I need to do is basically whenever I create a new record, I need to fetch the “To Transfer From Last Year” and push it to “Transferred” so I can use it in calculations. This is where I got stuck.
I tried using the rolled up value to calculate “Total Remaining” and “Total Available” but the problem is that Glide didn’t allow it because it would be an endless loop.
Lool this is just some sample data that’s linked to an “absence tracker”. Each absence is linked to an “annual leave” record depending on what timeframe the absence is in.
So this is how it’s calculated:
Total Available = New + Remaining (info comes from the last chronological record so basically from last year from Total Remaining property) + Manual Adjustment
Total Remaining = Total Available - Spent (rollup from Leave Tracker relation)
And yeah there is only one record per Employee Type Year.
I think what you may need is two more math columns. Think of Total Available and Total Remaining as interim columns that only pertain to that year. What I think you need is another set of columns like Final Available and Final Remaining which sum the values in to Total columns with the value in the To Transfer Rollup column. At least something along those lines. I hope that makes sense.
this is just some sample data that’s linked to an “absence tracker”. Each absence is linked to an “annual leave” record depending on what timeframe the absence is in.
But it worked!! Thanks 
