Set Same Date Each Year

Question

This may be a simple solution, but I am unable to figure out how to do it. We have created a new app that requires yearly review of records. All reviews are due by June 30 of each year. The first date for this will be 6-30-2024, but then, when a review has been completed, I need that date to change to 6-30-2025.
Children is the parent table with Annual Reviews as the child table. I'm not sure if I can just do this on the parent table and or if it should be done on the child table. If more info is needed, just let me know what is needed. Thank you!
------------------------------Amy Gosz------------------------------

markshnier__you · Answer

Does the Annual Review table have an explicit field that tells us what year the review was for? I have no doubt that there is a field for annual review date but my concern is they may be late and do an annual review on July 1 that relates to the previous fiscal year. Can you simply tell me conceptually a formula for how I know which year the review is for? Like maybe you allow three months grace to get the reviews done for the fiscal previous year.

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

amygosz1 · Answer

Hi Mark....I did not set up a field to specify the year of the review. I do have a summary field 'Last Annual Update'. If a new update is not completed, then this field would not change and the field 'Next Annual Update Due' would show the review is past due. I created a field on the Children table (parent) called 'First Annual Update Due' and set it as 6-30-2024 for all records. I can have this date set for 'Next Annual Update Due' if 'Last Annual Update' is blank. However, I also need the formula to not look at this field after 'Last Annual Update' is set. I can figure that out, but am unsure how to have the formula add 1 year to the date June 30 and not the 'Last Annual Update'.

------------------------------
Amy Gosz
------------------------------

markshnier__you · Answer

if we are safe in making the assumption that these annual reviews would be done in the back half of your fiscal year whuch means they would in practice be done during January to June, then try this

LastDayOfMonth(AdjustMonth(FirstDayOfYear([Last Annual Review]),17))

The formula for the [Last Annual Review] can be the&nbsp;
Max(Date( 2023,06,30), [my summary field of the most recent annual review date])
The above would set that last annual review date at June 30, 2023 for all Children records which don't yet have any reviews and hence the formula will calculate the next (and first)&nbsp; reviews due June 30, 2024.&nbsp; Once they do a review say dated June 1, 2024, then next review due date will become June 30, 2025.&nbsp;&nbsp;

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

amygosz1 · Answer

Thanks Mark....once again you came through for me. This is exactly what I needed.------------------------------Amy Gosz------------------------------

Forum Discussion

Set Same Date Each Year

4 Replies