Forum Discussion

DavidDavid2's avatar
DavidDavid2
Qrew Trainee
8 years ago

Unique Values

I have two tables with a relationship. Table A provides the unique value of Tree_GPS_Coordinates to Table B. Table B uses the lookup value of Tree_GPS_Coordinates and collect data for fields Year and Height.

Goal: I want to prevent any duplicate entries for trees in a given year. (e.g. Tree1 cannot have two 2017 measurements)

Problem: Year field cannot be marked as unique because many trees will be marked as 2017, 2016, 2015 etc. Tree_GPS_Coordinates is a lookup field.

Question: How do I create field formula to ensure there are not duplicate entries? I continually receive the message "This formula is incompatible with this field being marked unique. Please change the formula or uncheck the unique checkbox.
Ask about Quickbase functionality
Errors
Formulas and functions
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    No problem.

    The lookup fields cannot be used in a unique formula, but the field which is probably called [Related Tree Coordinate] can be used in a formula field marked as Unique.
    • DavidDavid2's avatar
      DavidDavid2
      Qrew Trainee
      8 years ago
      QB is unable to find the Related field and displays "This table does not contain a field called Related Tree TREE#. To see available fields, click the Fields & Function dropdown to the right of the formula box."

    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      8 years ago
      What is the field of the field which is on the right side of the relationship between the Tree_GPS_Coordinates Parent and the children Tree measurements?

      That is the field to use.
  • ShawnRenn2's avatar
    ShawnRenn2
    Qrew Trainee
    8 years ago


    The way I'd solve for this would be two parts,

    Part 1: Create a Formula Text field on Table be to combine the "Year" field on Table B, and the "Related Tree". (for now lets call this the "Tree Year Formula" field,
     - The formula will be as follows - [Related Tree] & " " & [Year]

    (Note that you can get pretty creative with the concatenating formula to combine values, ie, between the quotes if you wrote " last measured " the returned value would read "Related tree last measured 2017" for example.

    The second part is just a scalability safeguard since Formula fields can't currently be used as the Key field for a table. Let's say at some point you needed this value to be the "Key" field, I always suggest taking one extra step and making one final field (which will be your "key") and call it "Tree Measured Year". This field will be a plain-old text field, BUT it will be populated with a Dynamic form rule.

    The form rule will need to be written as follows -  when "Tree Measured Year" is equal to Blank then change "Tree Measured Year" to the value in the field "Tree Year Formula" and "Make Read-Only"(blank just means no selection)

    Once all this is done, any time a user creates a Measurement from a "Tree" Record, your key field will autopopulate with the appropriate naming convention, and the user will NOT be able to save the record if another record...with the added bonus that this field can now be used as a key field if need be.

    Two quick tidbits...
    1. If you do not need the tree/year to be the key field, you can skip the second part and simply make the formula field "must be unique"
    2. The second part will not be an option if your tree measurements are being imported as it relies on a Dynamic Form Rule which (unlike a formula) requires that a record be manually edited/saved to "Fire".

    Hope htis helps!