How can I display a dropdown list for a field that's defined as an expression

Hi ​@Jheng,

It should be possible to achieve this by using variants.

1. Setup prefilters

   • Create a prefilter 'company_name' on the 'company' table, where you filter on companies starting with "NL".

     • Think about what display type you want for this prefilter when opening the 'Companies' screen in places other than the look-up you're referring to. If they are not relevant elsewhere, you can set them to 'Off hidden' at the subject level.

   • Create a prefilter 'liners' on the 'company' table, where you filter on companies starting with "L".

     • Think about what display type you want for this prefilter when opening the 'Companies' screen in places other than the look-up you're referring to. If they are not relevant elsewhere, you can set them to 'Off hidden' at the subject level.

2. Setup variants

   • Create a variant 'company_name' on the 'company' table.

     • Go to the Prefilters tab in that variant and change the look-up state of the prefilter 'company_name' to 'On', 'On locked', or 'On hidden', depending on what you prefer.

   • Create a variant 'liners' on the 'company' table.

     • Go to the Prefilters tab in that variant and change the look-up state of the prefilter 'liners' to 'On', 'On locked', or 'On hidden', depending on what you want.

3. Ensure the look-up's use the new variants

   • Under User interface > Subjects, go to the 'Freedays' table.

     • Select the 'Company' column, and click the "Look-up" tab. Here, you can set a "Look-up table variant". Choose the variant 'company_name'. This ensures that when you open the look-up for this column, you'll enter the variant where the prefilter filtering on companies starting with NL is enabled by default.

     • Select the 'Liners' column, and click the "Look-up" tab. Here, you can set a "Look-up table variant". Choose the variant 'liners'. This ensures that when you open the look-up for this column, you'll enter the variant where the prefilter filtering on companies starting with L is enabled by default

Since this only involves model changes, you don't need to do a creation afterwards.

• If your end product is started against SF, a model refresh is sufficient.

• If your end product is started against IAM, you need to synchronize and then do the model refresh.

Let me know if this helps!

Hi Jeroen,

Thank you for the help, I already created a prefilter and variants under Company table.

However I think there's some missing steps? I'm stuck on item 3, the look-up seems to be empty.

Prior to the setup that you provided, I created a reference to freeday table,

which makes the company_id as foreign key in freeday table

Is there anything i'm missing? 

Thank you in advance

When you created the reference, did you select “Show look-up”, like here? If not, you can still enable this by going to User interface > Subjects, selecting the 'Freedays' table, then navigating to the Links tab followed by the Look-ups tab. There, you can still check 'Show look-up' for the relevant reference.

Hi Jeroen,

I clicked on the show lookup 

But as I've mention earlier the reference I'm using is company_id only from the company table is that correct?

Hi! It's a good setup when you use company_id as the reference column. Since you use the company name as the lookup display column, the name will be correctly shown in the lookup, even though the ID is used as the reference column.

You do need to make the company_id visible (it's currently set to hidden) in order for this to work properly.

This is the Freedays table under User interface > Subjects

lookup is really empty for both company_name and liners

but for company_id wherein I have reference from company table, it shows the relevant lookup

Hi Jeroen,

Is there anything I missed out to set? that is why it's not working as expected on my side?

Thank you

It is logical the company_name and liners do not have a look-up display column as they do not participate in any reference. You can remove these fields unless they have a clear purpose. 

Company_id is sufficient. By showing this in your application, the company_name_1 value is shown instead, allowing you to know which company it is.

Generally speaking, only the base table will need a [table]_name column, which then can be set as Look-up display column. Linked tables will only need [table]_id with a forein key to this base table. The [table]_id column will then display the [table]_name column

Hope this helps!

Hi Mark,

I understand that using company_id would definitely show lookup to the company_name_1, but this is not what I require at the moment.

As my initial inquiry how can I display a separate drop-down list of the Company and Liners.

Company and Liners. Both values are derived from the same table called Company. To differentiate them, the criteria is as below:

• company_name includes companies starting with "NL"
• liners includes companies starting with "L"

We have this design in order for users not to key-in the values but instead able to choose from the dropdown list 

Please advice if there's any way to achieve this.

Thank you

​@Jheng You need to make a reference to the column you wish to see the result. I think the current naming might be a bit confusing, but I guess this is your attempt (this is an example, do what best fits your own model):

I extended your column names to hopefully make it a bit more clear how lookups work.

“company_name_company_id” shows companies starting with “NL”
“liners_company_id” shows companies only starting with “L”

You have created prefilters for those on the Company table and created for each option a variant with the specific prefilter ON (locked or hidden).

Make for both columns on Freedays a reference where from the Company table you use column “company_id”. So this would be:
“company_id” - “company_name_company_id” (make sure this one had the company_name lookup table variant)
“company_id” - “liners_company_id” (make sure this one had the liners lookup table variant)

Now you have on both column a lookup with limited options because of the prefilter that is configured in the variant of the table.

Thank you all I will try the above suggestions and let you know 

Hallo,

Thank you all!  I manage to display to dropdown list base on all your suggestions above.

Feel free to select the answer that helped the most as "Best answer"