Forum Discussion
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
------------------------------
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
------------------------------
- JimHarrison10 months agoQrew Champion
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
------------------------------- AlexWilkinson10 months agoQrew Assistant CaptainJim: 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 pairingOR(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