Set value of field in form based on previous (with ability to edit/add new)

[jgiangra]

I have a form where the user is either selecting an existing name or adding a new name. If existing, I'm pulling in the email address for that name, if new, they can enter a new corresponding email address.

I'm using this formula in as suggested values:  SELECT(Partners[Attorney Email Address], ([Attorney Name] = [_THISROW].[Attorney Name])). 

It works but its not the best user experience; once an existing name is selected, the correct single matching email is appearing but it has to be selected from a drop down, rather than the field populating automatically. 

Is there a way to achieve this where if existing name is selected, the field auto populates but user can still add new if new name is entered? I tried app formula but error is expected value is email and this renders list (but want user to be able to edit any way so doesn't seem like the right route). I tried a lookup formula but get the same result as select. 

Example

I selected the name then have to click the drop down on the email field and select the email (which is the correct email being pulled in by the formula), rather than the email filed populating automatically. 

[Marc_Dillon]

Use Initial Value.

https://help.appsheet.com/en/articles/961507-app-formulas-and-initial-values

[jgiangra]

Hi - I forgot to mention that I tired initial value as well and received the same error as app formula "The expression is valid but its result type 'List' is not one of the expected types: Email."

Maybe there is an expression other than select or lookup that will work in initial value?  

[Marc_Dillon · Accepted solution]

"The expression is valid but its result type 'List' is not one of the expected types: Email."

---

SELECT() would just need wrapped in ANY()...which is exactly what LOOKUP() is.

[jgiangra]

Moving it to initial value and wrapping in any() achieved the result I was looking for! Thank you