Many-to-Many adding records

[javabandit]

Hi everyone.

One of the solutions I'm working on requires a many-to-many relationship. This is my first attempt at setting this up. We have tools and part numbers. The basic relationship can be phrased as such:

Each tool may produce many part numbers.

Each part number may require many tools to be produced.

I've been working for several days learning the principles of a many-to-many join, and I now have a structure that works. The next big challenge for me is to make an interface that is easy to use. I've attached a clone file of the work I've done so far. As far as I can tell, this is working just fine. The reason I'm posting is that I feel as though I had to some sneaky trickery in order to get it to work, and I'm pretty sure there's a better way.

(There's always a better way, eh?)

Anyway, I would very much appreciate if some of you curious developers would take a look at my method and let me know what you think.

Some info that would be helpful to know:

- We have thousands of tools. Here I'm just showing a handful.

- We have tens-of-thousands of part numbers.

- Adding tools and part numbers is a frequent occurrance.

A a major function of this database is to add new tools with their associated part numbers. Typically a user will think "I need to add this type of tool... and these are the part numbers that go with it."

So, if you would be so kind, please open my file, select a tool (or add a new tool) and add a part number to it with the portal.

My trickery comes into play because I don't want the user to decide if they are "joining" an existing part number to the tool, or if they are "adding" a new part number to the part numbers table, that would then be joined.

This is not a 'polished' interface, I'm just working through the logistics of adding join records. I look forward to any suggestions you may have to offer.

Thank you for your time.

P.S.

I -very- much appreciate the folks on this site, and I continue to recommend it to others.

many_to_many_Clone.zip

[bruceR]

A related comment: I suggest a different symbol for the portal row delete. You are using ">" which most often means GO to this item or do something with this item. I suggest you change to "X" or "DEL" or something more obviously linked to deletion.

[javabandit]

Thanks Bruce.

Yeah, this is not really the look of the final interface. I'll probably use a red X or "del", as I've used in other, similar, databases. The > was left over from when I was experimenting with a 2 portal setup where a user would "move" the records between the joined and UN-joined portals.

What I'm most interested in is any comments related to my weird hidden-field, button script, and script trigger. Is this type of thing normal for adding records in a many-to-many, or am I just off the deep end? It seems to me like a bit of a kludge. Is there a more clean way to do it?

Thanks for your time.