I am in search of documentation or advice on how to link two objects together for a many to many relationship. Aware you cannot link the objects within Workshop and have to edit and link in the object view. Anyway around this?
When making the backing dataset for the many to many link, say you have two of the same columns in each dataset, do you make a new dataset with the columns that match from both datasets and then do an outer join on the two columns and create a new dataset and put that as a backing dataset. I did that, now I have thousands of the same cell value links in each column, is that ok? Or should I have a unique 1:1 link between what matches on the datasets not a 100:100 of the same value.
Tried a many to many link and there are many of the same combinations. One dataset has 100k of value X and the other dataset has 10 of value X. Outer joined into that to make 100 times 10? What is a better way? Can I do this in PIPELINE BUILDER? Is there an easy button?
Let’s say you have ObjectTypeA backed by DatasetA and ObjectTypeB backed by DatasetB. If you want a many-to-many link type for A and B, you need a third dataset, DatasetC.
The only required columns in DatasetC are the primary key columns from DatasetA and DatasetB. Each row in DatasetC represents an instance of the many-to-many A-to-B link type. Which actual rows are in DatasetC depends on what real relationships you want to represent. For example, object A1 might be linked to objects B4 and B6. And object B6 might be linked to objects A1 and A13. But this all depends on your real data.
If you already have data integrated from some other system that identifies which objects A should be linked to which objects B, you should use that to create your many-to-many join table. You can do that in Pipeline Builder – it’s no different than creating any other dataset.
If you want users to be able to create new links between objects A and B on the fly, then you have a couple options:
Create an empty many-to-many dataset to back the link type and create an Action that lets users create new links between objects A and B. This is the simplest method and should be your default option.
Create a new object type C that represents the relationship between objects A and B. Here, you would have a one-to-many link type for both A-to-C and B-to-C. This is more complicated but lets you store other attributes on the object C. For example, if you want to record a note when a user creates a new link, you could have a note property on object type C. This is a more advanced setup.
In case future readers aren’t familiar with many-to-many relationships in general, check out this Wikipedia page to get a grounding in the basic premise.