Setting up many to many relationships between tables, or between multiple columns in a single table, in Dataverse
Hoping someone might lend insight into something I am trying to sort out.
I am working on an app that contains a very large table (several thousand records) that contains information on virtual assistants and their information, and on clients and client information – the table contains multiple entries for the same VA, and multiple entries of the same client depending on whether one VA services multiple clients, or one client has multiple VAs.
I have the app set up now such that, a VA “button” navigates to a VA page where a gallery points to the table and uses the VA column/attribute to display VAs, and when a VA is selected, the gallery pushed that VA record to a form to display the fields. There is also a Client “button” that navigates to a client page where a gallery points to the table and uses the client column/attribute to display clients, and when a client is selected, the gallery pushed that client record to a form to display the fields. The fields also allow the user to update the records.
But, in the gallery for the VAs, there are multiple entries for some VAs, because some VAs service multiple Clients. therefore some VAs show up multiple times.
And the same for the Gallery on the Clients page – some clients have multiple VAs, so the same client can show up multiple times in the Clients gallery.
This is just the typical many-to-many relationship common when setting up a relational database. So, based on what I am reading, Power Apps can handle this once the data is in Dataverse, and it looks like there are two approaches. I can have either – one table for VAs and one for Clients and set up a many to many relationship between them the client name and VA name columns. I believe it is discussed here:
Having multiple instances of the same VA show up in the VA gallery, or Having multiple instances of the same client show up in the client gallery is not satisfactory.
Was hoping someone might point me towards an approach… would it be better to use a separate client table and VA table, and establish a many-to-many relationship between them (1st URL), or would it be best to keep the one table and set up a many-to-many relationship between the client and VA columns (2nd URL) in the one large table that contains both?
I think – the second approach is best because the relationships are already established in the table. If I did it the first way, then the existing relational information between VA and client would not exist and would need to be mapped – unless a third relational table that mapped clients to VAs could be imported.
I am not sure yet how it will impact the user interface, or if the interface I have built so far will need to be changed… since right now, I am simply using the gallery form approach I mentioned above.
I’m just looking for thoughts or insights. Which is the best approach – 2 relational tables, or 1 table with relational columns, and, how will that impact the interface I built?
I would recommend setting up a custom many to many relatiomship, meaning you create a custom table (e.g. Client Virtual Assistants), and add lookups to both Client and VA. Using this approach will allow you to add additional attributes as well versus a native Many to Many relationship which has many limitations. You can then filter that table in your Canvas app based on either a client or VA so that you don't end up with duplicates.
I would also recommend creating an Alternate Key on your Client Virtual Assistant table composed of the Client and VA lookups so that duplicates don't end up in that table.
--- Please click Accept as Solution if my post answered your question. This will help others find solutions to similar questions. If you like my post and/or find it helpful, please consider giving it a Thumbs Up.