Changing a field with a formula inside

[Gregorrrry]

I have a form with an Email (Ref), ID No. (Key), Employee Name, Line Manager Name and Line Manager Email column.

The Email which is also a reference to an Employees table, pulls the Useremail() value. The Employee Name automatically populates using the initial value [Email].[name]. Line Manager is the same, using formula [email].[line manager]. Along with line manager email using [email].[line manager email].

I have the Employee name field editable as a dropdown so another person can fill the form out quickly if necessary.

This is where the problem occurs, I change the name however the line manager and line manager email fields don't update to the new person's name. Of course, because the formula is linked to the Useremail.

How can I keep all of these steps, and then when I change the Employee Name, the Line Manager nd Line manager email fields update accordingly.

[WillowMobileSys]

In the columns , "Employee Name", "Line Manager Name" and "Line Manager Email", change the "Reset on Edit" property to this expression:

[_THISROW_BEFORE].[Email] <> [_THISROW_AFTER].[Email]

This will re-trigger the Initial Value expressions for those columns updating them to the new entered email.

DO NOT place this expression in the Email column.

[Gregorrrry]

Thank you for this.

I may have explained slightly wrong sorry. The email is in the background and not shown as a useremail(). The Employee name is pulled from the useremail to match the reference using [email].[name] as the form is created. 

They then change the name hence needing to change the line manager name and line manager email to match the new name.

[Steve]

This:

LOOKUP(
  [_THISROW].[Employee Name],
  "Employees",
  "Employee Name",
  "Line Manager Name"
)

and:

LOOKUP(
  [_THISROW].[Employee Name],
  "Employees",
  "Employee Name",
  "Line Manager Email"
)