Filtering a dropdown of a connection field for multi-tenancy when creating a new record

Scenario:

  • A table of accounts. The account is connected to the account it-self, so you can chose a parent account
  • Multi-tenancy - accounts connect to the tenant connected to the logged-in user
  • Filtering the dropdown for the accounts which are available for this tenant
  • Adding a new account through a form, in which you can chose the parent account

Requirement:

  • Only show the accounts connected to the tennant of the loggedin user in the drop-down

Approach chosen which doesn’t work:
Filter the dropdown with “Tenant” “is connected with current records field” “Tenant”

Why it doesn’t work
The record does not exist yet when you add a record through a form

What I would want.
Only show account records which connect to the loggedin users tenant

Question

  1. Do I have logic error in my thinking
  2. When no: how can you solve this tenant issue, is there a clever way to do this?

Thanks

PS copy/pasting whole pages and tables which are restricted to each tenant would be a solution but would conflict with the DRY principle.
PS2 I could think of a solution with local storage but that can not be chosen as a filter.

Hi Peter, I believe the right schema should be 3 tables: 1 for tenants (users), 1 for accounts (parent) and 1 join table for account/tenant. The form should be used for the third table, so you will be able to filter in the form using the condition all accounts for the logged-in user or whether it is not a logged-in user, for the record value of the tenant.

I found a duplicate question, with no definite answer, but that @moe might have found a solution or @SuiteUpstairs would look into it

Hi Peter, I did provide a workaround for this. It involves creating a 2-step form:

To elaborate on this more, when the user submits the first blank form, you would set up a form rule that sets that record’s connection to the logged-in user. This way, on part 2 of the form, you can add the parent connection field, hide it from view, then add your child connection field and filter it by the parent connection field. Since the parent connection field was set in the previous form, the child connection field will only show the records connected to the parent.

Hello again Peter, I just tested this solution out by @Effy and it works great:

Hi @Peter @SuiteUpstairs

Cam’s solution is the only way at the moment. Its a 2 step process im afraid. You might want to consider taking a step back and relook at your data model whereby you have:

1 - Principle Admin ( creates Site Admins per organisation )
2 - Site Admin (creates User accounts for tenant/organisation)
3 - Users (tenant organisation, created By etc are auto inserted into table using record rules at step 2 above)

screenshot below does same job as Cam’s solution via edit once new user is created.

ie tenant dropdown list only shows user account for Tenant 1 and not Tenant 2 user which are shown below