Getting the layouts to work with join tables
I've been having some difficulties getting Filemaker Pro to work the way I need.
[I have software development background - I've worked as a .NET C# and Java programmer for some time, I have extensive experience with the MS SQL Server. So I know how relational databases work.]
I'm battling with the FileMaker user interface - the layouts.
What I need to do is pretty straightforward. I have a table called Product, which stores all the product info, and a table called Order, which will store orders. Since one order can contain many products, and one product can be on many orders, it's a many to many relationship. SO I added a join table called OrderItem. Its fields are pretty self explanatory:
I've attached a diagram.
I've set up the relationships, everything should be in order on the data model side. Now I need to create a layout that will allow the user to create an order and put order items on it. I've made a layout based on the Order table, and put a portal on it, now I can create new OrderItem entries on the layout. But that's not quite what I want the user to see.
I want the user to be able to pick a product by ProductName, from a context menu/popup window or something like that. There's actually a lot of products, so navigating to a separate form would be acceptable. The ProductID of the selected product should be entered into the OrderItem's ProductID attribute, but the corresponding field should display the ProductName. I'd also like the Price field to be pre-filled when the user selects a product, but the user must be able to edit the field without rewriting the original value in the Product table. In other words, the user needs to be able to put in special price just for the specific order.
I realize this is pretty basic stuff, but I have almost zero experience with FileMaker, and I'm kind of stuck. If I could get help figuring this one out, it would be a huge boost, because most of the other situations in my database are exactly or pretty much the same.
Thanks in advance for any help.