How to edit uniq and required column value?

sayan1 · 03-08-2020 08:56 AM

Hi everyone!

I need your help

I have table with two uniq and required column.
Firsrt is a key value, there is no problem working fine.

And second one is prevent duplicate with this query:

NOT(COUNT(Select(Animals[VID], AND( [Farmer] = USEREMAIL(), [_THISROW].[VID] = [VID])))>0)

Everything is ok, but, when I edit the row, this query say that this column value is duplicated and I can’t save my changes.

How can I do that ?

Suvrutt_Gurjar

Hi @sayan,

If I have understood your requirement., I believe you need to subtract the current row from the expression. I believe you may need an expression something like below

NOT(COUNT(Select(Animals[VID], AND( [Farmer] = USEREMAIL(), [_THISROW].[VID] = [VID], NOT(IN([Key Column], LIST([_THISROW].[Key Column]))) )))>0)

Please take a look at section " Preventing Duplicate Field Values" in the following article
However , please note that this duplicate prevention logic could fail in multiuser environment.

Steve

Try:

ISBLANK(
  SELECT(
    Animals[VID],
    (USEREMAIL() = [Farmer])
  )
  - LIST([VID])
)

Very true.

@sayan, just to be clear, a key value must be unique within the entire column of the table, not just for a particular Farmer column value. If VID is the table’s key column, no two farmers can have the same VID value. Your expression may very well ensure a VID value is unique for a Farmer, but it doesn’t check for duplication of another farmer’s VID. The error appears to be telling you it duplicates another Farmer’s VID.

Suvrutt_Gurjar

Very compact expression as usual @Steve.

sayan1

Thank you!

VID is a visual ID of cow.

Farmers cow VID could be same like me, but uniq just for him.

Your query check duplicate for all VID, mine check by user, but when I edit the row, I can’t save update, becase query condition is not valid )))

It’s so fanny ))

So, I don’t know how to chage update when form value is not valid.