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

[Lalit_Kadam]

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

[v-siky-msft]

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

[Lalit_Kadam]

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

[v-siky-msft · Accepted solution]

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.

Hope this helps.

Sik