Nested Search for Dropdown Element's Choices source

I am developing an app that multiple organizations will subscribe to. Each organization will have multiple users.

Some users will be members of multiple organizations.

When displaying records, I am filtering the records based on the organization that data belongs to.

I have a data type “Accounts” which in addition to it’s unique ID has a single field for an account name.

I have another data type “Account Memberships” which has a field “Account ID” which holds the unique ID of an “Account”, the unique ID of the user who is a member of that account and a lot of other fields that have no bearing on this question.

When a user logs into the database, I am doing a search of the Account Memberships table to determine if the user is a member of more than one organization. If they only belong to one organization then I write that organization’s unique ID to a custom state that is used to filter the data that user can see.

If however, the user is a member of multiple organizations, I am displaying a non-reusable popup with a dropdown element. From this dropdown, the user will select the organization (Account Name) whose records they wish to work with.

I am having trouble filtering the list of organizations that appear in the dropdown.

What I “think” I need to do is the following:
Search for Accounts where the Account ID exists in a record within the Account Memberships data type AND the user ID of the Account Membership = the current user’s unique ID.

I’m going nuts trying to this working and would appreciate any guidance that can be offered.

Hi @louisnickerson ,

so if I do understand your data structure correctly, you can use this expression for the dropdown options:

It means: look for all account memberships that have user = current user and list each of the organizations related to that in the dropdown.

I tried it and it worked for me: (2)


My apologies for the delay in responding. I have been tied up on another project the past couple of days. I will study your suggestion and give it a try.

Thank you very much for your assistance.