Hello! I’ve found similar threads related to filtering choices but I haven’t been able to find one that translates to my use case.
I have a collection which when added to, presents the user with a choice dropdown. The visibility settings are arranged to only present the user with items specific to their dropdown choice for submission. My issue is that once the dropdown choice they’ve selected is submitted, the same choice should not still be available in the choice dropdown (ie each choice should only be submitted once per screen).
I’ve tried a relation to a collection-specific unique ID (allowing multiples) followed by a lookup which gives me an index of all the used choices for that screen. I can effectively generate a list of choices to exclude, but the filter settings on the choice component doesn’t recognize the calculated lookup column.
How can I exclude used choices from future collection additions?
Attached is an example where North, South, and East are already submitted so the user should only see “West” available as a choice.
There are a couple of ways to accomplish this. Where are the choices being written to? The users table or some other table?
Sorry if I’m not understanding, the choices are written to the same table referenced by the collection component, not the user table but not necessarily another table either. The choices themselves exist in a “Dropdown” table which I’m using to hold all the the choice lists.
So each user will only ever submit at most one entry per available choice? Would there ever be a time they’d choose North again?
That’s correct! In the example case, we want to limit the user to only submit one set of data for North. The collection is set to allow data editing for “North” if changes need to be made.
Without knowing more about your data structure, my thinking is in the table where you’re storing the choices, you can create a query targeting the submissions.
Then filter by submitted ID is signed-in user’s ID, and choice being this row > choice.
In your choice component, filter by query result is empty.
Sorry for the late response.
ThinhDinh - thank you, this is absolute magic with a catch. The hiccup is that the choices available in the choice component on each screen are all the same. The submissions from all screens are stored in the same column so any single submission from one screen excludes the option in another screen; so, the choice component should only exclude choices submitted depending on the screen. I’ve updated the example to use your suggestion and propose a resolution.
Each screen is associated with a unique ID which is included in the query targeting the submissions. In the choice table, is there a way to reference your submissions query to list what unique IDs have submissions for each choice? My thinking is the choice component could additionally filter choices where 1. the query result is empty and 2. the screen’s unique ID is not included in the list of unique IDs.
The desired result in the example is for screen ABC to present choices East and West, while screen DEF would present North and East.
Let’s circle back a bit. You have a choice table with 4 choices being N, S, E, W. Then you’re presenting those choices in a collection table, each has a unique ID.
If a user has chosen either N, S, E, W for a collection, then that value shouldn’t be available for all other users?
The choice selected for submission should not affect another screen for the same user nor for other users.
Maybe the disconnect is the collection in question exists within a collection. When a submission in the parent collection is selected, the detail view includes this child collection. The submissions in the child collection are influencing child collections for other parent collection submissions and this is the undesired effect.
Yeah, I didn’t mention it would affect other screens, but it would affect the same screen, is that correct? And it would affect the ability to choose for the same user, or for all users?
Yes it affects the subject screen so I think it’s in the right direction. I’m understanding the solution lies in how to apply a screen-specific condition to the choice filter.
Choices available to users should be independent of choices other users submit. Users can only see the collections they themselves define and the choices made in the child collection should be unique to each user’s parent collection submission.
