How to make a two way connection between rows in the same table that link through a second table

[johndoe101]

Ok here is the problem. I want to show that persons in a table are associated with each other. I would like to link PERSON A to PERSON B from INDIVIDUALS TABLE with the details of the association going to ASSOCIATES TABLE.

That way, regardless of which PERSON I view in Appsheet, I can see the association between both PERSONS.

Thank you!

[SkrOYC]

This is many to many. Since it's a little bit cumbersome to explain, refer to these videos by @MultiTech 

Many to Many References With SELECT() in AppSheet || The Brute Force Method - YouTube
(Join Table) Many-to-Many References DEEP DIVE - YouTube

[MultiTech]

Cheers @SkrOYC !

[johndoe101]

I tried for 2 months to make this work. As you can see, when I associate Jane Doe to John Doe, she shows up under his profile in the Association: inline view. But when I go to her profile, that connection isn't reflected.

Any other material on the web that could guide me in? Happy Thanksgiving by the way! Thank you!  

[dbaum]

If your implementation is something like an Associations table with the key column as Person A ID and a Person B ID column, then you may need to do one of the following:

• In the Individuals table, add to the [Related Person A IDs] that are seemingly already showing the additional Person A ID values where the individual's ID appears in the Person B ID column. Potentially, that's an expression like:

  • REF_ROWS(...) + SELECT(Associations[Person A ID], [Person B ID] = [_THISROW].[Individual ID])

• In the Associations table, use an automation to maintain a row for the inverse of every row that is added, updated, or deleted. For example:

  • Person A ID	Person B ID
    Jane Doe	John Doe
    John Doe	Jane Doe

[johndoe101]

Above is the Individuals Table

Above is the Associates Table


The first column of Individuals table generates a unique ID when an individual is entered.

The Associates table generates a unique ID in the first column when I create an association between two individuals which are being Ref from the Individuals table. The Association Source is the individual I'm creating the association under, while the associate is the individual I'm connecting too.

Above are how the virtual column look in the Individuals table column.

Above are the scripts running in virtual columns.

Above are how the virtual column look in the Associates table column. The two Ref both pull from Individuals table.

Are suggesting that I make the Association Source, and Associate columns Unique IDs?

Thank you for the help by the way. I feel like I'm close to figuring this out. It's just difficult to grasp some of these concepts with no code. I'll try what you suggested. Thank you for the patience and willingness to help. 

[dbaum · Accepted solution]

---

@johndoe101 wrote:

Are suggesting that I make the Association Source, and Associate columns Unique IDs?

---

No, I'm not suggesting you generate new ID values in those columns.

I am trying to distill a few key concepts for you to build on. That said, the thorough explanations are in @MultiTech's videos that @SkrOYC pointed you to.

Regardless, it sounds like for each row in the Individuals table, between the Association: and Associate: columns, you already have a comprehensive list of keys of relevant rows from the Associations table. Maybe all you need to do now is dereference each of those key lists to the IDs of the associated individuals and then combine those dereferenced lists. For example, consider creating another virtual column with an App formula along the lines of the following:

[Association:][Associate:] + [Associate:][Association Source:]

[johndoe101]

That worked perfectly! I have been trying to find a solution for this problem none stop. YOU ARE AWESOME! I can feel the stress leaving my body! So many thanks!