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

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
Community Support Team
Community Support Team

Re: Add Column to Collection by lookup in another Collection

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

Re: Add Column to Collection by lookup in another Collection

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

 

Thank you,

 

@TopShelf-MSFT 

5 REPLIES 5
sopatte
Level 8

Re: Add Column to Collection by lookup in another Collection

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.



Community Support Team
Community Support Team

Re: Add Column to Collection by lookup in another Collection

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

Re: Add Column to Collection by lookup in another Collection

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

 

Thank you,

 

@TopShelf-MSFT 

ruslank
Level: Powered On

Re: Add Column to Collection by lookup in another Collection

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
Level: Powered On

Re: Add Column to Collection by lookup in another Collection

@v-xida-msft @TopShelf-MSFT 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!