cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper III
Helper III

create relation between entity and use with gallery and drop down and Text Box

Hi,

 

I have two tables (Chemical and Lab). both table have common column Lab_Id. I want to create relation between both entity(chemical (Many):Lab(one)) in CDS.

 

Once relationship is created. I have to use both entity In Gallery and dropdown and Text box control in canvas app.

What will be difference in code level if I make relation between entity? should I need to use relationship ID?

Please Suggest.

 

Regards,

Lalit K

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Lalit_Kadam ,

 

The second way is correct, The reason that you can only see Lab name and Supervisor on the first line of gallery is that you only relate the first record of child entity to the parent entity. The records for the remaining child entities have not yet created a relationship.

 

Here are two ways to assign the LookUp field in batch:

1. Based on the common column Lab_Id. Put this code to Button.OnSelct to trigger it manually, the LookUp field will be assigned automatically based on the common Lab_Id.

 

ClearCollect(Col0,RenameColumns(Chemical_Orders,"Lab_Id","LID"));ForAll(Col0,Patch(Chemical_Orders,LookUp(Chemical_Orders,Lab_Id=LID),{Lookup_LAB_chemical_order:LookUp(LAB, Lab_Id=LID)}))

 

2. Edit data in excel, Reference: Edit data in Excel , then you can select the related record from parent entity to assign the LookUp field.

Snipaste_2020-04-27_10-26-48.png

Hope this helps.

Sik

View solution in original post

3 REPLIES 3
Community Support
Community Support

Hi @Lalit_Kadam ,

 

After the N:1 relationship is created, a lookup field is generated automatically in Chemical Entity(Child). You have to link the record from Lab Entity(Parent) to the lookup field manually.

The N:1 relationship is not established completely until the LookUp field is linked to the parent entity's record.

How to link records of two entities? Please reference from Relate and Unrelate functions  .

 

The LookUp field in Child Entity represents the whole related record of Parent Entity. If you collect the lookup field to Collection, you can find it is a nested table with the same data construct of Parent entity. 

So, If you want to reference the other fields of related record in Child entity gallery, please try this:

First(ChildEntity).LookUpField.FIeldNameInParentEntity.

On the contrary, if you want to get all related child entity records, please try this:

First(ParentEntity).ChildEntityName

 

Here is an excellent blog on how to apply N:1 relationship in PowerApps, please take a focus on it.

One-to-Many Relationships for Canvas apps 

Hope this helps.

Sik

 

 

Hi Sik,

Thanks for your details information. It is very useful for further development.

I have more query on this topic.

I have to load both(Parent and child ) entities all column in single gallery. there for I tried two ways but are not working.

 

1.  Add code on screen 'OnVisible' Property to make collection of both entity.  when I use myconn1 collection in gallery data is not displaying on gallery.

Relate(First(LABS).Chemical_Orders,First(Chemical_Orders));ClearCollect(myconn1,Chemical_Orders.Lookup_LAB_chemical_order.Lab_Name);

 

2.  Add child entity(Chemical) on gallery item property and add two extra label to fetch (Lab name and Lab supervisor) from parent entity (Lab) on gallery. but only first row data only displaying on gallery for Lab name and supervisor.

 

Added below code on label.

Gallery Item property :- Chemical ----Child entity

ThisItem.Lookup_LAB_chemical_order.Lab_Name---- To fetch parent Lab_Name based on lookup field.

ThisItem.Lookup_LAB_chemical_order.Supervisior---- To fetch parent Supervisior based on lookup field.

 

 

Please suggest to fetch both entity columns in single gallery. 

 

 

Regards,

Lalit Kadam

Hi @Lalit_Kadam ,

 

The second way is correct, The reason that you can only see Lab name and Supervisor on the first line of gallery is that you only relate the first record of child entity to the parent entity. The records for the remaining child entities have not yet created a relationship.

 

Here are two ways to assign the LookUp field in batch:

1. Based on the common column Lab_Id. Put this code to Button.OnSelct to trigger it manually, the LookUp field will be assigned automatically based on the common Lab_Id.

 

ClearCollect(Col0,RenameColumns(Chemical_Orders,"Lab_Id","LID"));ForAll(Col0,Patch(Chemical_Orders,LookUp(Chemical_Orders,Lab_Id=LID),{Lookup_LAB_chemical_order:LookUp(LAB, Lab_Id=LID)}))

 

2. Edit data in excel, Reference: Edit data in Excel , then you can select the related record from parent entity to assign the LookUp field.

Snipaste_2020-04-27_10-26-48.png

Hope this helps.

Sik

View solution in original post

Helpful resources

Announcements
New Badges

New Solution Badges!

Check out our new profile badges recognizing authored solutions!

New Power Super Users

Congratulations!

We are excited to announce the Power Apps Super Users!

Power Apps Community Call

Power Apps Community Call: February

Did you miss the call? Check out the Power Apps Community Call here.

Top Solution Authors
Top Kudoed Authors
Users online (77,260)