CDS Coices field from Lookup table

(0) Share

Report

Posted on by HCLin

124

Hi,

I am creating a field as "Optional Viewers" and it supposed to be a Choices type, but I would like the options in the choices to come from a Lookup table called "Users".

I encountered two issues with it:

The Choices type in CDS, the options need to be input manually and they are hardcoded
The Lookup type in CDS, only allow one choice

I have tried two things:

I set the field as Choices and only input a random option from CDS, and tried to Patch a list in the App, but the Patch function doesn't work well with the Choices column

ForAll(Optionviewerslist,
 Patch(Contracts,
 {'Optional Viewers': IsBlank()},
 {'Optional Viewers': Optionviewerslist.Value}))

*Optionviewerslist is a collection I collected from the Lookup table (Users)
This would give me an error that shows"You're updating expects an 'OptionSetValue', type and you are using a 'Text' type."

2. I tried to set the field as Lookup type, and manually change the input box from an Editform in the app to a Combo Box, but in the CDS record, it still only can save one record.

Is there any suggestion I can resolve the Choices type with the options from a lookup table?

Thanks a lot🙏🙏🙏🙏🙏

Categories:

Building Power Apps

All responses (3)

Answers (1)

Sort by

Verified answer

v-jefferni Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @HCLin ,

Could you please share more details about your scenario? Would you like to let users select multiple users from User Table in a dropdown or combo box in a EditForm which displays the item selected from a gallery?

If so, as per your description, I suspect that actually you are seemingly looking for the way to create the Many-to-Many relationship between Contracts table with Users Table.

Please refer to below doc:
https://docs.microsoft.com/en-us/powerapps/maker/data-platform/create-edit-nn-relationships
Relate function refer to:
https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-relate-unrelate#description

On your end, you will need to create a N:N relationship between Contracts table in which you would like to create the Choices field and the User table.

Full steps as follows:
1\ Create N:N relationship follow the steps:
https://docs.microsoft.com/en-us/powerapps/maker/data-platform/data-platform-entity-lookup#add-a-many-to-many-relationship

2\ In EditForm, add a cusom Card, then add a Combo box which set to allow multiple selections.

3\ Set Items of the Combo box to Users table.

4\ In submit button, apply below formula in the OnSelect:

SubmitForm(EditForm);ForAll(ComboBox.SelectedItems,Relate(Gallery.Selected.Users,ThisRecord))

You could see a gallery displays users selected in the combo box, the Items property of the gallery has been set to Gallery.Selected.Users, the DefaultSelectedItems of the Combo box would be like it as well if you would like to display the Users in ViewForm or EditForm.

Hope this helps.

Best regards,
Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.
HCLin 124 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @v-jefferni ,

Thanks for your detailed reply
I just realized and was starting to look into the many-to-many thing, then got this awesome reply.

This helps me A LOT!!!

Thanks again. 😃
HCLin 124 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @v-jefferni ,

I would like to ask your help with a further question.

*I already set Contract table and User table as many-to-many relationship

My scenario is that I have a contract table, and will have a gallery to select a specific contract.
Then will navigate to a contract detail page -> Viewform with all contract detail (eg. Contrac name, contract number, PM …)
I would like to create a field that indicated who (From User table) can view this specific contract detail.
So basically, I am dealing with data card but not on a gallery.
Now the field I created from CDS called “Optional Viewers” and is a “Lookup” data type with “User” table
In Editform, I manually change that input box for this field to ComboBox.

In my Viewform, in this field, I set
But it will only return ONE record, and also in CDS also only has ONE record.

How can it be viewed correctly in Viewform, and save correctly into CDS table?

Thanks in advance.