Template
Distinct pairs
Use data validation to select distinct pairs
Industry:
n/a
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.
Loading...
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.
Data
This is a high-level model of both the data entities and the UI elements in the app.
Loading...