Table field with many-to-many relationship, save record in viewform/editform

(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

Datatable in this scenario:

Contracts table:
- Field: Optional Viewers: type: Lookup to Users (Default table from the system)

Users table: (Default table from system)
- Field: Full name, email...

Now I am trying to deal with the N:N relationship

*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 …) -> Editform for edit contract detail

I would like to create a field that indicated who (From User table) can view this specific contract detail.

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.

And back to Viewform, in this field, I set

Item = ThisItem.'Optional Viewers'

But it will only show ONE record in the Viewform, and also in the database also only has ONE record.

How can it be viewed correctly in Viewform, and save correctly (Many Users) into CDS table? 😥

Thanks in advance 🙏🙏🙏🙏🙏

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

v-jefferni on at

Like (1)

Report
Hi @HCLin ,

Would you like to relate the contact record with multiple users selected in the combo box from User table in CDS?

If so, and you have already created the N:N relationship between Contact and User tables, you will only need to set DefaultSelectedItems of the combo box to:

Gallery.Selected.Users

There is no need to create a "Optional Viewers" field since the related items will not be stored into a specific field, create N:N relationship with Relate function just create links among records.

The formula on the Submit Button is in my last reply for you in another thread:
Solved: Re: CDS Choices field from Lookup table - Power Platform Community (microsoft.com)
SubmitForm(EditForm);ForAll(ComboBox.SelectedItems,Relate(Gallery.Selected.Users,ThisRecord))

Notice that it is a Custom Card in the form, so the "Optional Viewer" Label is manually added into the Card, this custom card does not represent a field in Contact table.

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.

Was this reply helpful? Yes No
HCLin 124 on at

Like (0)

Report
Hi @v-jefferni ,

Thanks for your reply, I successfully set up the relation, but the thing is that I would like to use this "field/ relationship" to filter the user access.

I will filter my contracts gallery based on the username, such as username in the "Optional viewer"(this filed/ this Combobox), then the user can access/see this specific contract.

So based on this situation, how do I call this field correctly.

Ex: If I would like to filter on the PM, I will do
Items = Filter(Contracts, ('Project Manager'.'Manager Name' = First(CurrentUser).Username))
But now, I am filtering by "Optional Viewers", and since it is not a "real field" in the contracts table, how do I deal with it?

Thanks!

Was this reply helpful? Yes No
Verified answer

v-jefferni on at

Like (1)

Report
Hi @HCLin ,

Since you have created N:N relationship, imagine that each contact relate to several users, does this mean each user is related with several contacts as well? So, if you would like to find all contacts that a specific user is one of its "Optional Viewer", you will need to find that user in User table and get its related contacts.

Not sure if you could understand clearly. On your end, you could apply below formula in the Items of Gallery to find contacts relate to current user:

LookUp(Users,'User Name' = varEmail).'Contacts (RelationshipName)'

To avoid delegation issue, set OnStart of the App with the variable:
Set(varEmail,User().Email)

If you would like to use a TextInput as search box, then the formula would be:
LookUp(Users,StartsWith('User Name', SearchBox.Text).'Contacts (RelationshipName)'

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.

Was this reply helpful? Yes No
HCLin 124 on at

Like (0)

Report

@v-jefferni ,

Thanks for the detailed information.
It works!

Was this reply helpful? Yes No