I'm pulling my hair, people. It's like this:

I have a table with line items, and some of them have to be linked with none, or one, or many of their fellow line items (basically, they are bank transactions, and they either stand on their own, or they have to be linked with one or many other transactions, because they relate to the same business move).

Until now, I've had a text field. If a transaction stands on its own, the field is blank. If it's to be linked to others, then I go into that text field, and I manually (yikes!) enter the primary IDs of its related transactions. So, it's time to smarten this beast up.

Any idea as to how? I'm thinking, setup a new table as a line_items table for the multiple joins. But I haven't quite figured it out yet, and I'm not a rookie, either.

Say that transaction #3 must be linked to #6 and #7. I need to make it so that if I am looking at #3, I can see #6 & #7 as linked transactions in its portal; but also, when I'm looking at #6, I can see #3 and #7 in its portal; and so on.

You follow? Anyone thinks they can offer a little help?

Much appreciated people.

Have you looked at this, in that it explores parent/child hierarchies? Link

I am not sure if what you describe is a structural issue or a UI issue.

Assuming it's structural, you should have a table of Moves, where SOME transactions would have a parent record. Then it's just a matter of associating a transaction with a move record: either by entering the MoveID, or by entering the entire transaction through a portal on the Moves layout.

If it's just a matter of how/what/where to enter data to make some transactions related to each other, I would again enter some common value into a Move field.

Thanks for the posts, guys.

@comment: It's most definitely structural: I need to be able to click on the shown ID of the linked transaction and load it on the screen for quick review (or even better, pop up a new window on the screen with the linked details). I'll have a go tomorrow, as it's after midnight here in Athens, Greece. I need to get a life, too.

I didn't see the Move entity in your first post. I immediately jumped to parent/child transactions in a chain or hierarchy. Sounds like creating transactions from a Move, or assigning a MoveID to a transaction will do it (as comment suggested).

I need to be able to click on the shown ID of the linked transaction and load it on the screen for quick review

There's nothing structural in that by itself. You can get this functionality once the transactions are linked, and for this purpose it doesn't matter if they are linked by a self-join or through a parent record. We are missing a large portion of the picture here, esp. how are the transactions entered.