cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ruslank
Frequent Visitor

Add Column to Collection by lookup in another Collection

I have two collections:

(CollectionA) with Columns:

- ID

- Description

 

(CollectionB) with Columns

- Id

- Price

 

I would like to create additional Column in CollectionA "Price" and bring in the Value from Price in CollectionB based on the lookup of of CollectionB.Id=CollectionA.ID

 

Any help or direction is greatly apprecated!

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-xida-msft
Community Support
Community Support

Hi @ruslank ,

Do you want to add a new column in your Collection A with values from the Price column in your Collection B based on CollectionB.Id=CollectionA.ID?

 

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

Set OnSelect property of the "Button" control to following:

ClearCollect(
               NewCollectionA,
               AddColumns(
                           CollectionA,
                           "Price",
                           LookUp(CollectionB, Id = CollectionA[@ID], Price)
               )
)

Note: The result the AddColumns() function returned is a new table with the transform applied. The original table isn't modified. In other words, the AddColumns() function returns a temporary table with the transform applied, but the underlying table (CollectionA) would not be modified.

If you want to get/use the modified table, you could consider save the result the AddColumns() function returned into a new Collection as I mentioned above, then you could use the NewCollectionA collection within your app.

 

More details about the AddColumns function, please check the following article:

AddColumns function

 

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.

View solution in original post

Anonymous
Not applicable

Thanks for posting in the community @ruslank - can you review the above replies and update the thread if they were helpful?

 

Thank you,

 

@Anonymous 

View solution in original post

7 REPLIES 7
sopatte
Microsoft
Microsoft

You can't dynamically add Columns to an existing collections afaik and write them back to that collection. But you could save a new collection with the additional column:

 

Collect(
    CollectionC,
    AddColumns(
        CollectionA,
        "Price",
        Lookup(
            CollectionB,
            Id=ID,
            Price
        )
    )

)

If you run into a naming collision with the ID column, use RenameColumns, e.g. instead of using CollectionB use:
RenameColumns(CollectionB,"Id","BID")
which will produce a table identical to CollectionB but with the Id column renamed to BID.



v-xida-msft
Community Support
Community Support

Hi @ruslank ,

Do you want to add a new column in your Collection A with values from the Price column in your Collection B based on CollectionB.Id=CollectionA.ID?

 

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

Set OnSelect property of the "Button" control to following:

ClearCollect(
               NewCollectionA,
               AddColumns(
                           CollectionA,
                           "Price",
                           LookUp(CollectionB, Id = CollectionA[@ID], Price)
               )
)

Note: The result the AddColumns() function returned is a new table with the transform applied. The original table isn't modified. In other words, the AddColumns() function returns a temporary table with the transform applied, but the underlying table (CollectionA) would not be modified.

If you want to get/use the modified table, you could consider save the result the AddColumns() function returned into a new Collection as I mentioned above, then you could use the NewCollectionA collection within your app.

 

More details about the AddColumns function, please check the following article:

AddColumns function

 

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.

View solution in original post

Anonymous
Not applicable

Thanks for posting in the community @ruslank - can you review the above replies and update the thread if they were helpful?

 

Thank you,

 

@Anonymous 

View solution in original post

Thank you so very much apologies for delay in my reply @v-xida-msft ! This works! Woudl you be able what the difference is between CollectionA.ID and CollectionA[@ID]

Thank you!

ruslank
Frequent Visitor

@v-xida-msft @Anonymous Team, do you by chance have any insight into the question i posted on the difference  between CollectionA.ID and CollectionA[@ID]? Thank you much!

I appreciate so much this post, as it helps really on getting the right syntax.

My case is a bit tricky, as the link is not tied by the column id on both side, but table1.id = table2.newid.

I still don't understand how to look if they are text of int. But with Value (newID)=Table1[@ID] works just fine. 😉

 

LVD
Advocate I
Advocate I

I use an AddColumns to a collection using a lookup from an Entity in CDS but all values get the same value in the newly collected collection. I welcome any ideas.

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Don't miss the call this month on June 16th - 8a PDT

Top Solution Authors
Top Kudoed Authors
Users online (20,415)