Customize System Text "There is already a row with the key"

Where in editor can I change the wording of “There is already a row with the key”?

Because I have not found this in UX > Localize > Customize System Text.

1 Like

You can use Error if invalid property with a Valid_If expression of:

AND(
    TRUE,
    IN([Key],TableName[Key])
)

and enter desired text to the Error if invalid property.

6 Likes

Are the AND and TRUE required in the expression? Can it be only

IN([Key],TableName[Key]) ?

@Swoopy
They are explicitly needed. Otherwise the expression itself alone will produce a list of key column’s values in a dropdown.

2 Likes

FYI:

5 Likes

Btw, I haven’t realized one thing here; this might also create a negative benchmark when you are editing an existing record actually.

1 Like

Invalid value error expression:

IFS(
  ISNOTBLANK(
    FILTER(
      "table",
      AND(
        ([column] = [_THIS]),
        ([_ROWNUMBER] <> [_THISROW].[_ROWNUMBER])
      )
    )
  ),
    "duplicate value"
)

Replacing table and column appropriately and duplicate value as desired.

Valid if expression (just copy in the Invalid value error expression as above and wrap it in ISBLANK()):

ISBLANK(
  IFS(
    ISNOTBLANK(
      FILTER(
        "table",
        AND(
          ([column] = [_THIS]),
          ([_ROWNUMBER] <> [_THISROW].[_ROWNUMBER])
        )
      )
    ),
      "duplicate value"
  )
)
5 Likes

I cannot make this work for my primary key having value from more than 1 column. Also, does your expression produce error?

AND(
          ([column] = [_THIS]),
          ([_ROWNUMBER] <> [_THISROW].[_ROWNUMBER]),
)

I think the last comma is not required.

Capture

If we need to work that hard in order to just change the error message, this slogan might not be totally correct. :smile:

Yes, it does !

What does this mean?

True, it’s a typo. I’ve removed it from my prior post.

1 Like

I have table’s key consisting of 3 columns, let say [Col1]&[Col2]&[Col3]. How to modify your expression? in order to customize the AppSheet error message.

Does your expression prevent saving data when editing? How does it differentiate between adding a new row and editing an existing row?

A key column contains only one value, regardless how it is generated. My expression is sufficient for your need.

The key column value cannot change once the row has been saved the first time. AppSheet will not even try to recompute the key column value, so the Valid If expression for the key column will never be applied when the row is edited.

2 Likes

[column] = [_THIS]

My point is this part of your expression. If the key is made from other columns then it becomes a virtual column.

My understanding is that [_THIS] is recognized by physical columns. If [column] is virtual, will it work with [_THIS]?


“AppSheet will not even try to recompute the key column value”

But the valid-if expression will be recomputed when user tap SAVE button. I ask this point because LeventK’s work-around prevent saving on edit form no matter what column you are making change, the form cannot save.

[_THIS] is recognized for column constraints. Valid If is a column constraint. Yes, it will work even for a virtual column. If you’d prefer, you can replace [_THIS] with the equivalent reference [_THISROW].[column] (replacing column with the column name)).

Nope. Columns used in a computed key will also not be allowed to change.

2 Likes

The ObjectKey is key (virtual) column. Then I do this,

captured3

and

captured4

saved and then I’ve this error,

Any clue?

Swap the expressions; the expression you have for Valid If should be in the error message and vice versa.

1 Like

Done but the ObjectKey does not show on form view since it’s virtual column (computed key). At the top of form, still the same error message.

Capture

How to show the “duplicate value” on form view?

So you don’t want the user to see the key, but do you want a message to display?

1 Like

Can computed key be shown in form view? I try to display it but nothing. Also, I do not need to display it in any view anyway because it seems for techies than the end-users.

Sure, any column can be displayed in a form.

That makes sense. Given that, here are the two alternatives I see:

  1. Add a column of type Show that displays the error message when the new’s key column duplicates that of an existing column.

    OR

  2. Configure the Valid If expressions of the (three?) other columns that are used to construct the key column value to produce an error.

Myself, I use approach (2).

Invalid value error expression for each of the key component columns (not the key column itself):

IFS(
  ISNOTBLANK(
    FILTER(
      "table",
      AND(
        ([key-column] = [_THISROW].[key-column]),
        ([_ROWNUMBER] <> [_THISROW].[_ROWNUMBER])
      )
    )
  ),
    "duplicate value"
)

replacing table and key-column as appropriate.

Valid If for each of the key component columns:

ISBLANK(
  IFS(
    ISNOTBLANK(
      FILTER(
        "table",
        AND(
          ([key-column] = [_THISROW].[key-column]),
          ([_ROWNUMBER] <> [_THISROW].[_ROWNUMBER])
        )
      )
    ),
      "duplicate value"
  )
)
5 Likes