AddColumns adds a new column with all duplicating data

Like (0) Share

Report

Posted on 18 Oct 2023 16:13:09 by Eternal995

Hello,

I have Three tables, call it A, B, and C. Table B have two lookup columns, the first 'Column A' is a lookup column reference to table A, it's relationship is many-to-one in the table property, but in the table it's either empty or only have unique link to a record in table A, the second lookup column 'Column C' is a lookup column reference to table C, it is a many-to-one relationship as well and have multiple record pointing to one record in table C.

When I try to do this:

ClearCollect(
 'Temp Table A',
 AddColumns(
 'Table A',
 "New Column",
 LookUp(
 'Table B',
 'Column A'.Id = Id,
 'Column C'.'Name'
 )
 )
)

The "New Column" in the collection will show only duplicates of one particular data, does anyone know what's going one have a potential solution?

Thank you!

Categories:

General Questions

Connecting to Data

Using Apps Using Formulas

All responses (3)

Answers (1)

LaurensM 12,510 Moderator on 18 Oct 2023 at 18:44:52

Like (1)

Report

Re: AddColumns adds a new column with all duplicating data

@Eternal995 happy to help!

ThisRecord is an implicit record scope reference. When using nested functions that loop through your datasource or have a condition parameter (e.g. Filter, AddColumns, LookUp, ForAll...) an explicit record reference is recommended.

This way Power Apps does not have to 'guess' whether ThisRecord refers to (in your case) the LookUp or AddColumns scope. By defining your AddColumns record scope explicitly as 'Main', ThisRecord (implicit scope) now refers to the LookUp record scope. (ThisRecord. is optional, you don't have to write this and can simply reference the column name)

I hope this helps!
Eternal995 6 on 18 Oct 2023 at 18:36:17

Like (0)

Report

Re: AddColumns adds a new column with all duplicating data

Thank you! that's works for me. I tried using 'ThisRecord.Id' but it doesn't work, what's the difference between using ThisRecord and this explicit record scope reference?
Verified answer

LaurensM 12,510 Moderator on 18 Oct 2023 at 17:38:38

Like (1)

Report
Re: AddColumns adds a new column with all duplicating data

Hi @Eternal995,

We may have to use an explicit record scope reference. Could you give the code below a try:

ClearCollect( 'Temp Table A', AddColumns( //Explicit record scope 'Table A' As Main, "New Column", //Note: LookUp retrieves the first record that matches the condition LookUp( 'Table B', //Id references table A record 'Column A'.Id = Main.Id, 'Column C'.'Name' ) ) )

If this solves your question, would you be so kind as to accept it as a solution & give it a thumbs up.

Thanks!