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