Connect records in the same table

Hey, i’m wondering if someone can help work out how to do the following. I’m all out of ideas. This is the last sticking point before we can release our client portal app to our clients.

hope this makes sense, i’m not too good at explaining these sort of things.

  • I have a table of users of which most are clients.
  • clients may be connected to each other eg. a married couple, joint mortgage
  • The clients will log in to our client portal accessing their own data - not their connected persons data
  • we want to allow connected clients to be able to link themselves and the person(s) they are connected to, to child tables. Addresses, debts, children, mortgages etc… this will avoid each connected client adding the same data for shared items such as parent to the same child.
  • Each child table has a connection field to the Users table with a many connection - currently this has no filter so a client could see a complete list of all of our clients.

Ideas i have mulled over so far;

  • a join table - but i can’t figure out how to call the connection from the child pages
  • another connection field in the user table - to the user table to connect the second / third person etc. but again i can’t envisage how this would work practically for the clients adding / editing their data.

Any ideas very much appreciated.