Forum Discussion

AmyGosz1's avatar
AmyGosz1
Qrew Trainee
3 months ago

Set Same Date Each Year

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
------------------------------

4 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Qrew Champion
    3 months ago

    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's avatar
      AmyGosz1
      Qrew Trainee
      3 months ago

      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's avatar
        MarkShnier__You
        Qrew Champion
        3 months ago

        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 

        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)  reviews due June 30, 2024.  Once they do a review say dated June 1, 2024, then next review due date will become June 30, 2025.  



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