Below is our Customer/Prospect Contact List entry form. The user picks the active Customer or Prospect of the Contact person from the drop-down list. The drop-down is a Lookup Column from our Customer/Prospect List, which also includes no longer active accounts.
Naturally we don’t want these non-active accounts on our drop list, so we use a Calculated Column called “Active Customers Prospects” =IF(OR([Customer Status]=”Customer-Active”,[Customer Status]=”Prospect-Active”),[Customer Name],””). The “Active Customer Prospect Name” Lookup field is based on this calculated column.
A complete contact entry looks as follows:
An issue comes up when we make a Customer or Prospect not active. The name does not appear on the drop-down list as desired, but their name also does not appear on the Contact list. See below:
Here is the approach we use to keep the Customer/Prospect Name on the Contact List:
- Add the Customer/Prospect Name column to the Contact List. This is now the column we use in our views.
- Remove the Customer Prospect Lookup column from all Contact List views. This keeps the drop list on our entry page but keeps it off our views, so the users do not get confused.
- Add the Microsoft Flow below that maintains the integrity of the Customer Prospect Name on the Contact List.
One more note. We turn off on the under Contact List Settings>Advance Settings>Quick Property Editing since using this view requires the Customer/Prospect Lookup column to be on the Views.