Don't allow to submit value in column , if already...

M_S · 09-05-2021 04:54 AM

Hello,

I don’t want to allow users to submit duplicate records based on specific column values,
I am trying the below expression-

ISBLANK( FILTER( “مساعدة زواج”, AND( ISNOTBLANK([CPR]), ([CPR] = [_THISROW].[CPR]) ) ) )

but no luck.

WillowMobileSys

Your table is currently relying on the AppSheet Supplied column [_RowNumber] to be the key. I would NOT recommend that. There is no guaranteed that rows will always be assigned the same sequential number.

Instead, since you want the rows to be unique based on a collection of columns, you want to check the Key property in each of the columns that make the row unique. AppSheet will automatically construct a [_ComputedKey] column based on concatenating those columns and set it as the key value.

Afterwards, if a user tries adding a row with those same values in those columns, you will be warned that the row already exists. No additional validation is needed.

Example

I set 3 columns as the columns that make the row unique - i.e. be part of the row key

[_ComputedKey] column is automatically created

Steve

Change this:

ISBLANK(
  FILTER(
    “مساعدة زواج”,
    AND(
      ISNOTBLANK([CPR]),
      ([CPR] = [_THISROW].[CPR])
    )
  )
)

to this:

ISBLANK(
  FILTER(
    “مساعدة زواج”,
    AND(
      ISNOTBLANK([CPR]),
      ([CPR] = [_THISROW].[CPR]),
      ([_ROWNUMBER] <> [_THISROW].[_ROWNUMBER])
    )
  )
)

M_S

Hello Steve,

I tried as you explain well, but still no luck. I have 1 column CPR, which is filled up by users and must be unique.

let me explain to you what I did-
step1- CPR column (Key)
step2- Valid if, I am using the below code-

ISBLANK(
FILTER(
“مساعدة زواج”,
AND(
ISNOTBLANK([CPR]),
([CPR] = [_THISROW].[CPR]),
([_ROWNUMBER] <> [_THISROW].[_ROWNUMBER])
)
)
)

when I submit values from the FORM with the same CPR number, it replacing the existing values.

Excel

FORM

Steve

Please post a screenshot of the CPR column configuration screen, and one of it’s entire current Valid If expression.

M_S

When I submit the same CPR number, it’s replacing the existing record but I want to stop submitting the form if the same CPR number already exists.

CPR Column Configuration -

Steve

It appears properly configured to work the way you want.

Please post a screenshot of the view you use to add a new row. Please take the screenshot of the app emulator in the app editor, and please include the View and Table displays below the app screen.

SkrOYC

What I don’t understand is that AppSheet should prevent this by default. Have you tried without any expressions? I mean, I get this default behaviour in my app. I added a Edit expression that makes that field editable just if the field is blank, then it frozes the value (since it’s the key and I don’t want anyone to mess it up after it was created) but that’s about it.
Your case should be the same as mine since this is one of the very few times I allow the user to input the key by themselfs

Steve

Note that a key column value is only editable before the row is saved the first time. The key column value is read-only thereafter; no Editable_If expression needed.

M_S

As you asked, please see the below screenshot.

SkrOYC

Could you try this on Valid_If ?

NOT(
  IN(
    [_THIS],
    SELECT(
      مساعدة زواج[CPR],
      [_ROWNUMBER] <> [_THISROW].[_ROWNUMBER]
    )
  )
)

Don't allow to submit value in column , if already exist in excel table column