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!
Solved! Go to 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:
Best regards,
Thanks for posting in the community @ruslank - can you review the above replies and update the thread if they were helpful?
Thank you,
@Anonymous
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.
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:
Best regards,
Thanks for posting in the community @ruslank - can you review the above replies and update the thread if they were helpful?
Thank you,
@Anonymous
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!
@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. 😉
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.
ClearCollect(LeadsAssignedStates,
UnassignedLeads,
"Applicable States",
LookUp(StateBorders, USState = UnassignedLeads[@'*State'], Concatenate(Abbreviated, ", ", BorderaAbbv))
);
User | Count |
---|---|
258 | |
111 | |
95 | |
48 | |
41 |