Join two CDS entity and show on gallery on powerapp

[Lalit_Kadam]

Hi,

I am trying to join and load two entities data in one collection in powerapps but some how not able to add second entity's all columns in collection. it showing only first entity columns.

Applied blow steps.

WWD1S--First entity

UserMaster---Second entity

screen on visible property :-  ClearCollect(CDS_collection, WWD1S);ForAll(UserMasters,Collect(CDS_collection, {User_id:User_id}),AddColumns(UserMasters.User_id))

Item property on gallery :- CDS_collection

How can i add both entities column in single collection and can show on gallery?

Regards,

Lalit Kadam

[GarethPrisk · Accepted solution]

Are both entities custom entities?

Are they related to one another, like WWD1S (N:1) UserMaster?

If so, this seems like you'll simply need to Collect WWD1S records. You'll be able to reference the related UserMaster in a Gallery by stepping through the relationship. Like ThisItem.(UserMasterFieldNameOnWWD1S).FieldOnUserMaster

[LalitKadam]

Hi,

The relationship is not exists on CDS level on both entity. but i have created record like User_id is a common field between entities. All UserMaster tables's User_id are exists in WDD1S entity.

Can we show both entity data on gallery without relationship in CDS ?

If No, can you please tell me each steps to achieve same with relationship entity?

Regards,

Lalit Kadam

[Lalit_Kadam]

Hi,

The relationship is not exists on CDS level on both entity. but i have created record like User_id is a common field between both entity. The UserMaster tables's all User_id are exists in WDD1S entity.

Can we show both entity data on gallery without relationship in CDS ?

If No, can you please tell me each steps to achieve same with relationship entity?

Regards,

Lalit Kadam

[v-siky-msft]

Hi @Lalit_Kadam,

The Relationship is not required, please try this:

ClearCollect(CDS_collection,  AddColumns(WWD1S, "Column1", "Column2"));
ForAll(UserMasters, Patch(CDS_collection, LookUp(CDS_collection, User_id=User_id ), {Column1: 'Column1InUserMasters', Column2: 'Column2InUserMasters'})) 

Hope this helps.

Sik

[Lalit_Kadam]

Hi,

I tried below code. but it is not working. it showing "Invalid Argument". Please refer attached image of error.

ClearCollect(cds_coll2, AddColumns(WWD1S, "User_Name", "User_Type"));
ForAll(UserMasters, Patch(cds_coll2, LookUp(cds_coll2, User_id=MasterUser_id ), {User_name: 'User_NnameInUserMasters', User_type:'User_TypeInUserMasters'}))

Note, I am using CDS as datasource for both entity. 

Please let me know if i need to change anything.

Regards,

Lalit Kadam

[v-siky-msft · Accepted solution]

Hi @LalitKadam 

Sorry, I missed to define values in AddColumns function, please try this:

ClearCollect(cds_coll2, AddColumns(WWD1S, "User_Name", "",  "User_Type",""));
ForAll(UserMasters, Patch(cds_coll2, LookUp(cds_coll2, User_id=MasterUser_id ), {User_name: 'User_NnameInUserMasters', User_type:'User_TypeInUserMasters'}))

Note: please ensure the 'User_NnameInUserMasters' and 'User_TypeInUserMasters' are the real column name in UserMasters Entity.

Sik

[Lalit_Kadam]

Thanks a lot Sik !

It is working.

[Lalit_Kadam]

Hi Sik,

I am trying to add multiple entities in one Collection.  but it is not working. I have four tables. (Chemical, Labs, chemicalOrder and Location).

Chemical and chemical_Order entities  are connected based on chemical_id.

Chemical_order and LABS entities  are connected based on Lab_ID.

LABS and Location entities are connected based on Location_id.

Using below code.

ClearCollect(SearchResultCollection,Chemicals);ClearCollect(SearchResultCollection,AddColumns(Chemicals,"ChemicalOrderID","","Lab_ID",""));

ForAll(ChemicalOrders,Patch(SearchResultCollection,LookUp(SearchResultCollection,ChemicalId=Chemical_ID),
{ChemicalOrderID:crb05_chemicalorderid,Lab_ID:crb05_lab_id}));

ClearCollect(SearchResultCollection,AddColumns(Chemicals,"Lab_Name",""));
ForAll(LABS,Patch(SearchResultCollection,LookUp(SearchResultCollection,Lab_ID=Lab_ID),{Lab_Name:crb05_lab_name})); ClearCollect(SearchResultCollection,AddColumns(Chemicals,"Location_Name",""));ForAll(Locations,Patch(SearchResultCollection,LookUp(SearchResultCollection,Lab_ID=Lab_ID),{Location_Name:crb05_location_name}));

Above code is not showing correct result.Need your help here to correct this code. Please suggest.

Regards,

Lalit Kadam