Iโd like to have an error message for duplicate entries in a column on a new form and set the length to 15 characters, to avoid mistypes, but iโm going in circles. I was using conditional formatting as a bandaid to a google sheet but cant figure out an equivalent here.
Solved! Go to Solution.
In the microchip columnโs Valid if expression:
ISBLANK(
IFS(
ISNOTBLANK(
FILTER(
"the-table",
([_THIS] = [microchip-column])
)
- LIST([_THISROW])
),
"Duplicate microchip ID!"
)
)
In the same columnโs Invalid value error expression:
IFS(
ISNOTBLANK(
FILTER(
"the-table",
([_THIS] = [microchip-column])
)
- LIST([_THISROW])
),
"Duplicate microchip ID!"
)
Note that the first is merely the second wrapped in ISBLANK().
In both, make appropriate substitutions for the-table
and microchip-column
.
No need to make the microchip column required. Donโt use the microchip column as or part of the key. I recommend using UNIQUEID() to set the key column value.
If you want to notify the user if a microchip value is not the expected 15-character length but not treat it as an error, consider adding a (virtual?) column of type Show to display a message in that case.
Hey @Eli_b hope youโre doing awesome! Check out this articleโฆ If you can be a little more specific, we can help a little better.
Itโs a microchip field for foster animals. Right now weโre using a spreadsheet that anyone can edit so we often have issues with incomplete, leaving a number off the end or a putting in a number thatโs already assigned to another animal. The chips we most often use have 15 numbers. Iโve got the column in the app set as text though. That seemed like a better fit.
Hey @Eli_b this should be fairly simple as long as you can make this a required column for your table. Go to Columns and select this particular column and you want to do 2 separate things:
Set the Data Validity:
Set the Key to On. This will auto check for duplicates for you and not allow them. You can have more than one column that is a key for the table if you also have another Key.
If you can have a record with the microchip field blank then this gets a little trickier. Let me know and Iโll see if I can come up with something else. Also, for all of your current bad data, the next time someone opens a record to edit it, it should force them to fix the microchip column before saving. Hope this helps!
It would be convenient to not have the microchip field required but its not a huge deal. I can always fill it will all zeroโs until getting the actual number. Id rather do that then have dups:). Iโm having a problem with having multiple keys. Once I add the microchip as a 2nd key both the L# and the microchip allow duplicates. Iโm learning as I go so it seems like I fix one thing but create 3 problems lol. I had L# set to sequentially generate for new forms but cant do that with a key.
In the microchip columnโs Valid if expression:
ISBLANK(
IFS(
ISNOTBLANK(
FILTER(
"the-table",
([_THIS] = [microchip-column])
)
- LIST([_THISROW])
),
"Duplicate microchip ID!"
)
)
In the same columnโs Invalid value error expression:
IFS(
ISNOTBLANK(
FILTER(
"the-table",
([_THIS] = [microchip-column])
)
- LIST([_THISROW])
),
"Duplicate microchip ID!"
)
Note that the first is merely the second wrapped in ISBLANK().
In both, make appropriate substitutions for the-table
and microchip-column
.
No need to make the microchip column required. Donโt use the microchip column as or part of the key. I recommend using UNIQUEID() to set the key column value.
If you want to notify the user if a microchip value is not the expected 15-character length but not treat it as an error, consider adding a (virtual?) column of type Show to display a message in that case.
@Steve I was looking for that part of the docs that we had made that literally had all of the expressions for thisโฆ But when I clicked on the link to โpreventing duplicatesโ it takes you to the list docsโฆ Do you remember what Iโm talking about?!
The part youโre talking about probably got wiped out in the list expressions doc rewrite. What do you want to see? Iโll try to reproduce it.
I think it was just that ^
Added:
Thanks Steve. I was just needing this today for a personal project Iโm working on.
" If you want to notify the user if a microchip value is not the expected 15-character length but not treat it as an error, consider adding a (virtual?) column of type Show to display a message in that case."
Could you expand on the steps for this? I tried a few things but could still have more or less than 15 and no notification.
Create a virtual column with an App formula expression of ""
, a Show? expression of (LEN([microchip-column]) <> 15)
, Type of Show, Category of Text, and Content expression of "Is microchip ID value correct?"
.
What am I doing wrong?
Looks good to me. What makes you think anything is wrong?
Nothing happens
Is the column present in your form? Specifically, if youโre using a slice, is it in the slice?
It is now, thanks:)
Hopefully the last question, Can I change the color of that text?
Iโm not sure, actually. You could try using a format rule.
Thatโs awesomeโฆ I think the key knowledge nugget is that the new record youโre creating doesnโt yet exist so far as last expressions are concerned
Thanks for the help guys. I made a row with a uniqueID. Im trying to make this app as simple as possible for the volunteers that are not a fan of technology. The spreadsheet theyre using is a mess and time consuming to keep updated. Would it be a bad idea to use this for our Identifier for each dog? Right now i just have a bunch of empty profiles with unused L#s. I know if they have to go back and forth to see what the next l# is especially when adding a large litter, theyre not going to give up the spreadsheet. =ARRAYFORMULA( IFS( ROW(C:C) = 1, โL#โ, LEN(C:C) = 0, IFERROR(1/0), ROW(C:C) > 1, โL20-โ & ROW(C:C) + -1 ) )
Hello Everyone,
I badly need help.
I want to make sure that no duplicate data entry occurs
The fields in each row are as follows: (State, City, House Number)
Example
State | City | House Number | Duplicate (y/n) |
---|---|---|---|
Dhofar | Salalah | 234 | N |
Dhofar | Hafa | 234 | N |
Dakhiliyah | Salalah | 234 | N |
Dhofar | Salalah | 234 | Y ( Duplicate) |
ISBLANK(
FILTER(
"table",
AND(
ISNOTBLANK([State]),
([State] = [_THISROW].[State]),
ISNOTBLANK([City]),
([City] = [_THISROW].[City]),
ISNOTBLANK([House Number]),
([House Number] = [_THISROW].[House Number])
)
)
)
Thank you so much, you saved me, man
The formula works fine when add a new record. When I modify it give me an error
Oops! Yep! I missed a line! Try the following:
ISBLANK(
FILTER(
"table",
AND(
ISNOTBLANK([State]),
([State] = [_THISROW].[State]),
ISNOTBLANK([City]),
([City] = [_THISROW].[City]),
ISNOTBLANK([House Number]),
([House Number] = [_THISROW].[House Number]),
NOT([_ROWNUMBER] = [_THISROW].[_ROWNUMBER])
)
)
)
How about this it worked with me. is it correct?
ISBLANK(
FILTER(
โtableโ,
AND(
ISNOTBLANK([State]),
([State] = [_THISROW].[State]),
ISNOTBLANK([City]),
([City] = [_THISROW].[City]),
ISNOTBLANK([House Number]),
([House Number] = [_THISROW].[House Number])
)
)
- LIST([_THISROW])
)
Looks good to me!
Thank you, man, you are a savior
this save a ton of work! thanks!
User | Count |
---|---|
43 | |
30 | |
25 | |
23 | |
13 |