Forum Discussion

GunshamLakhiani's avatar
GunshamLakhiani
Qrew Cadet
6 years ago

Custom Data Rules

Hi,

I have a header table and a detail table where in i am trying to build a validation on my header table on certain field values which should not be repeated.

For example i have 3 fields Field A, Field B and Field C
I am entering values as A, B and C in the respective fields and save it after which i can enter the detail part of this record. Now i want to build a validation here where in i am entering another record in the header and if i again enter values as A, B, and C then it should stop me from doing this and throw a validation message.

Pl advise if the same is possible.


Regards

------------------------------
Gunsham Lakhiani
------------------------------
Custom Data Rules
Formulas and functions
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    6 years ago
    Yes, this is easy to do, but you will not be using Custom Data Validation.
    A formula field can be flagged as being unique.  That will mean that the system will not allow duplicates to be saved, the exception being that duplicate nulls are OK.

    You will make a formula text field such as

    List("-", [Field A], [field B], [Field C]) and flag it as being unique.

    Note that these field may not be lookup fields.  So if they are from a relationship, say from a table of Employees where the Key Field was [Record ID#], the formula would need to be

    List("-", ToText([Related Employee]), [field B], [Field C])

    and not

    List("-", [Employee name lookup field], [field B], [Field C])

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • GunshamLakhiani's avatar
      GunshamLakhiani
      Qrew Cadet
      6 years ago
      Hi Mark,

      Thanx a lot. It works perfectly fine and Yes one of the fields is from the relationship table so the ToText  worked fine as well.

      I am troubling you again for one more help here related to this. Would like to know if we can provide a custom message saying that "This combination of so and so values already exists" if the system detects these unique values.

      Regards
      Gunsham



      ------------------------------
      Gunsham Lakhiani
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        6 years ago

        Oh sorry, I forgot to mention the trick for that. Here is the trick. That field that you just created which is unique. Call it

        [This combination of so and so values already exists]


        Then try saving a duplicate and you will see how the error message reads. Basically you have to fake the field name to make the error message makes sense



        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        mark.shnier@gmail.com
        ------------------------------