Creating a Many-to-Many Self-Relationship

It's a bit of a niche scenario...

Aug 07, 2023

I'm in the process of building a custom app for professional tabletop roleplaying gamemasters to manage multiple campaigns. It's been a piece of work but I've been learning a lot as I build it out. Here's a peek at the data model:

One of the requirements for the app is that the gamemaster ("user" from here on out, ‘cause it's faster to type) wants to be able to see how multiple NPCs (non-player characters) relate to each other.

Unfortunately, that becomes an issue.

In Salesforce, we can relate one object to another object in a many-to-many relationship. And we can have a self-relationship pretty easily too. But a many-to-many self-relationship gets tough:

Here's how I solved it anyway.

First, I created the requisite junction object. NPC_1_Name Lookup field. NPC_2_Name Lookup field.

I decided not to use a master-detail relationship. If for some reason the end user deleted an NPC, I didn't want all history of that NPCs relationships to disappear. Also, since you can only have one master-detail lookup to a single object, there wouldn't be much point, since the other half of the relationship would be a regular lookup anyway.

I added a picklist for relationship type, and used a global picklist I set up. My end users will be Salesforce neophytes, so the fewer places they have to go digging around to make the changes they want, the better. All the picklists that they could need to edit are in one spot, and the help field for each picklist has directions for how to make changes to it.

So far so good. Now for the funky bit!

As-is, I have two "Related NPCs" lists on the NPC object. If I add just one of them, only half the relationships will show up, the ones where Alice, say, is NPC 1.

I had to poke around a little and get some advice (shoutout to Solo Admin on the SFXD Discord!) before I decided on a solution:

Screen Flow Data Table!

I knew I'd want a flow embedded on the NPC record page to easily create a new relationship, so I decided to combine that "new record" flow with the display flow.

The flow is a pretty straightforward one:

For what it’s worth, yes, it does now bother me that I put descriptions on 2/3 of the elements.

It first pulls all NPC Relationship records where the recordId (the NPC record page it's showing on) is either NPC_1 or NPC_2. Then comes the screen element. I put the fields needed to make a new record right above the datatable:

The data table gave me some trouble, though.

Turns out, if you have one of the columns point to a Lookup field, as I was doing, it ends up just displaying the record ID string. Not quite the slick experience I was hoping for!

I ended up working around it by creating a formula text field on the NPC_Relationship object that just copies the name of the NPC record selected in the lookup field, then pointing the data table column to the new formula field. And now the name shows up beautifully!

I didn't put the new field on the page layout, of course, so the user UI is none the wiser.

To get everything to show and work correctly, I popped the Flow onto the Lightning App Builder Record Page, and ticked the very important box "Pass record ID into this variable" (got a flow fault email about that one!).

And voila! A many-to-many self-relationship that lets my users add new relationships and see at a glance who's related to whom!

Going forward, the nice-to-haves here would be hyperlinking the relationship & names, and enabling in-line editing. I know it’s easily doable with some third-party stuff, but I don’t want to send my end users on a hunt of additional things to install!

I'm sure there are many other ways to go about this, and I know a few third-party tools offer functionality that applies, too. How would you have built this differently? I'd love to learn!

Maximizing Salesforce

Discussion about this post