Hi there,
Thanks for clearing that up.
In that case, you need to query Microsoft's hidden intermediate table for that N:N relationship to find out if the User is related to that table, then do something based on it, like disable the assign button or hide the Tasks already linked to the selected User.
This table has 3 useful fields - the 3 unique IDs for the value of Table 1, the joining ID (ID of the intermediate table) and the value of Table 2.
This can be seen most easily (in my opinion) using SQL4CDS in the XRMToolbox, by querying that intermediate table (the name of which will be similar to the relationship name)
SELECT TOP 1000 *
FROM {Intermediate Table Name)
WHERE 1=1
You can filter your list of dropdown options based on Tasks that do not have a row in this table in some way like this:
Filter(IntermediateTable, Table1Field != ThisItem.ID)
If you want to only show records not already assigned to the selected User, I would use ClearCollect to put this into a fresh collection each time a User is selected, and then have the dropdown options show that collection, which is filtered on Tasks related to this selected user through that intermediate table, something like this:
// In the OnSelect property of a button
ClearCollect(
RelatedTasks,
Filter(
Tasks,
TaskID in Filter(Intermediate Table, UserId = UserGallery.Selected.UserID).TaskId
)
)
Note; This solution doesn't prevent Tasks from being assigned to the same User, it just hides the possibility of happening from the UI/UX of your App with these controls.
Hope this helps! :)
