Many-to-many relationships with large forms
I'm trying to find the best approach to creating many-to-many relationships between a series of forms (tables) that have hundreds of entries in them but generally only a few are selected. For example,
- a farm will sell their produce to one or more restaurants,
- a farm will sell their produce to one or more distributors,
- a farm may be a member of a market group,
- a distributor will sell to one or more restaurants,
- a restaurant buys from zero or more farms,
- a restaurant buys from zero or more market groups,
It goes on but you get the idea. The problem I find is that by using field type input data/multi-select, there is a lot of data to scroll through to find the records to select. It would be nice to start typing the name and have the cursor jump forward as you type.The other problem is, if you accidentally let go of the control button while selecting, you have lost all your selections. If a user comes back to this record later and accidentally clicks in this field, all the selections are lost.
Can these selections be stored in some sort of link-table? I'd also like to be able to jump to the other form for editing of record or to be able to add an additional record.
I see that bi-directional relationship is now available but that seems to just pull data from one form into another.
Any help in this area would be appreciated
John
/*.