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.
@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 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.
Been looking for this everywhere! My god! And itโs been a long time since I have been looking for an answer like this. Never found out how to do it until now. Brilliant!
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?
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.
Missing a minus sign:
@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.
@Steve, that is what I thought and I did but then the message displays incorrectly related to the logic of the validation
Please provide the exact expression after the swap.
@Steve. Apologies for the late reply. See attached Screenshot.
I will do the Swap at the end of our working day so that the system still function while we are using it while we have Visitors.
Thanks for the help so far
Please post a screenshot of the expression that doesnโt work.
Whoops! My bad! Change ISBLANK() to ISNOTBLANK().
@Steve. Damn I missed it as well
Thanks for your help with all of this and the other formulas. Your help is always appreciated
Also, an extraneous comma:
@Steve, does it mean I have to also change ISBLANk in the Valid_IF also to ISNOTBANK?
So not just in the โInvalid value errorโ section
No: the value is valid if the FILTER() expression returns a blank list (ISBLANK()), meaning there are no other rows with the same value.
Ok noted. Makes logical sense
@Steve, me again.
I want to also include the following into the 2nd part of the Validation Error where I used your code to test for DuplicatesโฆIF([VisitorIDNumberIndicator] = โSA IDโ
I use that in the first part of the validation Error.
How would I do that.
Here is the Screenshot of the current formula
BTW is the FALSE statement at the end before all IF STatements are terminated the way to do it if one wnats the formula not to validate? :-0
I Tried below, but Seems to not be working:
IF(
[VisitorIDNumberIndicator] = โSA IDโ,
IFS(
IF(
10-NUMBER(RIGHT(TEXT([IDNumberValidateOne] + [IDNumberValidateThree]),1)) = RIGHT([_THIS],1),TRUE, โID Number is INVALIDโ),
IF(ISBLANK(
FILTER(
โVisitorโ,
([_THISROW].[VisitorIDNumber] = [VisitorIDNumber])
)
โ LIST([_THISROW])
)
,TRUE
,โID Number ALREADY EXISTโ
)
)
,
FALSE
)
My CURRENT VALID_IF formula is below as a guide. I want to introduce the Validation Messages as above in the Validation Error, but could not find a way to do that using the Copy and Paste of the VALID_IF as the โฆIF([VisitorIDNumberIndicator] = โSA IDโ is already testing both conditions and I wanted to use it as-is
IF(
[VisitorIDNumberIndicator] = โSA IDโ,
AND(
IF(
10-NUMBER(RIGHT(TEXT([IDNumberValidateOne] + [IDNumberValidateThree]),1)) = RIGHT([_THIS],1),TRUE, FALSE),
ISBLANK(
FILTER(
โVisitorโ,
([_THISROW].[VisitorIDNumber] = [VisitorIDNumber])
)
โ LIST([_THISROW])
)
)
,
[_THIS]
)
Iโve put off addressing thisโฆ
Both of your expressions are poorly constructed and confusing, making troubleshooting difficult. Please try this Valid if expression:
IFS(
(โSA IDโ <> [VisitorIDNumberIndicator]),
TRUE,
ISNOTBLANK(
FILTER(
โVisitorโ,
([_THISROW].[VisitorIDNumber] = [VisitorIDNumber])
)
- LIST([_THISROW])
),
FALSE,
(
(10 - NUMBER(RIGHT((([IDNumberValidateOne] + [IDNumberValidateThree]) & ""), 1)))
<> RIGHT([_THIS], 1)
),
FALSE,
TRUE,
TRUE
)
and this as the Invalid value error expression:
IFS(
(โSA IDโ <> [VisitorIDNumberIndicator]),
"",
ISNOTBLANK(
FILTER(
โVisitorโ,
([_THISROW].[VisitorIDNumber] = [VisitorIDNumber])
)
- LIST([_THISROW])
),
"This ID Number ALREADY EXISTS",
(
(10 - NUMBER(RIGHT((([IDNumberValidateOne] + [IDNumberValidateThree]) & ""), 1)))
<> RIGHT([_THIS], 1)
),
"The SA ID Number is NOT VALID",
TRUE,
""
)
Note that the two expressions are the same except the first produces TRUE/FALSE results and the second produces error messages.
@Steve. As I said I am a power user and not a developer Getting into Appsheet type development at a ripe age of 53 Thank you always for your guidance on the forum. I always check out posts where you respond to other peopleโs problems. I learn a lot from that and from your assistance on this abortion of code that I tried. The assistance is much appreciated
Not trying to be critical, @Henry_Scott. Just ready to move on to other problems, so thought Iโd give you working code. Still happy to help you tweak it further if needed.
@Steve. I know. I welcome critique anyway. Only way I learn and improve.
Thanks for the code again
Hi I want to undestand if this solve the problem. That when you are creating a new record see if there is the value in the base and if you are only editing an existing record dont check any of this
User | Count |
---|---|
38 | |
35 | |
27 | |
23 | |
18 |