CDS Lookup issue in Canvas

[AnjanChidige]

Hi All,

I have Employees, Projects, Project_Allocation entities. The Employee.Email, Project.Name fields are LookUp columns in Project_Allocation entity.

On the Canvas app, I have to filter projects assigned to an employee and show the Project Names in a Drop Down.

On DropDown's Items property, below code is given

Filter('Project_Allocation',Email.Email=_userEmail)

I get the Primary Key field of the Project_Allocation entity. But i need to show the Project Name from Projects entity.

Please suggest.

Thanks

Anjan

[Drrickryp]

Hi @AnjanChidige 

A dropdown contains a single record.  The window shows one of the Values of that record.  You can select any value in the record from the Value dropdown on the right side column with the control selected.  Likewise, any column in the dropdown can be selected in a formula or from anywhere in the App by Dropdown1.Selected.AnyColumnName

[AnjanChidige]

Thanks @Drrickryp  for the quick response.

The dropdown doesn't show the LookUp Fields i.e. Project.

I would like to show the Project Name in the dropdown, which will only come from Projects entity.

Is there any workaround?

Thanks

Anjan

[Drrickryp]

@AnjanChidige 

If you have the projectID as a column in your 'Project_Allocation' table you can add the name using AddColumns() to your Items property.

AddColumns(Filter('Project_Allocation',Email.Email=_userEmail),"ProjectName",Lookup(Projects,ID=projectID,ProjectName))

[AnjanChidige]

Thanks @Drrickryp 

I don't have the column right now on the entity. If I add it, it will be an additional effort for the user, who allocating project to the employee. Use needs to fill in the Project Id along with selecting the project. 

Is there a way to automatically update project ID to this new field, based on the Project selection in LookUp column in CDS?

Thanks
Anjan

[Drrickryp]

@AnjanChidige 

It depends upon how you set up your app.  For example, you have a table of projects and you want the user to enter data for allocations to that project. (a classic One to Many relationship). In the form for adding to the allocations table, you would have a dropdown with the Items property Projects displaying project name.   It would save the ID field from the Project dropdown as projectID in the allocation table. This would prevent a user from allocating anything to a nonexisting project since they could only choose to allocate items to an existing project.  ie. there would have to be a Project created first before anything could be allocated to it.  

[AnjanChidige]

@Drrickryp ,

I already have the Project fields(which is a lookup filed to Project Entity). So, the user would browse Projects and select one for Employee. 

The selected Project will be stored in the LookUp field. This will be of a Record type. 

If I add an additional column i.e. ProjectID, the User has to explicitly enter a value for this column. I was concerned about this additional data. 

I hope, I made my point clear.

Thanks

Anjan

[Drrickryp]

@AnjanChidige 

I am trying to understand the structure of your database.  Obviously each project can have many employees,  can each employee have many projects? 

[AnjanChidige]

Yes, its Many to Many. 

[Drrickryp · Accepted solution]

Hi @AnjanChidige 

While is use CDS extensively in my apps, I do not use the Many-to-Many relationship automatically assigned in my entities.  I am "Old school" and create a junction table entity.  I frequently have additional fields that I want to add to the junction table and this cannot be done with the out of the box relationships provided in CDS.  Perhaps @EricRegnier can help here.  Here is a reference to the relate/unrelate functions that may be helpful. https://powerapps.microsoft.com/en-us/blog/relate-records-in-many-to-many-relationships/