Many to Many to Many relationship

[mkas]

Hey everyone,
Hoping somebody has some idea of how I can achieve this in Espo.

We have multiple tables:
* Mission
* Simulator
* Console
* Member

When we create a mission, we choose a simulator.
Each simulator has consoles that are attached to it.

When we create a mission, we want a list populated in the mission of all the consoles that are linked to the simulator.
We then want to be able to associate a member to each of the consoles.

So in the end, a member is linked to a console, which is linked to a mission (and a scenario).

I have attached how we anticipate the screen would look (roughly)

We also want the console and mission to show on the members detail page.

Thanks heaps for any guidance and advice.

[Mark G.]

I don't have an answer unfortunately, but sadly the documentation for linking tables in Espo, is very sparse

[rabii]

Hi mkas

i Think this could be done as below (need to make sure you choose the correct schema for your tables and the right relationships), i would approach this as below:

1- Mission One-to-One Right Simulator (If one Simulator is linked to only one Mission) / otherwise if one Simulator could be linked to multiple Missions then you need to set up this relationship (Mission Many-to-One Simulator)

2- Mission One-to-One Right Scenario (If one Simulator is linked to only one Mission) / otherwise if one Scenario could be linked to multiple Missions then you need to set up this relationship (Mission Many-to-One Scenario)

3- Mission One-to-Many Console (if many Consoles belongs to only one Mission) / otherwise if Consoles could be shared by many other Mission then you need to set up this relationship (Mission Many-to-Many Console)

4- Mission One-to-Many Member (if many Consoles belongs to only one Mission) / otherwise if Members could be shared by many other Mission then you need to set up this relationship (Mission Many-to-Many Member)

5- Simulator One-to-Many Console

Then on formula for Mission you could do something like code below:

// Check if we have a selected simulator then get its consoles and link them to current Mission
ifThen(simulatorId,

// Get count of consoles of the related Simulator linked to current Mission
$consoleCount = record\count('Console', 'simulatorId=', simulatorId);

// Get all consoles of current simulatorId
$consolesIds = record\findRelatedMany('Simulator', simulatorId, 'consoles', $consoleCount, 'createdAt', 'desc');

// Link consoles of the simulator to the current Mission
entity\addLinkMultipleId('consoles', $consolesIds);

);

- The tricky thing here is how to link a Console to a Member it could be done creating a custom view which would allow you to choose a Member to a specific console.

I hope this helps, remember you need to think about how the relationships should be set (it makes all the differences).

Cheers