I have a table called PRODUCTS and it has two fields:

_kp_products_id
product_name

In product_name field it has following values in separate records:

iPhone Screen Protector
Samsung Screen Protector
iPhone Charger
Selfie Stick

In a layout I want to show the product_name in a drop down field and when the user choose any of the value it should assign _kp_products_id of that product.

If I type: "Char" in the field then it should filter down the dropdown with these values:

iPhone Charger

If I type: "iPho" in the field then it should filter down the dropdown with these values:

iPhone Screen Protector
iPhone Charger

How can I accomplish this?

 

Edited April 8, 20178 yr by nexgen

The simple way is to let the user choose the product's ID from a value list using values from the Products::Product_ID field, also displaying values from the Products::Product_Name field. If you like, you can set it to 'Show values only from second field'.

This is assuming you are selecting a product to be used in a table other than Products. In this table you do not need - and should not have - a field for the product name. In order to display the product's name, place the related Products::Product_Name field on the layout, and make it non-enterable to prevent accidental changes. Or format the local product ID as pop-up menu.

 

1 hour ago, nexgen said:

I have a table called PRODUCTS and it has two fields:

 

 

 

_kf_products_id

That's rather confusing: "kf" stands for "foreign key". In the products table, the Product_ID field is the primary key - so if you're using this naming convention, you should name the field "_kp_products_id".

How can I make the dropdown field narrow down the displayed list as I type? The solution you suggested is just a simple drop down field with a value list.

Regarding _kf_products_id I mistakenly typed that way. I have corrected it.

You might find reading the help on value list helpful. 

http://fmhelp.filemaker.com/fmphelp_14/en/html/edit_layout.10.49.html

Also, do a search for Conditional Value List or Dynamic Value Lists.

Couldn't find much help regarding what I want to accomplish in that page.

12 minutes ago, nexgen said:

How can I make the dropdown field narrow down the displayed list as I type?

I am afraid that's not possible using the method I have suggested. If you really need this, you will have to select the product's name into a local field (as you originally intended), then use a relationship based on matching name to lookup the ID. This has several disadvantages, e.g. maintaining a redundant name field in the other table (which could potentially become conflicting with the real name), as well as requiring another relationship. You might want to look into alternative selection methods that do not use value lists (e.g. finding the actual product record).

 

53 minutes ago, comment said:

You might want to look into alternative selection methods that do not use value lists (e.g. finding the actual product record).

Any example for this?

Btw, the autocomplete method will only work for filtering the values which matches from the beginning. I want to match the typed string match anywhere in the value.

On 4/8/2017 at 8:18 PM, nexgen said:

I want to match the typed string match anywhere in the value.

Then you cannot use a drop-down, no matter how you define your value list. I would suggest you open a new window as a list view of the Products table and use a global field with an OnObjectModify trigger to perform a find on the entered phrase (prefixed and suffixed by *).