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 ?
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.
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.
Very compact expression as usual @Steve.
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.
User | Count |
---|---|
33 | |
29 | |
29 | |
20 | |
18 |