Prevent Duplicate Records

[alhazen]

Hello,

I need your help.

I have two table which are "Device" and "Conmon". [tag_number] in Conmon table get data ref from [tag_number] in Device table.

I used this formula  to prevent duplicate records and it works properly. The Issue appears when Users edit the saved data, users cannot save edited data. 

Is there a better alternative to this issue? Thank you very much for your help.

SELECT(
device[tag_number], 
NOT(
IN(
[tag_number], 
SELECT(
conmon[tag_number],
AND(
[month]=[_THISROW].[month],
[year]=[_THISROW].[year],
TRUE
)
)
)
)
)

[TeeSee1 · Accepted solution]

You have to exclude the first row's key from the expression. See the very last exchange in

https://www.googlecloudcommunity.com/gc/Tips-Tricks/How-to-Prevent-Duplicate-Records/m-p/322114#M203...

[alhazen]

Wakatta, thank you Sir @TeeSee1 

This is the perfect formula i am using now:

SELECT(
device[tag_number], 
NOT(
IN(
[tag_number], 
SELECT(
conmon[tag_number],
AND(
[month]=[_THISROW].[month],
[year]=[_THISROW].[year],
[_RowNumber]<>[_THISROW].[_RowNumber],
TRUE
)
)
)
)
)