Set up a relationship between your layout's table occurrence and an occurrence of products that matches by product name, not ID.
LayoutTO::ProductName = ProductsByName::product_name
Define the productID field in your layout's table to either look up or auto-enter the product ID from ProductsByName.
You can then format LayoutTO::ProductName with an auto-complete value list of just product names.
This name based relationship should should only be used to get the Product ID. Use ID based relationships in the rest of your solution as the way to link records by product.
The auto-complete Value list approach that I've described can, BTW, be used in conjunction with a script that can handle adding new values and having more than one record with the same name but different IDs. These don't seem like something you need for this problem, but are additional options that can be implemented if you need them.
You can also use a keystroke by keystroke filtered selection portal for this--which can open the door to options not possible with a value list.
You can find working examples of both methods as well as detailed documentation on how to set them up here:
Thank you for the excellent demonstration file.
The autocomplete will only complete the field if it matches from the beginning. What I'm looking for is something which can match even if the text contains in the middle.
I looked at the "Auto filtering search portals" and it looks like what I need but how can I make the interface look like a drop down?
You can't make it look exactly like a drop down as you are using different controls. You can get close by hiding borders and portal row dividing lines. And as shown later in the file, you can put these portals inside a popover button to save space on your layout.
I later noticed the popovers & sliders example. It's almost exactly what I want.
How can I setup to work within a portal. For example, I have portal and in each row the user select products. How can I implement that using popover? I think the popover containing portal won't work within a portal.
You can put a popover in a portal row, but you cannot then put a portal inside that popover.
But you can put the popover button outside of the portal, but hidden next to/behind a corner of the portal. A regular button in the portal row can use go to object to open the popover (set an object name on the popover panel, not the button) and set a match field in the layout's current record to the needed value from the portal row (If necessary) so that your portal displays the correct set of related records. You can use Hide Object When with True as it's only term in the expression to make the popover button always invisible. You can also set a global variable to the primary key of the portal row so that clicking a value in the portal row runs a script that uses that value to update the correct record in the original portal.