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.
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:
For the Valid If you could use: IF(Len([Microchip])>=15,True,False)
Then set a message that goes back to the user if the microchip is less than 15.
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
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 ^
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 ) )
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?".
Looks good to me. What makes you think anything is wrong?
Is the column present in your form? Specifically, if you’re using a slice, is it in the slice?
It is now, thanks:)