Forum Discussion

JimHarrison's avatar
JimHarrison
Qrew Champion
6 months ago

Custom Data Rules testing for Unique

I am looking for how to write a custom data rule to handle a Unique field. There doesn't seem to be any documentation or I have not found it yet. If you know the syntax for handling a unique field in...
JimHarrison's avatar
JimHarrison
Qrew Champion

We are seeing Webhook errors when users import records with non-unique values. I need to alert Users they are attempting to import duplicates on a field that is marked unique in the table. I want to write a custom data rule that throws an error when the User imports a non-unique value.



------------------------------
Jim Harrison
transparency = knowledge + understanding : The Scrum Dudes
------------------------------
ChayceDuncan's avatar
ChayceDuncan
Qrew Captain
6 months ago

Is the unique field the key of the table or just a entry field? 

One option if it's not the key field is to use a formula query and count the # of records that have the same value and if it's greater than or equal to 1 then you know it's not unique since it's finding records in the table with the same value. 

Is there a reason that the native unique field setting to make sure values are unique wouldn't work? You would still get a message that pops up during import that there are duplicate values



------------------------------
Chayce Duncan
------------------------------
  • JimHarrison's avatar
    JimHarrison
    Qrew Champion
    6 months ago

    I used a formula field to compare the new/updated input to the Unique field and that should solve the problem.



    ------------------------------
    Jim Harrison
    transparency = knowledge + understanding : The Scrum Dudes
    ------------------------------
    • AlexWilkinson's avatar
      AlexWilkinson
      Qrew Assistant Captain
      6 months ago
      Jim: Here's an actual working example of using formula-queries to trap bad records when the user attempts to save or import them. Below is the formula for the custom data rule of a Contacts table. Here, a valid Contact must always have a Related Person and can be a 

      (1) Person at an Organization, provided that there is not an existing record with that unique pairing
      OR
      (2) a solo-Person (not connected to any organization), provided that a unique text-formula based on name and email address or phone number does not already exist.

      var text queryPair=
          "{'6'.EX.'" & [Related Person] & "'}AND" &
          "{'7'.EX.'" & [Related Organization] & "'}AND" &
          "{'3'.XEX." & [Record ID#] & "'}";
         
      var text queryFormula=
          "{'72'.EX.'" & [Unique Contact Formula] & "'}AND" &
          "{'3'.XEX."  & [Record ID#] & "'}";

      var bool dupe=If(
          Size(GetRecords($queryPair,Dbid()))>0,true,
          Size(GetRecords($queryFormula,Dbid()))>0,true,
          false
      );

      var bool noPersonOrg=Nz([Related Person],0)=0;

      If(     $noPersonOrg,
          "Error: Missing person. A contact must have a Related Person.",
              $dupe,
          "Error: Duplicate record. This contact already exists as a solo-person or a person-org pair."
      )

      Alex Wilkinson