Search drop-down with code and description but store only code

[StephenSaid]

Hi

I have a field that stores an item code. To create the drop down, in the Valid If I have

Select(StockList[Item_Code], true)

Although this works fine, if the user tries to search for the item with its description, it does not work.

How can I fix this, please?

[AleksiAlkio]

What column type are you using with the "Item_Code"?

[StephenSaid]

Both fields are of text type.

[StephenSaid]

@AleksiAlkio 

When the user clicks on the Item Code field, he can search for the item.

I need the user to be able to search for the item using both code and description.

Is this possible?

[AleksiAlkio · Accepted solution]

You would need to use either Enum (base type of Ref) or Ref column type. Additional, you need to add a column where you concatenate these those columns together something like [Item_Code]&" - "&[Description] in your source table and set this new column as a label. Then it's possible.

[StephenSaid]

@AleksiAlkio 

Thanks. I'll have a look at this solution and revert, hopefully this evening.

[AleksiAlkio]

You're welcome!

[StephenSaid]

@AleksiAlkio 

This is what I've got.

This is the StockList table. I've added a virtual column to include both code and name and marked it as Label.

In the Transfers table, I updated as follows. Unfortunately, I had to Allow other values which is not what I want. But this might be a small price to pay!

When I click on the field, I get a blank list!

If I select Auto-complete other values, I get four items in the list. These items are items which are on other transfers in the Transfers table. This is not the behaviour I need. Maybe I'm doing something wrong need to do something else.

I really appreciate your input.

[AleksiAlkio]

You need to add a formula to Suggested values to read the list of key values.

[StephenSaid]

@AleksiAlkio 

Love it. Thanks

[AleksiAlkio]

You're welcome!