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

Same column name in multiple tables

Hi please can I ask a little naming issue:

 

I use sharepoint online custom list(s) as data source(s).

 

I have for example list Customers with CustomerName column.

Then I have list Buys with CustomerName column.

 

 

I want to make collection  for example ClearCollect(CustomerCache;Customers).

(its nice I think it gets all (more then 500 rows) .. it get all server data)

 

Then i wanted to make left join to Buys for example with AddColumns function.

 

Here is the problem:

____________________________

ClearCollect(CustomerCache;

AddColumns(Customers;
"AddedColumn1";LookUp(Buys;CustomerName=CustomerName)
)

)
___________________________

From https://powerapps.microsoft.com/en-us/tutorials/operators/#disambiguation-operator
I tried to change to CustomerName=Customers[@CustomerName])

But from I tried, it does not use "Parent" Column, but instead it use completly new "intance" of table Customers and found there in column (this issue causes blue dot (no server delegation)).

 

By documentation both AddColumns/ForAll function does NOT allow delegation

(but I tried to make Table1 with Column1 and Table2 with Column2 .. and working nicely with Column2=Column1 ..so from my experience yes it is possible to call "server delegation" within "foreach" function which is amazing background functionality making a client offline with advanced query and join abilities)

 

Is it possible to use like "Parent.Column" or "Customers.CustomerName" or Customers.CustomerName or something like that ....  to reference on parent column ?

(AddColumns use Customers ... and within is LookUp to Buys ..)

 

I am thinking about use Rename function and then call AddColumns but I think this will make AddColumns/ForAll calls 2 times (I wanted to do so in 1 iteration)

 

(usage of this whole conception is very comfortly working application : Customers buy items. Using Timer all data from server are downloaded to make collection and those use SaveData ... so Customers are able to use all app Offline and when they get Online just new data are cached ..... and those ForAll/AddColumns functionality in final will be able for every Customer lookup their Latest Buy ... from programming perspective I just want to make 1 call server per each Customer .. but I dont know how to reference "columns" yet)

4 REPLIES 4
Meneghino
Level 10

Re: Same column name in multiple tables

 

Hi @Marek, thanks for trying hard before asking questions on the forum.

 

First of all I think that you need to disambiguate both sides for clearer syntax, in other words:

ClearCollect(CustomerCache;
	AddColumns(Customers;
		"AddedColumn1";LookUp(Buys; Buys[@CustomerName]=Customers[@CustomerName])
		)
	)

 

Please let me know what results this gives, but this may not work.  This is now not due to syntax, but due to delegation and perfomance.  This is because a call to the Buys table needs to be made for every row of Customers, and this may not happen so you get a blank instead.

 

I would suggest another approach that I use often, and that would speed up performance significantly.  You should cache the Buys table first and then use the cached version for any lookups etc.  This is because the ForAll is nice, but also does separate calls to the server for every row.

 

If Buys has <= 500 rows then caching is trivial, if not please let me know and I can point you to the solution for caching SharePoint lists with > 500 rows.  However some details of the total number of rows and any columns that we can use to segment the table would be useful.

JRaasumaa
Level 10

Re: Same column name in multiple tables

What Meneghino said is how I've been able to join tables together. You need to do a collect of both tables and then join them with AddColumns. 

 

If you have too many rows if it's possible to filter data first you can always trim down the collections and then join and display, this is how we're working around the ridiculous 500 row limit.

 

 

Marek
Level: Powered On

Re: Same column name in multiple tables

hi Meneghin JRaasumaa

 

Thanks for replies sorry for so much late feedback

 

1. From I tried, Table[@Column] is working only for Collections (when used with dataSource blueIcon is appearing)

2. I wanted to join 20 rows table to X (thousands) rows table ... (for each record on left table, get the "latest" one record from right table... usage is for example when Manager want to see for each Customer thing he/she bought latest

 

(I resolved it by RenameColumns(AddColumns(RenameColumns .... rename column to unique name, make lookup, renameColumn to original) 

 

This allowed me to make "lookup" using delegation (because it is executing forAll on clientSide .. but request to serverSide is perRow ... its is slow because 20 times different request, but "query" in powerapps is just one and it is executed agaist whole server data (deleg)

 

.. yes it is slow, but next time i will make server side (SharePOnline) column(s) internalName unique within multiple tables

 

 

But thanks again and sorry for delay

srobin
Level: Power Up

Re: Same column name in multiple tables

Hi @Marek,

I'm stuggling with the same issue you had in this thread some time back and was wondering if you could elaborate on how you used RenameColumns to resolve your problem.

As I mentioned my issue is that I have a data table that I want to add another field to from another sql table. The field I want to join on has the same name in both tables. My items fx dialog for the data table is

AddColumns('[eng].[in_out_take]',"Target_Daily",LookUp('[eng].[location_target]',in_out_take_id=in_out_take_id).target_daily)

 

If I change the name of in_out_take_id field in the source table ([eng].[in_out_take]) it works perfectly. Ive tried lots of different approaches to resolve the issue but ran into problems with each. Sounds like the RenameColumns might be the solution but I discovered I cant chain commans in an items fx dialog so was wodnering how you got around this.

Would really appreciate any help.

Helpful resources

Announcements
New Ranks and Rank Icons in April

'New Ranks and Rank Icons in April

Read the announcement for more information!

Better Together’ Contest Finalists Announced!

'Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

thirdimage

Power Apps Community User Group Member Badge

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

sixthImage

Join THE global Microsoft Power Platform event series

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

thirdimage

Community Summit North America

Innovate, Collaborate, Grow - The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors
Users online (9,827)