Counting number of entries of an intersect

I have 3 data tables in my app.

  1. Roles (example data)
  • finance manager
  • purchase ledger clerk
  • IT support
  1. Training (used to highlight training events and have a list of roles attached)
  • safety training (role IT support to attend)
  • finance training (roles finance manager and purchase ledger to attend - stored in a list)
  1. Comms (communication events to be sent out to roles)
  • new finance regulation poster (roles finance manager and purchase ledger clerk to be made aware)

What I want to do is create an alert for any of the roles that have 2 or more entries. So for example

IT support only has one event (the safety training)
Finance manager has 2 events (finance training and poster comms)

What I think I need is an intersect from table roles looking at training and comms tables to do a count of entries in training and comms for each role - but I intend to display the number of roles affected in a text box on the screen. This means I think I need to loop through each role and do a count.

My backup plan is to just write how many events exist in a single table - but I would prefer not to change the data structure if i can help it.

Any ideas - if I havent confused you enough!

OK I have now simplified this to two tables but still having issues - would appreciate anybodies help.

I have a

Table: Roles

  • contains roles

Table: Events

  • contains all events. This has a field called Roles Affected and uses the ID from Table Roles.

I just want to
– display a RG that shows role entries that have at least one entry in the Events table
– display fields from table role (description) and Events (title).

This must be simple but I am not sure how I get this working :frowning: Anyone able to help?

Hey @p_clavering

Can you post screenshots of your Data Types config and your current RG setup?

Hi @ambroisedlg

Screenshot 2020-05-21 at 17.24.00

I have attached the two tables… which are effectively linked via the company role unique ID on the calendar events table. However this is a list of roles so need to be able to count.

I have made so many changes to the RG that I dont know what is the best way forward!

I could start with an RG that is of type role and then within the RG do a search for all calendar events that have the role in the list of roles affected. I could also create an RG of all calendar events and then somehow do a grouping possibly.

In terms of the fields in the RG I need

Role Code and Role Description and then a count of all Calendar Events where that role appears.

I have tried nested RG and toyed with intersect but just cannot get this to work :frowning: Ideally I only want roles to appear that have at appear in 2 calendar event lines.

@p_clavering I think I got what you meant based on that:

I just want to
– display a RG that shows role entries that have at least one entry in the Events table
– display fields from table role (description) and Events (title).

Check this demo:
And the editor:

I created dummy Events, each with a different count of Roles Affected (0, 1 and 2)

Let me know if that was it

This is it - perfect.

I spent hours looking at this and you solved in minutes. This really shows the power of this community.

Thank you - you have made my evening :slight_smile:

My pleasure, glad it’s working :slight_smile:

1 Like

it is working although I have noticed its very slow - not sure if thats just the server or if i could make it more efficient? I am guessing it is going through lists and matching items so will be slower.

@p_clavering RGs may take a bit of time to load/refresh when the data source changes. It wasn’t slow on mine but that can vary depending on your use case. @SerPounce might have some insights about performance?