@wsaico id you figure it our or get any help with this? I have run into the same issue and posted about it but no response.
sorry for my English.
The formula works great but …
What to do when I want to edit existing data? When I want to save the edited data, I get information that “data exists”.
This is my formula:
NOT(IN([_THIS],SELECT(skazani[sygnatura], [_THISROW].[sygnatura]=[sygnatura])))
Hope this is understandable 
Your expression’s SELECT function returns the current row, which of course includes that row’s value. See the original post’s criteria within the SELECT function that use the following, which returns all the rows except the current row:
- the table’s key (ID) column
- the “does not equal” (<>) operator
1 Like
The formula works great. I’d like to know if it is possible to allow users to overwrite it and enter the duplicate entry anyway–how do I do it?
It is not possible for a user to force the creation of a new row with a duplicate key column value using a form view, since the system itself prohibits the duplicate key. An action, however, can add a new row with a duplicate key column value. Both rows will exist until a sync occurs, at which point the original will be removed.
2 Likes
I’m making a smartphone stock control app, they are identified by the IMEI number which is a unique number, I need to validate an output to avoid duplicate output. To register the output of these smartphones I use two tables, one that registers the output and one that registers the smartphone, that way I can output multiple smartphones in a single output record.
That’s where my problem begins, I can’t find or develop an expression that can prevent a duplicate item, I even used the expression:
NOT(IN([_THIS], SELECT(ESTOQUE 9000_SAIDAS_ITENS[IMEI 1], [ITEM ID] <> [_ThisRow].[ITEM ID])))
, but in this way it is not possible to output again on the smartphone, I need that for [SAIDA ID] the [IMEI 1] is always different.
1 Like
@matt_dev
There’s a bug with this validation formula when processed through the app editor currently.
- The validation formula works in the actual app, just inside the app editor’s app preview does the validation formula not work.
Take Heart
- Your formula works… it’s just a bug
1 Like
It works however not as I would like, in this formula NOT(IN([_THIS], SELECT(ESTOQUE 9000_SAIDAS_ITENS[IMEI 1], [ITEM ID] <> [_ThisRow].[ITEM ID]))) the Item represented by IMEI 1 never more can be entered, so I tried the formula: NOT(IN([_THIS], SELECT(STOCK 9000_OUTPUT_ITENS[IMEI 1], [OUTPUT ID] <> [_ThisRow].[OUTPUT ID]))) which to my surprise worked exactly like the first one, I don’t know where I’m going wrong.
I need the value of IMEI 1 not to be repeated for SAIDA ID
@MultiTech Hey Matt, unfortunately, this expression isn’t working in my desktop version of the app either.
Can you check and see if you are getting the same issue?
This would work for enforcing duplicate checks for a single column. What if I want to enforce uniqueness across a combination of columns? In my case, I have a unique transaction ID for every transaction but want to enforce uniqueness across Transaction date, amount, account name and expense category, in order to ensure users do not enter the same combination again. Any tips for this, please?
Explain [tableid] if the name of the table dont work??
What’s meant by [TableID] is the column that is designated as the table’s key.
1 Like
Hi Everyone @MultiTech @dbaum
My expression is not working here. I want that if [get entry no] equals then [get entry no] then no entry. But when I edit it, it gets edited.
NOT(IN([_THIS], SELECT(SCAN SHEET [GATE ENTRY NO], [GATE ENTRY NO] <> [_THISROW].[GATE ENTRY NO])))
Valid if Error Message
IF(NOT(IN([_THIS], SELECT(SCAN SHEET [GATE ENTRY NO], [GATE ENTRY NO] <> [_THISROW].[GATE ENTRY NO]))), “Duplicate gate entry number found”, “”)
1 Like
There’s a problem with the logic here, you’re essentially saying in your SELECT():
- Go to the SCAN SHEET and get me all the “Numbers” where
- the Number doesn’t not equal the number I’ve selected
So no matter what… you’re never going to get your number.
- Go to the sheet and get all the numbers, where if the number is 5 (because that’s the number I’ve selected in my form) then don’t show me that number.
The solution would be to separate out the “Number” we’re looking for, and the “ID” of the records.
- Create a column in your “Scan Sheet” table to ID each record
- Initial value: UniqueID()
- Visibility: hidden
- Then your updated formula would be more like what’s presented in this thread
NOT(IN(
[_THIS],
SELECT(SCAN SHEET[GATE ENTRY NO],
[Scan_Sheet_ID] <> [_THISROW].[Scan_Sheet_ID]
)
))
1 Like
please help, i did precisely what is shown here but it doesnt work. This is my table called Listado:
| ID_Concursante | Nombres | Apellido Paterno | Apellido Materno | RUT |
|---|---|---|---|---|
| 2fa26875 | segewg | gweg | wegewg | 12123123-4 |
I have this valid if in RUT column: Not(In([_THIS], SELECT(Listado[RUT], [ID_Concursante] <> [_ThisRow].[ID_Concursante])))
But still i am able to enter the same RUT value 12123123-4
So I just used this and it worked for me:
NOT(IN([Inventory_Item_ID], SELECT(Pump AI TABLE[Inventory ID],[_THISROW].[Inventory_Item_ID] <> [Inventory ID])))
OR like below with your info/data
NOT(IN([Item in Table A], SELECT(Table B[Item to check table B],[_THISROW].[Item in Table A] <> [Item in Table B])))
Boa Noite Pessoal tenho apenas um campo no meu banco de dados no googlesheets que consta o contrato gostaria de impedir somente o lançamento duplicado referente a este campo como ficaria a formula e onde devo inclui-la
dumb mistake, i had filter out all existing rows, sos they were non existent
well I tried just below formula and it seems to be working.
NOT(IN([_THIS],thistable[thiscolumn]))
Would there be any problem if I’m using this?
Yes! there will be a problem. You can’t edit/update this this entry.
so this is not correct formula. I wrote below the correct formula:
ISBLANK(SELECT(Assets[ID],
AND(
[_RowNumber]<[_THISROW].[_RowNumber],
[ID]=[_THISROW].[ID]
)))
or
IN([_THISROW].[ID],(SELECT(Assets[ID],
AND(
[_RowNumber]<[_THISROW].[_RowNumber],
[ID]=[_THISROW].[ID]
)))=FALSE
Thanks
Saddam
2 Likes