Lookup Dropdown with combined columns

  • 11 September 2020
  • 1 reply
  • 2 views

Badge +2

Hi,

I'd like to get advice on the best way to achieve a very common thing.

Let's assume a List "Customers" that has two fields such as:

CustNbr CustName
123 John Doe, Inc.
456 Contoso, Inc.

 

In another List "Orders" I refer each order to a customer like:

OrderNbr CustNbr Whatever
666 123

Some stuff

 

In the Order Form, if I add a Lookup Dropdown for CustNbr, I end up obviously having only the numbers (123,456) in the dropdown selector, which is pretty useless.

 

What is the best practice in a Form to add a Lookup Dropdown field for CustNbr which actually shows combined values to select from, such as "123 - John Doe, Inc.","456 - Contoso, Inc." but only populates the right value in CustNbr when selected (i.e. select in dropdown "123 - John Does, Inc." and that populates CustNbr with 123.

 

The solution I can think of is to add a calculated column in Customers like "=CONCATENATE(CustNbr, " - ", CustName) and then lookup on this one and do some fn-substring to retrieve only the CustNbr part but that's terribly ugly. Any better idea ?

 

Looks like a very basic thing which should be used all the time but I can't figure out.

Thanks

Stephane


1 reply

Badge +8

Hi @Clipper,

something I think could work:

  • Create the calculated value field in Customers to combine fields
  • In Orders Form
    • Add a text-column to the list (txt_CustomerID) 
    • Add a lookup Control (not connected to any column), point it to your new column
    • Add a calculated value to your form with formula
      parseLookup({your_lookup_control}, false) 
      this gives you the ID of the selected element. Connect this to txt_CustomerID.
  • Using a workflow you can now translate your text-column to the lookup-column. It should be as easy as setting the lookup = text, as your text contains the ID of the element. Let the workflow run on item creation and everytime txt_CustomerID is changed to keep your Lookup-Column up to date

Reply