Use data validation to select distinct pairs
In this app, we've got Items (Mug, Laptop, Pen) and Characteristics (Color, Size, Material). We want to collect comments on each possible Item-Characteristic pair, but only allow for one comment per pair. We also want to enforce this in a form, so that if you choose Mug it will only show possible characteristics about Mug that have not been commented on.
Also see: tablet mode & fullscreen mode
How we built this app
We created two virtual columns on the Item table to make this easier. First, we created one called "Already Assigned" with this formula: SELECT(Item Characteristics[Characteristic], [Item] = [_THISROW]) The formula selects the list of characteristics that have already been commented on for this row. Next, we created another virtual column called "Not Yet Assigned" with this formula: Characteristics[Characteristic Name] - [Already Assigned] The formula subtracts the already assigned characteristics from the full list. Finally, we set the valid_if column of the Item-Characteristics table to be [Item].[Not Yet Assigned]. This makes it so when you select an item, only the characteristics that have not yet been assigned are in the dropdown.
This is a preview of the data sets used in the app.
This is a high-level model of both the data entities and the UI elements in the app.