Skip to main content
Question

Formula Queries - Entering Duplicates

  • November 6, 2021
  • 0 replies
  • 49 views

Forum|alt.badge.img+5
I have a Clients table where Record ID# is the key field. My goal is to prevent a User from adding a Client that already exists in the system. I've set up a data form rule that works great for preventing a duplicate record from being added if the SSN entered already exists. However, if I want to go into an existing record in Edit mode to update any other personal information such as an email or phone number, I cannot save the record because the system thinks that the current record I'm in already exists, which it does but I'm not "adding" a new record, I'm merely editing one. Below is the formula query that I'm using directly from the QuickBase training video: 

If(
Size(GetRecords("{22.EX.'" & [SSN] & "'}"))
> 0,
"A CLIENT ALREADY EXISTS WITH THIS SSN."
)

I've tried manipulating the ">0" portion of the formula but nothing seems to work. Any help would be greatly appreciated!

------------------------------
Todd M.
------------------------------
This topic has been closed for replies.

MarkShnierYou
Forum|alt.badge.img+24
Try this
Size(
GetRecords("{22.EX." & [SSN] & "}"),3))

I am still trying to wrap my head around when we need to use the single quotes but I think this will work.

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------

MarkShnierYou
Forum|alt.badge.img+24
I think I missed a point in you post.

Try this

IF(IsNull([Record ID#]) and 
Size(
GetRecords("{22.EX." & [SSN] & "}"),3)) >0, "A CLIENT ALREADY EXISTS WITH THIS SSN.")

That should mean that the test will only be active in Add Mode.

You can also have another field that does not block the save which is just informational.

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------