If you've marked this formula field as unique (must be unique is checked), you won't need any form rules, just attempt to save a new record with a known duplicate value and the standard built-in error message will appear.
Hey Mez,
Thanks for that. I created a formula field called "Quarter-Year Duplicate Check" where I combine the text for the two fields using the formula "[Reporting Quarter] & "-" & [Reporting Year]". The text it returns is like "Q1 - 2025", but I'm having trouble figuring out the next step from here.
On my form, I can't think of how to add a rule that will on record-save, check for duplicates records. Any other suggestions?
If you've marked this formula field as unique (must be unique is checked), you won't need any form rules, just attempt to save a new record with a known duplicate value and the standard built-in error message will appear.
Mez,
Ahh, thank you! I just made the field unique and now it works. Do you know if there's a way I could change the built-in error message? I want it to be more readable for users? I assume for this, I would have to do it using a form rule?
Please let me know.
No way that I know of to change the built-in error message. There are few different techniques you can use provided that your users will only use the built-in form, and the table date remains small. If your users may import data, then you'll want to also consider custom data rules.
*working assumption is only using the built-in form, you'll create two new formula fields: 1. [possible duplicate?] as checkbox (leveraging formula queries), 2. [ui custom message] as text. (naming for convenience only)
possible duplicate? checkbox field will leverage the GetRecords() function inside the Size() function and then inside an If() if you get a record, this means there is an existing record.
In this example, we are looking for existing records for Type Quartile and that other Record ID is not self.
var text query = "{18.EX.'" & [Type Quartile] & "'} AND {3.XEX." & [Record ID#] & "}";
If(
ToBoolean( Size( GetRecords( $query ) ) ), true,
false
)ui custom message field uses another If() to check the value of [possible duplicate?] and display a message. As the user is entering data, if there is a record returned, then this message will display on the form; we also set (check) the form to use alternate label text of nothing for [ui custom message], this way the message just appears and will disappear as the user backs-out the data. Doesn't guarantee the user will back-out what's been entered, so you then can create a form rule, but your message will still somewhat be limited as you have to craft this inside the 'abort the Save'. In this example the form rule is When the record is saved (after checking), and [existing?] is equal to 'checked', abort the Save message is same as the [ui custom message].
If(
[existing?], "You are attempting to create a duplicate record. Please check your entries for Type and Quartile.",
null
)Hopefully this helps.