I want to allow duplicates across the table, but prevent them within the company of the user. I have used this expression for the work order column on the work orders table and it works:
NOT(IN([_THIS],SELECT(Work Orders[Work Order ID],INDEX(Current_User[User_Company_Link], 1)=[CompanyID])))
I use this one in the UPC column of the UPC table and it doesn’t work:
NOT(IN([_THIS],SELECT(UPC[UPC],INDEX(Current_User[User_Company_Link], 1)=[CompanyID])))
I can’t figure out why it works in one and not the other. Any help is appreciated. Thank you.
Solved! Go to Solution.
This may be another way to express it:
ISBLANK(
FILTER( "Customers",
AND(
[_THIS] = [Customer Name]
[_ROWNUMBER]<>[_THISROW].[_ROWNUMBER]
)
)
In your case:
ISBLANK(
FILTER( "UPC ",
AND(
[_THIS] = [UPC]
[_ROWNUMBER]<>[_THISROW].[_ROWNUMBER]
)
)
If that returns true ==> no duplicate
If that returns false ==> there is a duplicate
Does that help you ?
Hi @rmsmeltz
If you use security filter to avoid company’s mixing:
==> You may just want to use the expression in the last section of this post:
because [CompanyID] should have an initial value depending on the USEREMAIL().
The example is this one:
ISBLANK(
FILTER( "Customers",
[_THIS] = [Customer Name]
)
- LIST([_THISROW])
)
Can you let us know if that’s ok for you ?
For reference:
I’m a bit confused. How will this expression prevent duplicate values of the UPC column on the UPC table?
This may be another way to express it:
ISBLANK(
FILTER( "Customers",
AND(
[_THIS] = [Customer Name]
[_ROWNUMBER]<>[_THISROW].[_ROWNUMBER]
)
)
In your case:
ISBLANK(
FILTER( "UPC ",
AND(
[_THIS] = [UPC]
[_ROWNUMBER]<>[_THISROW].[_ROWNUMBER]
)
)
If that returns true ==> no duplicate
If that returns false ==> there is a duplicate
Does that help you ?
I will try this and see if it works
User | Count |
---|---|
40 | |
34 | |
29 | |
23 | |
17 |