Many-to-one relationship not allowing to use the same related value twice

I have customModule1 and customModule2 and I need a many-to-many relationship between them, with some additional fields. I chose to work around it, so I created customModule3 and added two relationships to it:

customModule3:customModule1 many-to-one
customModule3:customModule2 many-to-one

I have added some fields and it was fine until I started creating records for customModule3.

For example, if I want to create customModule3 record that has value1 for customModule1 and value2 from customModule2, I can’t if there already exists customModule3 records that has value1 for customModule1. If I change my relationships to one-to-many, then I don’t have the related fields in the layouts.

How do I get this to work?

It’s a bit hard to get this working, but wouldn’t be better to add the fields to the relationship table itself, avoiding the third module?

How do I do that? I read somewhere on this forum that you can’t, that’s why I did this. I know that usually many-to-many relationships are tables on their own.

By the way, I deleted this module and redid everything and now it’s ok. I guess it was some kind of bug…

Yes, relationships have their own table, the question in that article is when you need to add fields to that relationship table.

Studio can’t do that, but it’s a common scenario in database design - every time some field is a property of the relationship, not of any of the two related records.

For example, I created a relationship between Contacts and Contacts to describe families and other work relationships in a school. In that relationship I had an extra field saying what these two people are to each other (Father, Mother, Tutor, etc). That’s a property of the relationship.

Ok, but maybe you don’t need that, if you have things working well already. :slight_smile:

1 Like