One to Many Relationship on multiselect

(0) Share

Report

Posted on by Rob90

Hi,

I'm fairly new to Dataverse and I can't seem to figure this out.

I have 2 Dataverse tables,1 main table that contains sites/ locations. Then another table called services which the site can do multiple services so would need to be a multi-select column (dropdown within powerapp). I don't want to use the multi-select choices option as I want the user to have the ability to add additional services and as far as I'm aware the user can't do this within Canvas App.

I have added the new column to the 'sites' database and then referenced the 'services' Table. This should be a 1-to-Many relationship...

on the relationship of the 'Sites' Database, this is saved as a Many to One...

When I try and edit the relationship I don't have the option as it's greyed out...

If I add a relationship it still keeps the existing relationship and I can't update it within the app

Can anyone shed any light on what I might be doing wrong here?

Thanks

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (4)

Answers (0)

Guido Preite 1,490 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

are the services shared between sites? As an example:

You have "Site 1" and "Site 2"

And in "Services" you have

"Maintenance A" and "Maintenance B"

If you can associate "Maintenance A" to both "Site 1" and "Site 2" then you need to create N:N relationship, if "Services" are unique for each site, then you need to create a lookup on "Services" table pointing to "Site"

Was this reply helpful? Yes No
Rob90 33 on at

Like (0)

Report
Copy link

Link copied!

I am basically using services as a multichoice column and should act the way the multichoice column acts, the only reason I am using a table is because I want the user to be able to add choices to select from, I don't think there is a way to patch to multi choice columns that I am aware of.

Basically, 1 record from the site table could have multiple services (e.g. Maintenance, Cleaning).

I did try your second suggestion by creating a lookup within services and pointing to the site but within the canvas app couldn't find any way of this being linked back onto the form as there wouldn't be a column inside the 'site' table to refer to what 'services' are being offered.

Was this reply helpful? Yes No
Guido Preite 1,490 Super User 2024 Season 1 on at

Like (0)

Report
Copy link

Link copied!

in my opinion you need a N:N relationship. If you are doing canvas apps check this video

https://www.youtube.com/watch?v=zvpdBS_nwcs

Was this reply helpful? Yes No
BhargavPatel 660 Moderator on at

Like (0)

Report
Copy link

Link copied!

@Rob90 Can the same service be performed by two different sites?
If yes, then as @GuidoPreite suggested you will need N:N relationship between Site and Service tables. Check out Lisa Crosbie’s video on how to do that. The entire video is very informative, but at 29:22 minutes she shows how to do multi select lookup.
If any given Service can be performed by only one Site then you will have 1:N relation between Site and Service Table. In this case you will need a Lookup column to Sites in the Service table. To show which Services related to Sites, use Subgrid component in the Form for Sites.

Was this reply helpful? Yes No