Link Table Question

Knack Community
1

So I have this setup of table
Company
Locations
Users
Location - User Link Table

I create a number of locations for a company
Give access to users to selective locations via a add form

Question is on the pull down how do I get it to show only the locations the user isn’t added to so they do not have duplicate locations?

Ie Show user details, click add location
Opens form and the pull down of location is where the issue is.

Regards

Mark

2

Hey @Mark7, the only way you’ll be able to achieve this is by JavaScript. Knack’s native field display rules are good for showing things a user ‘is’ connected to, but not for showing ‘is not’.

Requirements

  • A Locations table with a list of locations (e.g. Australia, United States, United Kingdom, Italy).
  • A Location Requests table with a connection field to the Accounts table and the Locations table (field_ZZ)
  • An Accounts table with a connection field (field_YY) to the Locations table (should be many to many relationship)
  • A form (view_XX) that adds a new Location Request record connected to the logged-in Account (should be a login-protected page)

This script will run on a ‘request location’ form (view_XX), will check the Location field in your Account table (field_YY), and compare it to the connection field (field_ZZ) on the form. Make sure you replace view_XX, field_YY and field_ZZ with the appropriate view/field keys.

$(document).on('knack-view-render.view_XX', function(event, view, record) {

  var userLocations = Knack.getUserAttributes().values.field_YY;
  var connectionField = "field_ZZ";
  
  $(`#kn-input-${connectionField} select option`).each(function() {
    var $option = $(this);
    var optionValue = $option.val();

    if (userLocations.includes(optionValue)) {
      $option.remove(); // Remove options the user is already a part of
    }
  });
  $(`#kn-input-${connectionField} select`).trigger("liszt:updated")

});

Because a connection field on a form stores both the record ID and record display name in two separate <select> and <ul> elements, this code loops all of the <option> tags to find and remove the selections in the <select> and then runs an event handler to update the <li> sub-elements in the <ul>.

The desired behaviour can be seen in the GIF below:
user_connection_field_dropdown_filter

Let me know how you go!

1 Like
3

I apologise, I only just properly read your listed tables.
It would be a bit more difficult to loop through the list of the Location - User link table versus what I showed with a many-to-many connection field stored under the Account, but if the above solution still works with the below workflow, then it might still be a winner:

  1. User requests location via a Add location request form
  2. Admin manually edits that user’s Location record
  3. Admin marks off location request as resolved
  4. User then is linked to that location
1 Like