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.
Solved! Go to Solution.
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.
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.
@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.
User | Count |
---|---|
61 | |
25 | |
14 | |
11 | |
6 |