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?
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.
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"
)
@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
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 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
@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