How to Populate Values from a Connected Table (Regions from Events into Projects)

[Quynh-Tram]

I have a projects table and an events table. There can be many events that are connected to a single project. Each event listed will be held in one county/parish, and each county/parish is associated with one region. There are nine regions that the state is broken down into.

My question is: how do I get the list of regions from the events table to show in the yellow highlighted area? In this example, I would need the yellow highlighted area to list both Acadiana and East Baton Rouge Parish.

Thank you so much in advance.

[StephenChapman]

Hi @Quynh-Tram, here’s how to do it:

1. Create the four tables (assuming this is your set up):
   

   

2. Projects table doesn’t require any connections:
   

   

3. Regions table doesn’t require any connections:
   

   

4. Parishes table should connect to one Region:
   

   

5. Events table should connect to one Project, Parish, and Region:
   

   

6. On your Add Event form, include the Parish connection field
   

   

7. On your Add Event form’s record rules, add a rule to set the Region to the connected Parish’s Region.
   

   

8. Now that Region is available on the Events table, you can insert a connection field to your Project details view that displays all Event Regions:
   

   

The only issue is that it will show duplicate Regions if two or more events share the same Region, e.g. Acadania shows twice.
There is no way around this apart from manipulating the field with JS code to remove any duplicate strings.
You can do so like so with something like this, where view_xx is your details view, and field_xx is your Region connection field:

$(document).on(`knack-view-render.view_xx`, function (event, view) {
  const $fieldBody = $(`#${view.key} .field_xx .kn-detail-body`);
  const fieldText = $fieldBody.text();
  const uniqueText = [...new Set(fieldText.split(',').map(s => s.trim()))].join(', ');
  $fieldBody.text(uniqueText);
});

Hope that answered your question! Feel free to buy me a coffee if that was helpful.

[Quynh-Tram]

Thank you Stephen! That is the issue that I am running into, the duplicates. I don’t want duplicate data to junk it up.