Create one Child Record per Year

Question

Each Parent record needs an annual review. I want to avoid someone creating two records for the same year. However, since I can't use a look up field (Parent Account#) in a unique field formula, I don't know what I can do to avoid this. I would like to concatenate the Parent record Account # and the child record Year to create that unique field value. I've tried searching for something similar but can't seem to find the right solutions. Any suggestions?

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

markshnier__you · Answer

It sounds like account # is a lookup field, which cannot be used in a unique formula.
But the field for Related Parent] can be used in a unique formula because it is not a lookup field.------------------------------Mark Shnier (Your Quickbase Coach)mark.shnier@gmail.com------------------------------

miketamoush · Answer

Can you just have a summary field check to see if a review exists during the current year, and simply hide that add review button if one exists? (Or you can even make a formula rich text button that says 'Add Review' when does not exist, or 'Edit Review' when one does exist.

------------------------------
Mike Tamoush
------------------------------

alexwilkinson · Answer

I heartily endorse Mike's suggestion of a smart button that displays as "Add..." if no record exists and as "Edit ..." if it does. From a user's perspective, it's easy to understand and easily leads to the right behavior. I use buttons like that A LOT with excellent results.

Alex

amygosz1 · Answer

HI Mark....I did try several different ways of writing the formula using Related Parent but it keeps coming back as not compatible to be unique. The field 'Visit Year' is a formula numeric field using the Date Created field. The concatenate field is Numeric Formula. Here is my formula which does not indicate any error until I click Unique and try to save
&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ToNumber([Related Home] &amp; [Visit Year])
Error: This formula is incompatible with this field being marked unique. Please change the formula or uncheck the unique checkbox.
I have tried this also as Text formula - same response.&nbsp;------------------------------Amy Gosz------------------------------

amygosz1 · Answer

Can't believe I tried to make this so hard. I already have a field for 'days till next update is due' so I created a form rule that says if the # of days <= 61, then show the Add Annual Review button.

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

Forum Discussion

Create one Child Record per Year