updating a column that has valid if expression

[AGairAdmin]

2 tables with "employee email"  on table "users" and "company employees" when an entry is being added to either table, it looks to the users table to see if the email exist 

on both of these tables, on the column company email   
valid if - 

NOT(IN([_THIS], SELECT(Users table[Employee Email], [User Key] <> [_ThisRow].[Employee Email])))

This works great , until you need to edit an existing item, then when i save it gives the error for "employee email" that it already exist and you can't save or update it. 

any help would be great 

[Joseph_Seddik]

Email is a naturally-unique attribute. Just make your emails columns as Key columns in both sheets, hence you wouldn't need to use formulas or anything else. 

[AGairAdmin]

its slightly more complicated 

both tables have at least the following 

User table [User key] / company employee table [employee key] = uniqueid()  = set as KEY
Email / Email = useremail () 
Employee Email / Employee Email = the one we want to check


The process "bot" process 

If a new user is added to the "user table"  = "ADD row with the chosen selected columns to "company employees table"

and the reverse.  

If a new employee is added to the "company employee table" = ADD row with the chosen selected columns to "User Table" 

--------------  
so we are wanted during the form complete phase, to say if the "employee email" already exist and not allow save ,  we can make that work for new records..... BUT it stops editing or updating existing records. 

NOT(IN([_THIS], SELECT(Users table[Employee Email], [User Key] <> [_ThisRow].[Employee Email])))

-----------------
we tried this expression as well but didn't work to stop the entry or even throw an error 

NOT(
IN(
[_THIS],
SELECT(
Company Employees table[employee Email],
AND(
([Employee key] = TODAY()),
([_ROWNUMBER] <> [_THISROW].[_ROWNUMBER])
)
)
)
)

---------------------------------------

[AGairAdmin]

the goal is .... to let a company add and edit their own employees , and certain data will be added or updated to the "user table",   this will allow the company to control basic info on their employe, but us the global admin can manage the "users table" on our own and when they add one we can be notified and then set some values that will allow that user/employee to view or interact with certain tables and views.   

[Marc_Dillon]

Remove .[Employee Email]

[AGairAdmin]

@Marc_Dillon   halfway there !!!    

it worked on the
User table to User table 

now just need to get it working on the 
Company Employee table to User table 

both should look to the user table for existing value.  im sure i have screen blindness after 4 hours of thinking on this issue  

[Marc_Dillon]

Is there a reference between the 2 tables?

[AGairAdmin]

@Marc_Dillon 
both tables have at least the following 

User table [User key] / company employee table [employee key] = uniqueid()  = set as KEY

Email / Email = useremail () 

Employee Email / Employee Email = the one we want to check

the edit you offered worked for the table "users table" , when adding a record it will say if there is a employee email already there, and it allowed me to edit an existing record that already had employee email filled in , (where as before it didn't allow the edit to happen and threw the error "not valid" for employee email )

[Marc_Dillon]

So...is there a reference between the 2 tables?

[AGairAdmin]

@Marc_Dillon   im too new at this to know what is the right answer, to me a reference is just a table with 2 column names that match. 

your OG edit of my expression worked perfect btw, just doesn't work in another table

maybe i can do a fresh rephrase of the issue and desired solution 

when adding a record to the table "company employee table" i would like column "employee email" to check and see if there is an existing record in table "users table" in the column "employee email" , if true , then don't allow employee email entry, 

and of course we would like to edit an entry in table "company employees" and not be stopped by "employee email already exist" issue. 

please bare with me, im trying to be short and sweet but i don't know enough to know which info i should be giving when asking for help

[Marc_Dillon]

Both of your tables have a key value. Does one of your tables include the other table's key value?

https://help.appsheet.com/en/articles/961426-references-between-tables

[Marc_Dillon · Accepted solution]

I'll explain more, now that I have some more time.

The basics of duplicate prevention can be described as:

"does this value NOT already exist in this list"

This works fine for new records, because the currently-being-added-record is not yet in the "this list". But if we want to be able to edit the record and still pass validation, we have to update it to:

"does this value NOT already exist in (this list which does not contain the current record)"

In order to do that, we need to be able to identify the current record within that list, in order to ignore/remove it.

In the case of looking at the same Table, that's easy, just [key-value] <> [_THISROW].

But you're asking about validating from a different Table, which makes it more complicated. You need to know which record in the other Table "matches" the current record. That's why I asked if there was a reference between the records.

As an example, perhaps the company_employee Table has a column [user], which holds the key value of a record in the user Table. In which case, you would use the valid_if expression of:

NOT( IN( 
  [email] ,
  SELECT(
    user[email] ,
    [key-column] <> [_THISROW].[user]
  )
) )

[AGairAdmin]

@Marc_Dillon  that was very very well written and i now understand how the expression functions. 
i think i need t make a change to the structure of the tables and functions, or merge both of these tables,

i think changing the unique id column for each table to match "user key" and "user key" NOT "user key" and Employee key"

i will have to update the functions of each table so the bot can push the data back and forth then i will move to implement the solution you have provided.


the only reason they are split is security concerns, idea being the user app will have "read only" to the user table but "add, edit, delete" to "company employee table" and the look up function keeps someone from changing an email name to get that persons access to the app. 

if you have any suggestions on the structure i would be greatly appreciated 



3 apps being made from data set 

Global admin:
-Full access to all tables 

Office Management:
Read only - Users table
Read only - Company Table
FULL - Company Employees

Field: 
Read only - Users table
Read only - Company Table
Read only - Company Employees

[Joseph_Seddik]

Remove the “Employ Key” columns and set “Employee Email” in both tables as keys, and you wouldn’t need any formulas to check for duplicates.