Forum Discussion

AmyGosz1's avatar
AmyGosz1
Qrew Trainee
3 months ago

Create one Child Record per Year

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

6 Replies

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

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

      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

                      ToNumber([Related Home] & [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. 



      ------------------------------
      Amy Gosz
      ------------------------------
  • MikeTamoush's avatar
    MikeTamoush
    Qrew Commander
    3 months ago

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

      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
      ------------------------------
      • MikeTamoush's avatar
        MikeTamoush
        Qrew Commander
        3 months ago

        You can also put that in an if statement in the button itself:

        var text URL = Add Record URL;

        If([# of Days]<=61, $URL,"Review not Due")

        Just in case you want to use the button in a report or something, it will still be hidden.



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