| Saim Ali
Most of the time we need to create one-to-many or many-to-many relationships while dealing with databases. Canvas Apps function library provides us with Relate and Unrelate functions. The Relate function links two records through one-to-many or many-to-many relationships in Microsoft Dataverse. The Unrelate function reverses the process and removes the link.
Syntax:
Relate( Table1RelatedTable, Table2Record )
Unrelate( Table1RelatedTable, Table2Record )
Example:
Relate( First( Products ).Reservations, First( Reservations ) )
Unrelate( First( Products ).Reservations, First( Reservations ) )
While using Relate and Unrelate functions in Canvas Apps I came across a scenario where I have to associate and disassociate multiple entities at once. Microsoft docs have shown the example but it is limited to an entity at once.
To associate and disassociate multiple entities at once. The problem was that when associating and disassociating multiple entities separately it was working fine but when i tried to do it separately with ForAll as given below:
ClearCollect(selectedProductIds , varSelectedOpportunity.'Products'.'Product' );
ClearCollect(selectedProducts, Filter('Products', 'Product' in selectedProductIds.'Product'));
// REMOVE PREVIOUSLY SELECTED ITEMS
ForAll(
selectedProducts,
Unrelate(
LookUp(Accounts, Account = Gallery1.Selected.Account).'Products',
ThisRecord
);
);
// ADD NEW SELECTED ITEMS
ForAll(
DataCardValue6.SelectedItems,
Relate(
LookUp(Accounts, Account = Gallery1.Selected.Account).'Products',
ThisRecord
);
);
This was not the solution I wanted. What I wanted was to disassociate all the entities first and then associate the one’s i had selected. So I came up with the solution that is given below:
ClearCollect(selectedProductIds , varSelectedOpportunity.'Products'.'Product' );
ClearCollect(selectedProducts, Filter('Products', 'Product' in selectedProductIds.'Product'));
Set(selectedProductCount, CountRows(selectedProducts));
Clear(countLoopIterationCollection);
If
(
selectedProductCount > 0
,
// CASE WHEN PREVIOUSLY SELECTED PRODUCTS EXIST
// REMOVE PREVIOUSLY SELECTED ITEMS
ForAll(
selectedProducts,
Unrelate(
LookUp(Accounts, Account = Gallery1.Selected.Account).'Products',
ThisRecord
);
Collect(countLoopIterationCollection, 1);
If
(
CountRows(countLoopIterationCollection) = selectedProductCount
,
// ADD NEW SELECTED ITEMS
ForAll(
DataCardValue6.SelectedItems,
Relate(
LookUp(Accounts, Account = Gallery1.Selected.Account).'Products',
ThisRecord
);
);
);
);
,
// CASE WHEN PREVIOUSLY SELECTED PRODUCTS DO NOT EXIST
ForAll(
DataCardValue6.SelectedItems,
Relate(
LookUp(Accounts, Account = Gallery1.Selected.Account).'Products',
ThisRecord
);
);
);
Join us next time, as we continue our journey of learning canvas apps.Click here to learn more about Imperium's Power Apps Services. We hope this information was useful, and we look forward to sharing more insights into the Power Platform world.
