Same column name in multiple tables

Like (1) Share

Report

Posted on 2 Aug 2017 08:29:42 by Marek

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)

Categories:

Connecting to Data

All responses (4)

Answers (0)

srobin 2 on 16 Oct 2019 at 15:37:01

Like (0)

Report

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.
Marek 43 on 15 Aug 2017 at 12:56:20

Like (0)

Report

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
JRaasumaa 1,325 on 02 Aug 2017 at 10:03:50

Like (1)

Report

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.
Meneghino 6,949 on 02 Aug 2017 at 08:54:17

Like (1)

Report
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.