System Error Messages

In the view shown below, SO# is the PK (not auto generated). In the image, I have attempted to add a new record with the same PK (10724) as another record, understanding that it should throw an error, which it did.

Question 1: Is there a way I can change the text? I would like it to say, for this view) “There is already a project with SO ‘10724’. Please enter a unique SO” or somethng along those lines.

Question 2: Is there a way that I can trigger/offer a secondary action when this happens? Example, since there is an existing record, I would like to offer the option to jump to the details view for that record.

Configure a Valid if expression for the column to ensure the value is unique, and a Invalid value error expressions to generate the desired message.

Not trivially within the form to create a new row.

@Steve, could you possibly give me a clue on a valid_if expression to verify uniqueness?

I tried the following, but this doesn’t work. I may be on the wrong track here:

NOT(
IN([_THIS].key, LIST([key]))
)

Try:

ISBLANK(
  SELECT(
    MyTable[_ROWNUMBER],
    ([_THISROW].[SO #] = [SO #])
  )
  - LIST([_ROWNUMBER])
)

This finds all rows in the MyTable table that have the same SO # column value as this row and produces a list of their row numbers. This row’s row number is then removed from that list, leaving only the row numbers of other rows with the same SO # column value. If that list of other rows is empty (blank), this row is the only one with that value.

2 Likes

@Steve Thanks, that did it. I found this to be more in line with how my brain works, however:

COUNT(
SELECT(Project[_ROWNUMBER], ([_THISROW].[SO] = [SO]))
) = 0

Slightly related question: why does the _RowNumber column get added automatically to my tables? The data is coming from a MySQL database, so row numbers are irrelevant, right? Could I have also used the PK ([SO]), like this:

SELECT(Project[SO], ([_THISROW].[SO] = [SO])) ??
Edit - so that did work as I suspected. Brings me back to: why is _RowNumber auto-created for these tables and can I safely delete it?

Again, probably part of the spreadsheet legacy.

I don’t know.