I am using a Valid If expression to flag up a duplicate entry which works great.
NOT(IN([PONumber], SELECT(Customer Orders[PONumber], [CustName]=[_THISROW].[CustName])))
The problem I have is if I go back and need to edit the row, the duplicate warning flags up and prevents me from saving any changes. What am I missing?
Your formula will work only when you add a new record. When you are modifing it, you need to add the existing value as well.
OK. Is there a a formula that will work only when creating a new record? I am just needing something to prevent duplicate orders being entered. I use a UniqueID() as my key.
Any suggestions?
You can include a condition to exclude the record you’re looking at:
NOT(
IN([PONumber],
SELECT(Customer Orders[PONumber], and(
[CustName]=[_THISROW].[CustName],
[PONumber] <> [_thisrow].[PONumber]
))
)
)
That works. Thanks. I am now needing the same thing, but only showing a duplicate warning, but still allows the record to be saved.
Okay, instead of using that formula in the valid if, where it will restrict the field from creating duplicates, move it to the Description - or create a Show (text) column - and create some warning that the user can see.
A combination of IF() and concatenate() like:
if(IN([PONumber],
SELECT(Customer Orders[PONumber], [CustName]=[_THISROW].[CustName])
),
Concatenate("PO Number (WARNING: POnumber (", [PONumber], ") has already been created.)"),
"PO Number"
)
Thanks MultiTech_Visions, that works just great.
Any time.
@Aleksi how would one achieve that? Is there a “state” in Appsheet to know if it is a New Record or a Record being modified? - “Your formula will work only when you add a new record. When you are modifing it, you need to add the existing value as well.” @MultiTech_Visions
I have the same challenge as what @James_Fussell1 had originally. On New Record the Duplicate validation work and if it is not a Duplicate then it Saves the Record. The challenge comes in that when you Modify the Record by editing one of the fields where a Duplicate Test is not required, then one cannot Save the Record, because the Original Field is complete and thus fails the Duplicate validation
This is the pattern I prefer:
ISBLANK(
FILTER(
"MyTable",
([_THISROW].[MyColumn] = [MyColumn])
)
- LIST([_THISROW])
)
FILTER("MyTable", ...) finds all of the rows in the MyTable table that match the given criteria (...; see (2)).
([_THISROW].[MyColumn] = [MyColumn]) matches rows where the MyColumn column value matches the MyColumn column value of the form.
... - LIST([_THISROW]) removes the row currently in the form from the list of rows found by the FILTER() in (1).
ISBLANK(...) asks, “is the list of rows produced by (3) empty?”
(1) finds all rows that duplicate the current form’s value, possibly including the form row if it had previously been saved. (3) removes the form row from this list, producing a list of rows other than the form row that have the duplicate value. (4) the confirms that there are no other rows with duplicate values.
@Steve - thanks let me try this
Steve:
ISBLANK( FILTER( “MyTable”, ([_THISROW].[MyColumn] = [MyColumn]) ) - LIST([_THISROW]) )
@Steve my formula does not work using your example.
It works on New Record being created, but…
If I go back into the record and Modify other fields then I get the error that the duplicate field exists.
Here is my edited formula
ISBLANK(
FILTER(
“Visitor”,
([_THISROW].[VisitorIDNumber] = [VisitorIDNumber])
)
I have this in the VisitorIDNumber’s Valid_If
Is VisitorIDNumber the table’s key column?
Nope I have another Column as Key that is hidden
VisitorIDNumer is a Customer Number
Please provide a screenshot of the complete expression as displayed in Expression Assistant, and of the app with the error displayed.
@Steve - see attached Screenshots. I have an additional Validation on VisitorIDNumber in addition to the Duplicate Check
@Steve , thanks I removed the extra comma and added the “-” minus sign. Seems to be working
On my “Invalid Value Error” formula the formula Error Messages I had to change around to evaluate and display as per the scenario, but the messages does not relate to the formulas
IF(
IF([VisitorIDNumberIndicator] = “SA ID”, IF(10-NUMBER(RIGHT(TEXT([IDNumberValidateOne] + [IDNumberValidateThree]),1)) = RIGHT([_THIS],1),TRUE, FALSE), [_THIS])
,
“This ID Number ALREADY EXISTS”
,
IF(ISBLANK(
FILTER(
“Visitor”,
([_THISROW].[VisitorIDNumber] = [VisitorIDNumber])
)
-LIST([_THISROW])
)
,
“The SA ID Number is NOT VALID”
,
[VisitorIDNumber]
)
)
So “This ID Number ALREADY EXISTS” should be with “your” formula and “The SA ID Number is NOT VALID” should be with the top part of the validation formula
It appears all you need to do is swap the text of those two messages.
