How can i prevent users to add existing record?

[Mark_Holanda]

hi guys,

I just encountered an issue, i realized that my form is not checking if the record does exist before saving the data.

Can you please help me on how to achieve this? Here’s my member’s table https://www.screencast.com/t/NXld5cRbNF

I would like to check the First Name, Last Name and Suffix if doesnt exist before saving.

Thank you in advance!

[Steve · Accepted solution]

If all three column values can be guaranteed to be non-blank, you can use the following expression to answer the question, is this row’s combination of First Name, Last Name, and Suffix column values unique?:

ISBLANK(
  FILTER(
    "MyTable",
    AND(
      ([_THISROW].[First Name] = [First Name]),
      ([_THISROW].[Last Name] = [Last Name]),
      ([_THISROW].[Suffix] = [Suffix])
    )
  )
  - LIST([_THISROW])
)

If any of the three column values might be blank (or even if not), you’ll wan to use something like this instead:

ISBLANK(
  FILTER(
    "MyTable",
    AND(
      IN([_THISROW].[First Name], LIST([First Name])),
      IN([_THISROW].[Last Name], LIST([Last Name])),
      IN([_THISROW].[Suffix], LIST([Suffix]))
    )
  )
  - LIST([_THISROW])
)

The reason the first wouldn’t work if a column value might be empty is because of an oddity in how AppSheet handles the is-equal operator (=) and the is-not-equal operator (<>😞 if the left-side operand is blank, the expression evaluates as TRUE regardless of the right-side operand. The IN() function, though, does not behave this way.

[Mark_Holanda]

Steve:

ISBLANK( FILTER( “MyTable”, AND( ([_THISROW].[First Name] = [First Name]), ([_THISROW].[Last Name] = [Last Name]), ([_THISROW].[Suffix] = [Suffix]) ) ) - LIST([_THISROW]) )

Hi Steve,

thanks for the reply. Yes that should be unique (maybe i will improve the combination in the future to make it more unique). I will try your code and i will let you know.

thanks again!

[Mark_Holanda]

Thanks Steve that works!

[WoodMan]

This is very similar to my issue. I want to prevent adding a duplicate record but only for n seconds.

Also, forgive my ignorance but where are you suggesting this expression be inserted? I have only been doing this for a week and I keep finding more flexible features.

[Steve]

WoodMan:

I want to prevent adding a duplicate record but only for n seconds.

In order to do this, you would have to keep track of the date & time each row is created. When a new row creation is attempted, the app would compare the current time with the time the last row was created. So, assuming you have a column named Timestamp that records the date & time the row is created, you could use this Valid_If expression to require a minimum of 30 seconds between new row creations:

(NOW() >= (MAX(Table[Timestamp]) + "000:00:30"))

WoodMan:

where are you suggesting this expression be inserted?

As a column’s Valid_If column constraint:

help.appsheet.com

Valid_If Column Constraint

How to check if a given form input is valid based on its type.