Chose multiple values from Dataverse look up column

[mohanmitta]

Hello dear group members

I have a requirement to chose multiple users from User table

I have a child table called 'Tracker' with column name as 'Approvers' (Look up column with 'User' table). But I am not able to chose more than one user from the column. I am struggling to find a option to enable allow multiple values for the column.

please suggest what is the best way to achieve this. Because we have few data flows which will insert bulk data to 'Trackers' table & as well as through Power apps we would like to insert data.

[v-xiaochen-msft · Accepted solution]

Hi @mohanmitta ,

Lookup column cannot save multiple data.

According to your description, which of the following is the relationship between your two tables?

1) Tracker --- User

      One    --- Many

2) Tracker --- User

     Many   --- Many

If it is the first type of relationship, you should create a lookup column in the user table. The related table of this column is ‘Tracker’.

If it is the second type of relationship, you should create a many-to-many relationship between your 2 tables.

Then you could Relate() function .

Finally, you could also create columns similar to the primary key and foreign key of SQL SERVER.

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

[mohanmitta]

Hi Wearsky,

I have created two tables 1. ParentTest 2. ChildTest

2. Created one to many from ParentTest to ChildTest with the column name 'ParentTestMutiple' 

but when I start adding data, I could not able to chose multiple values from the column 'ParentTestMutiple' in the table 'ChildTest'

I am attaching respective screen shots here. Please suggest

[v-xiaochen-msft]

Hi @mohanmitta ,

This is by design.

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.

[mohanmitta]

Hi Wearsky, in this case how would I assign same task to multiple users? please advise

Thanks,

Mohan

[DavidGiuliani]

This video might help:  https://youtu.be/DjrAF4n85MY

[Schwartz_Cory]

Is Microsoft planning on adding multi selection to lookup columns? Because this method is a joke. I have to create a relationship, and then "relate"/"unrelate" values from that table. Still does not solve the problem of getting a lookup column in my table that allows me to select multiple values from another table for that column, not the entire table. I think SharePoint Lists do this better 😂

[claytsch]

Agreed.  We're trying to migrate most of our data from SharePoint Lists to DataVerse tables but these features are a must.

[sallyolle]

Agreed.  Its useless

[Fiasco]

I think I have a solution. I was digging into this myself while working on ways to move from SharePoint Online to Dataverse. I'm trying to get things into a model-driven app format. It's not a perfect solution, and likely is not how you imagined your workflows going... But now you have the information to choose your own strategies.  I'm going to try work with this is an attempt to keep data structures as close to elegant as I can. Best of luck to us all.

Here's the trick: Your many-to-many lookup is already accessible in your table forms and views(!?) after you have created the many-to-many relationship in your table. You just need to know where to look and how to use it. It can be non-intuitive at first, but it is viewable in the "Related" tab after you have created a new row of data in the table which you wanted the lookup added. Note: without trying to data from the many-to-many or the related tables. In retrospect, it kind of makes sense, because the hidden join table can only contain rows of data that already exists in the joined tables; you need each related table to have unique data before you try selecting items from one for the other.

I'm including a visual guide because that's how I learn best and how I hope to find these solutions 🙂 
This guide assumes you have two dataverse tables created, the "lookup" table has data in it, and that the "looking" table (or both) has had a many-to-many relationship created relating the two tables.

Pic 1: Form Editor showing where you will find everything. But you can't open it in form edit mode. Very tricky, Microsoft folk.

Pic 2: New entry view.  Step 1: create your new row of data. You cannot see the relationship table/lookups/anything. Don't be discouraged.

Pic 3: Table view of your new data. Step 2: open/edit that data row.

Pic 4: Form view showing your data for editing. Note the "Related" tab. Step 3: Click it, your many-to-many relationship you set up with another table is there!

Pic 5: View of your many-to-many relationships with the other table. Step 4: You can "Add Existing "to pick from your related table. In the pic, I have 2 items from my related table showing that I already added.

I really hope this helps someone. It's not the answer we all want. However, it's an answer that doesn't seem to add extra work or workarounds, and may just be a matter of teaching end users how to use the tools. Of course, we can also ask Microsoft to build that many-to-many lookup we all dream of.