Add Column to Collection by lookup in another Collection

[ruslank]

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!

[sopatte]

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 · Accepted solution]

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,

[Anonymous · Accepted solution]

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

Thank you,

@Anonymous 

[ruslank]

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]

@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!

[monicali]

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]

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.