SharePoint List Lookup Column of Active Fields – Solving null when field is changed to Not Active.

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:

  1. Add the Customer/Prospect Name column to the Contact List. This is now the column we use in our views.
  2. 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.
  3. Add the Microsoft Flow below that maintains the integrity of the Customer Prospect Name on the Contact List.

We also 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 and added “Please refresh page to view changes.”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s