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

[Swoopy]

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.

[LeventK]

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.

[Swoopy]

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

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

[LeventK]

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

[MultiTech]

LeventK:

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.

FYI:

How To Show a Custom Valid If Error Message Tips & Tricks 💡

You’ve been working long and hard to get your Valid If formula correct, and finally it’s doing what it’s supposed to - preventing duplicates, wrong info, bad dates, etc. from being entered by your users. But now you want to go a step further and provide the user with some info about WHY the data they entered is invalid. This is where the “Invalid value error” field comes into play. [Shared with CloudApp] Utilizing this features is extremely easy, due to the fact that most the hard work has …

[LeventK]

LeventK:

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.

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

[Swoopy]

Yes, it does !

[Steve]

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"
  )
)

[Swoopy]

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.

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

[Steve]

Swoopy:

I cannot make this work for my primary key having value from more than 1 column.

What does this mean?

Swoopy:

I think the last comma is not required.

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

[Swoopy]

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?

[Steve]

Swoopy:

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.

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

Swoopy:

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

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.

[Swoopy]

[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.

[Steve]

Swoopy:

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

[_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)).

Swoopy:

But the valid-if expression will be recomputed when user tap SAVE button.

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

[Swoopy]

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

and

saved and then I’ve this error,

Any clue?

[Steve]

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

[Swoopy]

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.

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

[Steve]

Swoopy:

the ObjectKey does not show on form view since it’s virtual column (computed key)

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

[Swoopy]

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.

[Steve · Accepted solution]

Swoopy:

Can computed key be shown in form view?

Sure, any column can be displayed in a form.

Swoopy:

Also, I do not need to display it in any view anyway because it seems for techies than the end-users.

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"
  )
)

[Swoopy]

Finally, congratulation to myself and BIG THANKS to Steve !

However, I modified the expression:

Valid If

AND(
   *** other validation (1) ***,
   *** other validation (2) ***,
   ISBLANK(
      FILTER(
         "table",
         AND(
            [key-column] = [_THISROW].[key-column],
            [_ROWNUMBER] <> [_THISROW].[_ROWNUMBER]
         )
      )
   )
)

Invalid value error

IFS(
   *** other validation error (1) ***,
      "error message (1)",
   *** other validation error (2) ***,
      "error message (2)",
   TRUE,
      "Duplication found by combination of 🔑 fields !"
)

Notes: The key fields have the key symbol in their captions like this,

This does not prevent the system error message shown at the top of form. But there is a better guideline for users (who are not familiar with the relational database terms) of what they need to do.