Discussions

 View Only
  • 1.  Formula Queries - Entering Duplicates

    Posted 11-06-2021 15:42
    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.
    ------------------------------


  • 2.  RE: Formula Queries - Entering Duplicates

    Posted 11-06-2021 16:40
    Edited by Mark Shnier (Your Quickbase Coach) 11-06-2021 16:40
    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
    ------------------------------



  • 3.  RE: Formula Queries - Entering Duplicates

    Posted 11-06-2021 17:06
    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
    ------------------------------



  • 4.  RE: Formula Queries - Entering Duplicates

    Posted 11-10-2021 08:16
    Hey Mark, I had sent you a private thank you but wanted to post the final formula here for others. Thanks again for your help! 

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

    Todd

    ------------------------------
    Todd Molino
    ------------------------------