cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
ajyendra24
Level: Powered On

Need to Merge Two DataTable with same Columns in Powerapps

Hi All,

 

I want to create a datatable which is merge of two dataTable that contain Account and Contact Data respectively which I fetched from Dynamic CRM to connect Database. Now What happen When I Merge that Datatable through accountid it will not work as expected.

using Lookup It will not work. 

accountid in Account and parentAccountid in Contacts both are Guid Data Type.

So Can you please Suggest me how we can do that I almost waste couple of hours. 

 

Thanks 

Ajyendra Singh

10 REPLIES 10
Community Support Team
Community Support Team

Re: Need to Merge Two DataTable with same Columns in Powerapps

Hi @ajyendra24 ,

Could you please share a bit more about your scenario?

Do you want to merge the two data tables (Account Table & Contact Table) into single one Table based on the accountid and parentAccountid column?

Further, does the parentAccountid column in Contact Table reference values from the accountid column in your Account Table?

 

Based on the needs that you mentioned, I think the AddColumns function could achieve your needs. I have made a test on my side, please take a try with the following workaround:

Set the OnSelect property of a Button control to following:

ClearCollect(
                    MergedTable,
                    AddColumns(
                                         'Your Contact Table',
                                          "Account Name",
                                           LookUp('Your Account Table', accountid = 'Your Contact Table'[@parentAccountid], 'Account Name'),
"Email",
LookUp('Your Account Table', accountid = 'Your Contact Table'[@parentAccountid], Email),
"Phone Number",
LookUp('Your Account Table', accountid = 'Your Contact Table'[@parentAccountid], 'Main Phone'),
... ) )

Note: The 'Account Name', Email, 'Main Phone', ... represents the columns from your 'Your Account Table'. Please also set the "Data row limit for Non-delegable queries" option to maximum value -- 2000 within Advanced settings of App settings of your app.

Then you could use the MergedTable collection as data source within your app.

 

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ajyendra24
Level: Powered On

Re: Need to Merge Two DataTable with same Columns in Powerapps

Hi @v-xida-msft ,

Thanks for the reply.

Yes,  parentAccountid column in Contact Table reference values from the accountid column in Account Table.

LookUp(Accounts,Account = GUID(ThisItem._parentcustomerid_value),name)

this statement have some issue when I use in DataTable . But it is worked for Gallery.

Is something I missed or have some other syntax for datatable?

 

Thanks 

Ajyendra

Community Support Team
Community Support Team

Re: Need to Merge Two DataTable with same Columns in Powerapps

Hi @ajyendra24 ,

Could you please share a bit more about the error message?

 

I have made a test on my side, please take a try with the following workaround:3.JPG

Set the Items property of the Data Table to following:

AddColumns(
Contacts,
"AccountName",
LookUp([@Accounts], Account = AsType(Contacts[@'Company Name'], [@Accounts]).Account, 'Account Name')
)

Actually, the 'Company Name' (parentcustomerid) column is a Customer type (Polymorphic lookups) in CDS Entity, it could reference values from Accounts Entity or Contacts Entity.

Please check the following article for more details:

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/working-with-references#show-the-fields...

 

If you want to reference values from Accounts Entity within your Contacts Entity, you could achieve your needs through the  'Company Name' (parentcustomerid) column. Please consider modify above formula as below:4.JPG

Set the Items property of the Data Table to following:

AddColumns(
Contacts,
"AccountName",
AsType('Company Name', [@Accounts]).'Account Name', /* <-- Accounts represents your Accounts Entity */
"AccountEmail",
AsType('Company Name', [@Accounts]).Email,
...
)

Note: Reference values from the Accounts Entity through the 'Company Name' (parentcustomerid) column in your Contacts Entity.

Please take a try with above solution, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ajyendra24
Level: Powered On

Re: Need to Merge Two DataTable with same Columns in Powerapps

Hi @v-xida-msft ,

 

I just follow your steps but still have that issue in That:

attached a screenshot below.

 

error message:

When Used First Code :

The function 'AddColumns' has some invalid arguments.

The function 'AsType' has some invalid arguments.

When Used Second Code:

The function 'AddColumns' has some invalid arguments.

The function 'AsType' has some invalid arguments.

Name isn't valid. This identifier isn't recognized.

 

Thanks

Ajyendra

Community Support Team
Community Support Team

Re: Need to Merge Two DataTable with same Columns in Powerapps

Hi @ajyendra24 ,

Currently, I could not reproduce your issue on my side. Please consider re-add a new Data Table control, then apply the formula again, enable the corresponding fields within the Data Table control, check if the issue is solved.

 

Please firstly make sure you have added Accounts data source within your app already. In addition, please make sure you have turned on the "Relationl data, option sets, and other new features for new features" option and "Improve data source experience and Common Data Service view" option within Advanced settings of App settings of your app.

 

In addition, please consider re-create a new app, and add the Accounts Entity and Contacts Entity within your app, then add a new Data Table control, then try above formula I provided again, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ajyendra24
Level: Powered On

Re: Need to Merge Two DataTable with same Columns in Powerapps

Hi @v-xida-msft ,

Thanks for the reply but seem not working I just added few images please Check Am I missing something?

Already have  Accounts Entity and Contacts Entity within my app.

 

Thanks 

Ajyendra

Community Support Team
Community Support Team

Re: Need to Merge Two DataTable with same Columns in Powerapps

Hi @ajyendra24 ,

Currently, I could not reproduce your issue on my side. Is there some error with ther AddColumns formula I provided above in your app?

Have you taken a try to add a New Data Table within your app?

 

Also please consider take a try with the following workaround:

Set the OnStart property of the App control to following:

ClearCollect(
MergedTable,
AddColumns(
Contacts,
"AccountName",
AsType('Company Name', [@Accounts]).'Account Name' /* <-- Accounts represents your Accounts Entity */
)
)

 Add a New Data Table within your app, set the Items property to following:

MergedTable

then re-load your app (fire the OnStart property of App), check if the issue is solved.

 

If the issue still exists, please consider re-create a new app based on your Entities, then try above solution again, check if the issue is solved.

 

Also please consider turn off the "Enable improved data table selection and Value property" option and "Improved app rendering (final validation)" option within Advanced settings of App settings of your app, then add a new data table control in your app, try above formula again, check if the issue is solved.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ajyendra24
Level: Powered On

Re: Need to Merge Two DataTable with same Columns in Powerapps

Hi @v-xida-msft ,

 

not working as AsType function have some issue it always give error of AsType has invalid arguement.

Please See below attachment of formula what I wrote

Community Support Team
Community Support Team

Re: Need to Merge Two DataTable with same Columns in Powerapps

Hi @ajyendra24 ,

Please check if you have a 'Parent' column within your Address Entity. 

 

Also please check if the  'Parent' column in your Address Entity is the LookUp column references records from the Accounts Entity.

 

Best regards,

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors (Last 30 Days)
Users online (5,309)