Relating multiple accounts within the same table

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • In Progress
I created a table for key accounts in our company. I want users to have the ability to choose one or more accounts that are related to each other. The relationship of one account to another could be location, name, or other unique characteristics. 

I would like the user to be able to show these relationships on a form while having these links be clickable.

I have already related the table to itself. Any ideas of how to achieve this?

Thank you!
Photo of Meagan McLeod

Meagan McLeod

  • 130 Points 100 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
At first glance this looks like you will need a 'joined table' to accomplish this.

Are you trying to say that Accounts are related to themselves?  i.e. Account A is also connected to Account B?

How does the Name, Location or 'other unique' characteristics work into this?

Connecting the table to itself, might work, but i'm thinking there will need to be a different set up to get a pure connection.  Especially if their are multiple.

Can you provide more use case details and relationships to other things in your process?
Photo of Meagan McLeod

Meagan McLeod

  • 130 Points 100 badge 2x thumb
I'm trying to say that Account B, C, and D are related to Account A. And vice versa.

The relationship for each account would be determined by the account owner so not all accounts are related based off location. I didn't know if the relationship had to be based off one aspect or if it could be manually determined by the account owner/user.

Right now, I have the table related to itself and the [Related Account ID] on the form. The user can specify one account that is related but not multiple. Also, the relationship only goes one way. For example, Account A could show its related to Account B but not vice versa.
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
You are correct in your discovery and assumption that your current 'circular' relationship will not work for this.

You will want to create a 'joined' table.  

Call it something like "Joined Accounts"

Here is the tricky part:

You will want to make two relationships between Accounts and Joined Accounts.
Where one Account can have many Joined Accounts (twice)

Thus one of the connections will be Primary, and one will be secondary.

And you will have a record for any/all associated accounts.

Side Note:  This isn't a 'chart of accounts' set up you are looking for correct?  Because that is a different set up.