Maintain unidirectional synch between two fields from different tables linking to the same source

[Marc_Torra]

I have three tables: PROJECTS, ASSETS and PLACEMENTS

In both ASSETS and PLACEMENTS there is a field that links to PROJECTS, with the possibility of linking to multiple projects. They are called asset_projects and placement_projects respectively.

I need any project added to or removed from asset_projects to be added or removed from asset_placements, but not the other way around. This is, adding or removing projects from placement_projects shall not affect asset_projects, resulting in an unidirectional synch between the two fields.

I was wondering if there is a way to do that which does not involve the need to use a script and if a script needs to be used, I was wondering if anyone has ever created a similar script that I could recycle.

Thank you

[TheTimeSavingCo]

> I need any project added to or removed from asset_projects to be added or removed from asset_placements, but not the other way around.
Hm, what type of field is "asset_placements"?  A linked field between ASSETS to PLACEMENTS?

Could you provide screenshots of the tables and relevant fields as well as an example workflow?  E.g. you add "Project 1" to "asset_projects", then what happens in what fields?

[Marc_Torra]

Thanks @TheTimeSavingCo for the help.

The three tables are PROJECTS, ASSETS and PLACEMENTS.

The same asset can be applied in several projects and a project can apply several assets, so the link between both tables is many to many.

One asset can be distributed in several placements, with a placement belonging always to one asset only, so the link is one asset to many placements.

For example, imagine a project in rural electrification with a specific configuration of solar stand-alone system distributed in 10 different households. Each of the ten solar-stand alone systems in each of the ten locations is a placement of the asset, with the asset being the 10 solar-stand alone systems with an identical configuration.

Locations inherit the projects from the asset, so if the asset is involved in a project called "Village Electrification" (asset_projects = "Electrify Schools in the Outer Islands"), the field placement_projects also will have to reflect that (placement_project = "Electrify Schools in the Outer Islands"). However, users shall be able to remove a link for a specific placement if needed, without affecting the link between the asset and the project. This is why the synch is in one direction only, from assets to placements, but not the other way around.

For example, it may happen that in one of the placements the solar stand-alone system is not part of this project even though it has the same asset configuration. Ot that specific placement may be involved in another project, without involving the other 9 placements of the same asset.

I hope this helps you see the structure but please don't doubt to ask if needed.

[TheTimeSavingCo]

Ah, hm

Perhaps you could use an automation for this that would:
1. Trigger when "asset_projects" is updated
2. Uses a "Repeating Group" function on all the placements that are linked to this asset
3. Updates each of those placements with the projects that the asset is linked to

You would need to create another linked field in the PLACEMENTS table where you would key in the extra project though, as that's the only way I can think of to keep track of whether a placement's had an additional project added to it.  Step 3 of the automation above would add the records from this new linked field in as well

If I were you, I would not have a link between each asset and project, and would only have:
1. Link between Project and Placement
2. Link between Asset and Placement

Which seems like it would solve this problem

[Marc_Torra]

Thanks Adam,

I have finally solved it by creating another table than links one PROJECT to one PLACEMENT rather than having a link in ASSETS and one in PLACEMENTS, both to PROJECTS. You gave me the idea when you suggested to remove the link between ASSETS and PROJECTS, so I thank you for that. I also thank you for letting me know about the option "Repeating group".