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

[Peter]

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.

[Aprada]

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.

[Peter]

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

Automatic Pre-Population of Company Select Field

I’ve spent time looking through the forum and I’ve seen similar questions and answers but I can’t seem to find the answer that directly addresses my issue. I’m a beginner, so apologies if I’m repeating something that’s already been raised and resolved. I’m building a multi-tenanted app (one app serves multiple clients). All data tables include a relationship to a “Company table” in order to filter records when displaying, adding and editing. When I create an “Add Record” screen I need the new …

[SuiteUpstairs]

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

Not able to set dropdown selected value on connection field from java script

No, I have not been able to make it work that way. The workaround I use is a two-step form. I add the connection field that I want to set in the first step of the form and hide that field on the form, then make a rule to set the connection field, and then change the submit rules to go to step two of the form. Typically, I won’t add any other fields to the first step and just rename the button to something like “Create new Invoice” or “Start Quote” so that it doesn’t seem like a two-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.

[SuiteUpstairs]

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

Not able to set dropdown selected value on connection field from java script

Hey you can do this with the following TB.render('your_component', function(data) { const joinField = jQuery('#field_block_your_field select.select2', data.ele).select2(); const values = valueToSet.includes('[') ? JSON.parse(valueToSet.replace(/'/g, '"')) : [valueToSet]; values.forEach(value => { jQuery(joinField).trigger({ type: 'select2:select', params: { data: { id: value } } }); }); jQuery(joinField).val(values).trigger('change.select2…

[Shumon]

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.

[Shumon]

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