Restrict duplicates based off company of user

[rmsmeltz]

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.

[Aurelien]

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:

help.appsheet.com

List Expressions

List expressions use and/or produce lists of values.

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:

help.appsheet.com

Security Filters: The Essentials

You can use Security Filters to control which records a user can see and update.

[rmsmeltz]

I’m a bit confused. How will this expression prevent duplicate values of the UPC column on the UPC table?

[Aurelien · Accepted 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 ?

[rmsmeltz]

I will try this and see if it works